Website Searching

[jemmm]

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!

[Larry]

Okay, to start, a couple of minor things. First, you assign the $_POST variable to local variables at the top of the screen, but then don't use the local variables. I would recommend that you do some validation of the $_POST variables and create local variables in the process.

 

Next, I wouldn't think you'd really want to do a search if the user enters no terms and selects nothing. Or should that be the "all" supplier option, in which case you have 7 possible scenarios, not 8?

 

Next, you have 7-8 scenarios, but you really only have one query, which always selects the same data and always uses the same ORDER BY clause. The only difference is the WHERE clauses. I'd simplify the structure by playing off that fact. The only thing you have to watch out for is when to use AND or not in the SQL, because any clause could be the first clause. So I'd do something like this:

 

$and = false; // Don't use AND yet!

$q = 'SELECT * FROM supplier_details';
if ($keyword) {
   if (!$and) { // First clause, add the WHERE:
       $q .= ' WHERE ';
       $and = true;
   } else { // Already had a clause
       $q .= ' AND ';
   }

       $q .= "supplier_name LIKE '%$keyword%'";
}

 

You'd repeat this for the county and supplier type id, something like this:

if ($county) {

   if (!$and) { // First clause, add the WHERE:
       $q .= ' WHERE ';
       $and = true;
   } else { // Already had a clause
       $q .= ' AND ';
   }
   $q .= "county='$county'";
}

 

That should give you the logic you need, in only three conditionals. Remember that it relies upon validated local variables, set to false if that shouldn't be a factor in the query.

[jemmm]

Many thanks! That makes everything much clearer!