I want to filter records being displayed from a MySQL table if the value of a field = one of multiple predeclared values. I am already running a query on my table which displays records based on which option from a dropdown menu the user selects. However before the user selects an option from the dropdown box when the page has just loaded I want the table to display only some records.
<?php
require_once("includes/session.php");
include_once("includes/masterinclude.php");
$preferences = getPreferences();
$category = "";
$attribute1 = ""; $attribute2 = ""; $attriute3 = ""; $attribute4 = "";
$top_level="0";
$name = $_GET['member'];
$name = $_GET['countries'];
$information = getInformationPage($name);
$infopagename=$information->IN_NAME;
//meta data for information pages now taken from the information table
$pageTitle = $information->IN_NAME . html_entity_decode($information->IN_TITLE);
$pageMetaDescription = html_entity_decode($information->IN_META_DESC);
$pageMetaKeywords = html_entity_decode($information->IN_META_KEYWORDS);
$pageCustomHead = html_entity_decode($information->IN_CUSTOM_HEAD, ENT_QUOTES);
//initialise screen fields
$selected_member = "";
$id = "";
$username = ""; $username_original = "";
$password = ""; $password_original = "";
$password_test = "";
$title = "MR"; $first_name = ""; $last_name = ""; $company_name = "";
$address1 = ""; $address2 = ""; $town = ""; $county = ""; $country = ""; $postcode = ""; $phone = ""; $mobile = ""; $email = "";
$member_confirmed = "N";
$ast_first = 0; $ast_last = 0; $ast_company = 0; $ast_add1 = 0; $ast_add2 = 0; $ast_town = 0; $ast_county = 0; $ast_country = 0; $ast_post = 0; $ast_phone = 0;
$ast_mobile = 0; $ast_email = 0;
$ast_user = 0; $ast_pass = 0; $ast_passconf = 0;
$selected_product = "";
$members = Get_All_Members("ALL");
$counties = Get_All_Counties("ALL");
$_GET['searchdata'] = $_POST['SEARCH_DATA']; $_GET['searchmember'] = $_POST['MEMBER'];
$selected_county = $counties->CTY_COUNTY;
$_GET['searchdata'] = $_POST['SEARCH_DATA']; $_GET['searchcounty'] = $_POST['COUNTY'];
$selected_country = $countries->CTY_COUNTRY;
$_GET['searchdata'] = $_POST['SEARCH_DATA']; $_GET['searchcountry'] = $_POST['COUNTRY'];
include_once("includes/header.php");
?>
<!-- start: Page header / Breadcrumbs -->
<div id="breadcrumbs">
<div class="container">
<div class="breadcrumbs">
<a href="/">Home</a><i class="icon-angle-right"></i>Export Stockists
</div>
</div>
</div>
<!-- end: Page header / Breadcrumbs -->
<!-- start: Container -->
<div id="container">
<div class="container">
<div class="row-fluid">
<!-- start: Page section -->
<section class="span12">
<div class="row-fluid shop-result">
<div class="inner darken clearfix">
<h1>Export Stockists</h1>
</div>
</div>
<div class="row-fluid">
<?php
$sql = "SELECT * FROM member ";
if (isset($_POST['search'])) {
$search_term = mysql_real_escape_string($_POST['search_box']);
$sql .= "WHERE MB_COUNTRY = '{$search_term}' ";
$sql .=" OR MB_COMPANY = '{$search_term}' ";
}
$query = mysql_query($sql) or die(mysql_error());
?>
<form name="search_form" method="POST" action="stockists_country.php" enctype="multipart/form-data" class="form-search">
<div class="input-append">
<select name="all_countries" onchange="MM_jumpMenu('parent',this,1)" type="submit">
<option disabled="disabled" selected="selected" hidden="hidden">Select County</option>
<option value="103">Republic of Ireland</option>
<option value="39">Canada</option>
<option value="149">Netherlands</option>
<option value="193">South Africa</option>
<option value="194">Spain</option>
<option value="228">United States</option>
</select>
<input type="submit" name="search" value="Search for Stockist" class="btn btn-primary">
</div>
</form>
<?php
$sql = "SELECT * FROM member ";
if (isset($_POST['search'])) {
$search_term = mysql_real_escape_string($_POST['all_counties']);
$sql .= "WHERE MB_COUNTRY = '{$search_term}' ";
//$sql .= "WHERE MB_COUNTRY IN ('103','39','149','193','194','228')";
$sql .=" OR MB_COMPANY = '{$search_term}' ";
}
$query2 = mysql_query($sql) or die(mysql_error());
?>
<table width="70%" cellpadding="5" cellspace="5" class="table table-hover table-striped">
<tr>
<td><strong>Company Name</strong></td>
<td><strong>Website</strong></td>
<td><strong>Phone</strong></td>
<td><strong>Address</strong></td>
</tr>
<?php
$a = "MB_COUNTRY";
$b = "103";
$c = "39";
$d = "149";
$e = "193";
$f = "194";
$g = "228";
if( in_array($a, array($b,$c,$d,$e,$f,$g)) ){
?>
<?php while ($row = mysql_fetch_array($query)) { ?>
<tr>
<td><?php echo $row['MB_COMPANY'];?></td>
<td><a href="http://<?php echo $row['MB_MOBILE'];?>"><?php echo $row['MB_MOBILE'];?></a></td>
<td><?php echo $row['MB_PHONE'];?></td>
<td><?php echo $row['MB_ADDRESS1'];?>, <?php echo $row['MB_ADDRESS2'];?>, <?php echo $row['MB_TOWN'];?>, <?php echo $row['MB_COUNTY'];?></td>
</tr>
<?php } ?>
<?php } ?>
</table>
<a class="btn btn-primary" href="login_member.php">Member Login</a>
</section>
<!-- end: Page section -->
</div>
</div>
</div>
<!-- end: Container -->
<?php
include_once("includes/footer.php");
?>
I have tried putting an IF around my table that says if MB_COUTRY = $b,$c,$d,$e,$f,$g then print records. However when using that my table displays no records however there are no errors. I want to run both queries on the same field (MB_COUNTRY) but at different times. If anyone knows of a query that would help and how to run them at the right times that would be a massive help.
You need to do the if on every row for it to work. Also, you need to test on a value from $row and not an arbitrary value. Your if will need another instruction to test on the user_search to make sure it is not already filtered.
<?php
$a = "MB_COUNTRY";
$b = "103";
$c = "39";
$d = "149";
$e = "193";
$f = "194";
$g = "228";
while ($row = mysql_fetch_array($query)) {
if( in_array($row[$a], array($b,$c,$d,$e,$f,$g)) ){?>
<tr>
<td><?php echo $row['MB_COMPANY'];?></td>
<td><a href="http://<?php echo $row['MB_MOBILE'];?>"><?php echo $row['MB_MOBILE'];?></a></td>
<td><?php echo $row['MB_PHONE'];?></td>
<td><?php echo $row['MB_ADDRESS1'];?>, <?php echo $row['MB_ADDRESS2'];?>, <?php echo $row['MB_TOWN'];?>, <?php echo $row['MB_COUNTY'];?></td>
</tr>
<?php } ?>
<?php } ?>