search for any combination of forms field

[brad12345]

Hi all

I have a veterinarian website

 

I can search through my DB for the exact match of the form that the user fills out but if they leave a field missing or get a detail like the pets name wrong it wont come up with any data when i report their search, How do i make it so they can search for any combination, so if all they were to do is put in the pets age and not the pets name it would come back with all the pets that have that matching age, But if they were to put in the pets name and age it would get more accurate and not so broad with the answers.? did that make sense? hope so lol

 

Heres my code

 

//Owner POST variables///
$phoneNumber = $_POST['phoneNumber'];
$address = $_POST['address'];
$ownerName = $_POST['ownerName'];


//Patient POST vaiables//
$petName = $_POST['petName'];
$petSpecies = $_POST['petSpecies'];
$petBreed = $_POST['petBreed'];
$petAge = $_POST['petAge'];
$petWeight = $_POST['petWeight'];

$search = $_POST['search'];


///If the Search form has been filled out execute this code///
if (isset($_POST['search']))
{
$selectString = "select * from tblPatientBK where petName = '$petName' AND petSpecies = '$petSpecies' AND petBreed = '$petBreed' AND petAge = '$petAge' AND petWeight = '$petWeight'";
$resultPatient = mysql_query($selectString);	

	echo ("<table border=1>");
echo ("<tr>");
echo ("<td width=100><b>petID</b></td>");
echo ("<td width=100><b>Name</b></td>");
echo ("<td width=100><b>Species</b></td>");
echo ("<td width=100><b>Breed</b></td>");
echo ("<td width=100><b>Age</b></td>");
echo ("<td width=100><b>Weight</b></td>");
echo ("<td width=100><b>ownerID</b></td>");
echo ("</tr>");	

    while ($row = mysql_fetch_row($resultPatient))
		{

			echo("<tr>");		
			foreach($row as $value)
				echo("<td width=100>$value</td>");  
				echo("</tr>");
		}
           print (mysql_error());
		echo("</table>");


	$selectString2 = "select * from tblOwnerBK where name = '$ownerName' and address = '$address' and phoneNumber = '$phoneNumber'";
$resultPatient2 = mysql_query($selectString2);	

echo ("<table valign='top' border=1>");
echo ("<tr>");
echo ("<td valign='top' width=20><b>Owner ID</b></td>");
echo ("<td valign='top' width=20><b>Title</b></td>");
echo ("<td valign='top' width=200><b>Owner Name</b></td>");
echo ("<td valign='top' width=230><b>Address</b></td>");
echo ("<td valign='top' width=200><b>Phone Number</b></td>");
echo ("</tr>");	

    while ($row = mysql_fetch_row($resultPatient2))
		{

			echo("<tr>");		
			foreach($row as $value)
				echo("<td width=100>$value</td>");  
				echo("</tr>");
		}
           print (mysql_error());
		echo("</table>");		
}
else  ///Beginning else code 
{

	//echo("$errorString");//

	echo("<h4>Search</h4>");
	echo("Please enter your search query");


	///Creating the form to handle the Search query that the user is filling out////
	echo ("<form action='$self' method='POST'>");
	echo ("<fieldset>");
	echo ("<legend>Pet Details</legend><p>");
		echo ("<table>");

		echo ("<tr>");
			echo ("<td width=200>Pet name : </td><td><input type='textbox' name='petName' value=''></td>");
		echo ("</tr>");

		echo ("<tr>");
			echo ("<td width=200>Species :  </td><td><input type='textbox' name='petSpecies' value=''></td>");
		echo ("</tr>");

		echo ("<tr>");
			echo ("<td width=200>Breed :    </td><td><input type='textbox' name='petBreed' value=''></td>");
		echo ("</tr>");

		echo ("<tr>");
			echo ("<td width=200>Age :      </td><td><input type='textbox' name='petAge' value=''></td>");
		echo ("</tr>");

		echo ("<tr>");
			echo ("<td width=200>Weight :   </td><td><input type='textbox' name='petWeight' value=''></td>");
		echo ("</tr>");

		echo ("</table>");
		echo ("</fieldset>");




		///Creating the second table within the form to handle the Owner search querys///
		echo ("<fieldset>");
	echo ("<legend>Owner Details</legend><p>");
		echo ("<table>");

				echo ("<table>");
	echo ("<tr>");
	echo ("<td width=200>Name                             : </td><td><input type='textbox' name='ownerName' value=''></td>");
	echo ("</tr>");
	echo ("<tr>");
	echo ("<td width=200>Address                          : </td><td><input type='textbox' name='address' value=''></td>");
	echo ("</tr>");
	echo ("<tr>");
	echo ("<td width=200>Phone Number                     : </td><td><input type='textbox' name='phoneNumber' value=''></td>");
	echo ("</tr>");
	echo ("</table>");

		echo ("</table>");
		echo ("</fieldset>");
		echo ("<br>");
		echo ("<input type='submit' name='search' value='Search'> <p>");

		echo ("</form>");



} // end of else

?>		

 

Thanks all

[AP81]

Hi Brad,

 

What you need to do is dynamically build your SQL query based on what information you have.

 

So, what I would do is something like this (note: I have used real data instead of posted data, but this will work perfectly with your existing data) :

 

<?php
$petName    = 'Panda';
$petSpecies = 'Dog'; 
$petBreed    = 'Papillion';
$petAge      = 2;
$petWeight  = 1.5;

$phoneNumber = '123456789';
        $address         = '123 Test Street';
        $ownerName    = 'John Doe'; 


	function BuildQuery($colname, $colvalue, $resetCounter = false ) {
	  static $counter = 0;

		if ($resetCounter)
		 $counter = 0;

		if  ( (!isset($colvalue)) || (empty($colvalue)) )
			return '';

		$counter++;

		if ($counter <= 1)
			return "where $colname = '$colvalue' ";

		return "and $colname = '$colvalue' ";
	}


	$selectString = "select * from tblPatientBK ";
	$selectString .= BuildQuery("petName",$petName);
	$selectString .= BuildQuery("petSpecies",$petSpecies);
	$selectString .= BuildQuery("petBreed",$petBreed);
	$selectString .= BuildQuery("petAge",$petAge);
	$selectString .= BuildQuery("petWeight",$petWeight);

	echo $selectString;

	$selectString = "select * from tblOwnerBK ";
	$selectString .= BuildQuery("ownerName",$ownerName,true);
	$selectString .= BuildQuery("address",$address);
	$selectString .= BuildQuery("phoneNumber",$phoneNumber);
        echo '<br>';
	echo $selectString;
?>

 

Now take a look at the BuildQuery function.

 

What this is doing is:

a) checking the value is valid

b) building the querystring

c) returning the query string

 

The first thing you see is the static variable.  This keeps track of how may times we have called the function.  If we didn't do this, it would make it hard to check whether to output 'WHERE' OR 'AND' in the query.

 

The first time you call the function, it will default to where, and subsequent calls will default to 'and'.  Get it?  Simple.  If for some reason the value you have passed to the function is empty, it will return nothing.

 

The last thing to take note of is the reset counter variable.

<? function BuildQuery($colname, $colvalue, $resetCounter = false ) ?>

 

Remember that the $counter variable is static, so it holds its value.  If you are going to do multiple queries in the same page, you need to set $resetCounter to true, otherwise it will output 'and' instead of 'where' when you call the next query.  Take a look at what I did with the tblOwnerBK query and you will see what I mean.

 

You could even improve this code further, so it will allow for partial searches...for example, you could add another parameter to the BuildQuery function called $operator.  You can then pass 'LIKE' OR '=' to the BuildQuery function and have it do return something like this:

WHERE petName LIKE 'p%'

 

This will then return everything starting with 'p'.

 

Hope this clears things up for you.

 

[brad12345]

wow such an elaborate answer!

thanks a lot im gona give that a go sounds like it will work for me

 

Cheers!

[brad12345]

hey man that was great! also on top of that if i dont fill out any fields on my owner form it will produce the entire owner table?

 

any way of restricting this?

[AP81]

Good point.  Yes, you are right, it would do an entire select.  To get around it, you can do this:

 

<?php
	$selectString .= BuildQuery("petName",$petName);
	$selectString .= BuildQuery("petSpecies",$petSpecies);
	$selectString .= BuildQuery("petBreed",$petBreed);
	$selectString .= BuildQuery("petAge",$petAge);
	$selectString .= BuildQuery("petWeight",$petWeight);
                if (strlen($selectString) == 0) {
                  die('Please ensure one or more fields are filled');
                } else {
                   $selectString  = "select * from tblPatientBK " . $selectString; // add the query parameters to the base query
                   $resultPatient = mysql_query($selectString);	
                }

                // do the same thing to the next query ...
?>