Multi-Field Search Form; PHP returning all data/Not filtering results

[Doc20]

I am making a search form that will search a database with a few fields, and I am wanting the return to only show what matches the search criteria supplied. However, the script that I have now is returning all the rows, rather than just the ones that match/closely resemble (using %% wildcards).

 

I want the form to allow a person to type in as little detail as possible.  If they want to search by name only, I want it to search the name.  If they want to search a name and an address, it will search those two columns and return the data that is close (preferably even if the results are not from the same row of the database)

Here is the code:

<h1>Search Accounts...</h1>
<p>Fill out the fields you wish to search by.</p>
<form id="search" name="search" method="post" action="index.php?page=search">
  <div align="center">
    <table width="50%" border="0">
      <tr>
        <td>Debtor's Name</td>
        <td><label for="debt_name"></label>
          <input type="text" name="debt_name" id="debt_name" /></td>
      </tr>
      <tr>
        <td>Debtor's Address</td>
        <td><label for="debt_address"></label>
          <input type="text" name="debt_address" id="debt_address" /></td>
      </tr>
      <tr>
        <td>Debtor's City</td>
        <td><input type="text" name="debt_city" id="debt_city" /></td>
        </tr>
      <tr>
        <td>Vehicle VIN #</td>
        <td><input type="text" name="debt_vin" id="debt_vin" /></td>
        </tr>
      <tr>
        <td>Vehicle Year</td>
        <td><input type="text" name="debt_year" id="debt_year" /></td>
        </tr>
      <tr>
        <td>Vehicle Make</td>
        <td><input type="text" name="debt_make" id="debt_make" /></td>
        </tr>
      <tr>
        <td>Vehicle Model</td>
        <td><input type="text" name="debt_model" id="debt_model" /></td>
        </tr>
      <tr>
        <td>Company</td>
        <td><select name="company" id="company">
        <?php
		// Get Companies
		
		include ("include.php");
		
		$getcompanies = mysql_query ("SELECT * FROM company ORDER BY name ASC");
		
		while ($com = mysql_fetch_array($getcompanies, MYSQL_BOTH))
			  {		echo "<option value=$com[id]>$com[name]</option>";
			  echo $com['name'];
			  }	  
			  
			  
			  
		
		?>
        
        
        </select></td>
      </tr>
      <tr>
        <td colspan="2"><label for="company">
          <div align="center">
            <input type="submit" name="search" id="search" value="Search Accounts" />
          </div>
        </label></td>
        </tr>
    </table>
  </div>
</form>

<p>
  <?php

if(isset($_POST['search'])) {
	
	include ("include.php");
	
	// Get field data
	
	$name = $_POST['debt_name'];
	$address = $_POST['debt_address'];
	$city = $_POST['debt_city'];
	$vin = $_POST['debt_vin'];
	$year = $_POST['debt_year'];
	$make = $_POST['debt_make'];
	$model = $_POST['debt_model'];
	$company = $_POST['company'];
	
	//SQL statement to select relevant data
	
	$query = "SELECT * FROM `account`
	WHERE 
	name LIKE '%".mysql_real_escape_string($name)."%' OR
	address LIKE '%".mysql_real_escape_string($address)."%' OR
	city LIKE '%".mysql_real_escape_string($city)."%' OR
	vin LIKE '%".mysql_real_escape_string($vin)."%' OR
	year LIKE '%".mysql_real_escape_string($year)."%' OR
	make LIKE '%".mysql_real_escape_string($make)."%' OR
	model LIKE '%".mysql_real_escape_string($model)."%'
	ORDER BY name ASC";
	
	// run query
	
	$result = mysql_query($query) or die (mysql_error());
	
	// find out the number of matches
	
	$number = mysql_num_rows($result);
	
	// loop through results and get variables
	
	while ($row=mysql_fetch_array($result)){
		
		$d_name = $row['name'];
		$d_address = $row['address'];
		$d_city = $row['city'];
		$d_vin = $row['vin'];
		$d_year = $row['year'];
		$d_make = $row['make'];
		$d_model = $row['model'];
		
		echo "<table width='50%' border='1'>
  <tr>
    <th scope='col'> </th>
    <th scope='col'>Name</th>
    <th scope='col'>Address</th>
    <th scope='col'>City</th>
    <th scope='col'>Vin</th>
    <th scope='col'>Year</th>
    <th scope='col'>Make</th>
    <th scope='col'>Model</th>
    <th scope='col'>Company</th>
  </tr>";
  
  echo " <tr>
    <td></td>
    <td>$d_name</td>
    <td>$d_address</td>
    <td>$d_city</td>
    <td>$d_vin</td>
    <td>$d_year</td>
    <td>$d_make</td>
    <td>$d_model</td>
    <td>$d_company</td>
  </tr>";
  
  
		
		
		
		
	} // end while 
	
	echo "</table>";	
	
	
	
	
	
} // End check for if submit button has been activated.
	?>

Here is an image of the raw return (I got this by searching for "George" in the debtor's name box only; an entry that doesn't even resemble the data, and should have got a no return. Still, the output is the 2 sample entries I have in my MySQL database.

 

 

 

Thanks (again) in advance.

Edited November 27, 2013 by Doc20

[requinix]

Ah, but you left all the other fields empty, and ended up with conditions like

address LIKE '%%'

which will, of course, match anything and everything.

 

Only add those conditions in when there is a value to search for.

[Doc20]

That makes complete sense!   Here is my query...  I've tried to make it so it does not set a variable if the field is empty.  How can I modify the query now so it so those conditions are only used if there is something in the field?

	if(!empty($_POST['debt_name']) and isset($_POST['debt_name'])){
    $name = $_POST['debt_name'];
}
if(!empty($_POST['debt_address']) and isset($_POST['debt_address'])){
    $address = $_POST['debt_address'];
}if(!empty($_POST['debt_city']) and isset($_POST['debt_city'])){
    $city = $_POST['debt_city'];
}if(!empty($_POST['debt_vin']) and isset($_POST['debt_vin'])){
    $vin = $_POST['debt_vin'];
}if(!empty($_POST['debt_year']) and isset($_POST['debt_year'])){
    $year = $_POST['debt_year'];
}if(!empty($_POST['debt_make']) and isset($_POST['debt_make'])){
    $make = $_POST['debt_make'];
}if(!empty($_POST['debt_model']) and isset($_POST['debt_model'])){
    $model = $_POST['debt_model'];
}
	

	
	//SQL statement to select relevant data
	
	$query = "SELECT * FROM `account`
	WHERE 
	name LIKE '%".mysql_real_escape_string($name)."%' OR
	address LIKE '%".mysql_real_escape_string($address)."%' OR
	city LIKE '%".mysql_real_escape_string($city)."%' OR
	vin LIKE '%".mysql_real_escape_string($vin)."%' OR
	year LIKE '%".mysql_real_escape_string($year)."%' OR
	make LIKE '%".mysql_real_escape_string($make)."%' OR
	model LIKE '%".mysql_real_escape_string($model)."%'
	ORDER BY name ASC";

I have a feeling somehow using if statements within my query (using a bunch of concatenation/additions) is in order?  Or is there an easier way?

 

Ideally, I'd like this one search form to be able to handle any combination of inputs in the search boxes.. But if push came to shove, I could make a separate page/form for each criteria and just make a page for "Search by name", "Search by address", etc..

Edited November 27, 2013 by Doc20

[mac_gyver]

you would build an array of non-empty search terms and implode it to produce the WHERE term. see this post - http://forums.phpfreaks.com/topic/281041-adding-filters-to-sql-databasephp-query/?do=findComment&comment=1445605

[Doc20]

That did the trick!  Thanks a bunch!