Hi all,
I'm am wondering if anyone can give me any pointers in the right direction for my current issue.
I have three search fields on a page, 'keyword', 'county' and 'supplier_type' with a submit button below. The keyword field is a text box and the other two fields are drop down menus which take the data from the mysql database.
When searching users have the option to enter nothing, one field, two fields or three fields. Following this I have determined that there are 8 possible ways that users can search:
1). keyword only
2). county only
3). supplier_type only
4). keyword and county only
5). Keyword and supplier_type only
6). County and supplier_type only
7). All fields- Keyword, county and supplier_type
8). no data entered by user
The code below displays the form on the page
<h1>Search</h1>
<form action="suppliersresults.php" method="post">
<fieldset>
<p><b>Supplier Name Keyword Search:</b> <input type="text" name="keyword" size="20" maxlength="40" value="<?php if (isset($trimmed['keyword'])) {echo $trimmed['keyword'];} else {echo 'keyword';} ?>" /></p>
<p><b>Supplier Location:</b></p>
<select name="county" id="county" class="formtext">
<?php
echo"<option value='all' selected='selected'>all</option>";
require_once('includes/config.inc.php');
$cq = "SELECT county FROM county_table ORDER BY county ASC";
$cr = @mysqli_query($dbc, $cq);
if ($cr)
{
while ($ca = mysqli_fetch_array($cr, MYSQLI_ASSOC))
{
echo '<option value="' . $ca['county'] . '">' . $ca['county'] . '</option>';
}
mysqli_free_result($cr);//free up resources
} else {
echo '</select>We apologise there seems to be a problem with this data';
}
?>
</select>
<p><b>Supplier Type:</b></p>
<select name="supplier_type_id" id="supplier_type_id" class="formtext">
<?php
//the below code is for when the user selects all supplier types. Commenting out until get it to work first.
//echo"<option value='all' selected='selected'>All</option>";
echo"<option value='all' selected='selected'>all</option>";
require_once('includes/config.inc.php');
$cq = "SELECT supplier_type_id, supplier_type_name FROM supplier_type ORDER BY supplier_type_name ASC";
$cr = @mysqli_query($dbc, $cq);
if ($cr)
{
while ($ca = mysqli_fetch_array($cr, MYSQLI_ASSOC))
{
echo '<option value="' . $ca['supplier_type_id'] . '">' . $ca['supplier_type_name'] . '</option>';
}
mysqli_free_result($cr);//free up resources
} else {
echo '</select>We apologise there seems to be a problem with this data';
}
?>
</select>
</fieldset>
<div align="center"><input type="submit" name="submit" value="Search" /></div>
<input type="hidden" name="submitted" value="TRUE" />
</form>
I have tested this and it correctly send the data through to suppliersresults.php but my problem is with the searching mechanism. Perhaps this is the wrong way of thinking but I have created if statements but they are not returning the correct data in all occasions.
//set variables
$county = $_POST['county'];
$supplier_type_id = $_POST['supplier_type_id'];
$keyword = $_POST['keyword'];
/////////////////////////////////////////////////
//if the user doesnt' enter anything in any field on the search page except for a keyword - 1
if (isset ($_POST['supplier_type_id']) == 'all' && ($_POST['county']) == 'all' && ($_POST['keyword']) <> 'keyword')
//search for a supplier name keyword search
$q = "SELECT * FROM supplier_details WHERE supplier_name LIKE '$keyword%' ORDER BY registration_date ASC";
//if the user doesnt' enter anything in any field on the search page except for a county - 2
if (isset($_POST['supplier_type_id']) == 'all' && ($_POST['keyword']) == 'keyword' && ($_POST['county']) <> 'all')
//search for a supplier name search
$q = "SELECT * FROM supplier_details WHERE county='$county' ORDER BY registration_date ASC";
//if the user doesnt' enter anything in any field on the search page except for a supplier type - 3
if (isset ($_POST['keyword']) == 'keyword' && ($_POST['county']) == 'all' && ($_POST['supplier_type_id']) <> 'all')
$q = "SELECT * FROM supplier_details WHERE supplier_type_id='$supplier_type_id' ORDER BY registration_date ASC ";
//if the user enters a keyword and county AND NOT a supplier_type_id - 4
if (isset ($_POST['supplier_type_id']) == 'all' && ($_POST['county']) <> 'all' && ($_POST['keyword']) <> 'keyword')
$q = "SELECT * FROM supplier_details WHERE county='$county' AND supplier_name LIKE '$keyword%' ORDER BY registration_date ASC";
//if the user enters a supplier_type_id AND a keyword AND NOT a county - 5
if (isset ($_POST['county']) == 'all' AND ($_POST['keyword']) <> 'keyword' AND ($_POST['supplier_type_id']) <> 'all' )
$q = "SELECT * FROM supplier_details WHERE supplier_type_id='$supplier_type_id' AND supplier_name LIKE '$keyword%' ORDER BY registration_date ASC";
//if the user enters a county AND a supplier_type_id AND NOT a keyword - 6
if(isset ($_POST['keyword']) == 'keyword' && ($_POST['supplier_type_id']) <> 'all' && ($_POST['county']) <> 'all')
$q = "SELECT * FROM supplier_details where supplier_type_id='$supplier_type_id' AND county='$county' ORDER BY registration_date ASC";
//if the user enters a county AND a supplier_type_id AND a keyword - 7
if (isset($_POST['supplier_type_id']) <> 'all' && ($_POST['county']) <> 'all' && ($_POST['keyword']) <> 'keyword')
$q = "SELECT * FROM supplier_details WHERE supplier_name LIKE '$keyword%' AND supplier_type_id='$supplier_type_id' AND county='$county' ORDER BY registration_date ASC";
//if the user doesn't enter anything - 8
if (isset($_POST['supplier_type_id']) == 'all' && ($_POST['county']) == 'all' && ($_POST['keyword']) == 'keyword')
$q = "SELECT * FROM supplier_details ORDER BY registration_date ASC";
Could anyone give me some guidance on where I am going wrong?
Many thanks!