PHP Searching Multiple Fields

[AmieFeetandToes]

Hello Everyone!

I am working on a search form in PHP for a client's website. So everything works, the search brings results. What I am wanting to accomplish now is to be able to search more than one field, specifically first name and last name. Let's say if a user inputs let's say a first name AND a last name into the search input field and then searches, I want it to display results based on that. Right now if you try it, it says there are no results. My thought is how I've coded it is for the search box to be able to search only in ONE column field for per row.

Here is that part of my code:

// QUERY THE DATABASE TABLE
$sql="SELECT * FROM contracts 
WHERE Status LIKE '%" . $searchTermDB . "%' 
OR DealerName LIKE '%" . $searchTermDB ."%' 
OR Dealer LIKE '%" . $searchTermDB ."%' 
OR Contract LIKE '%" . $searchTermDB ."%' 
OR LastName LIKE '%" . $searchTermDB ."%' 
OR FirstName LIKE '%" . $searchTermDB ."%' 
OR EffDate LIKE '%" . $searchTermDB ."%' 
OR TermDate LIKE '%" . $searchTermDB ."%' 
OR Year LIKE '%" . $searchTermDB ."%' 
OR Make LIKE '%" . $searchTermDB ."%' 
OR Model LIKE '%" . $searchTermDB ."%' 
OR Serial LIKE '%" . $searchTermDB ."%' 
OR TermMeter LIKE '%" . $searchTermDB ."%' 
OR Plan LIKE '%" . $searchTermDB ."%'";

Let me know of any suggestions. Thanks!

[chrishea]

You could / should generate the search based on specific fields that are entered in your input form (rather than a generalized search term). Something like:

$sql = "select from contracts where ";

if ($first_name <> "") {
  $sql .= "FirstName like '$first_name' ";
  $and_sw = 1;
}

if ($last_name <> "") {
  if ($and_sw == 1) {
    $sql .= " and ";
  }
  $sql .= "LastName like '$last_name' ";
  $and_sw = 1;
}

... etc ...

This will give you a more efficient search that will handle as many fields as you want to define, as long as you explicitly define them in your input form. If you still want to be able to search every column of the db for the search value and do this for multiple search terms you could probably do something similar with an AND for your second search term against all of the columns but that could be a pretty inefficient search.

[colweb]

Because you have so many search items (14 if I count right), I would do it this way. The form uses the same name (search) but at a number for each input field. In the php code you get all the posted fields in an array and finally create the query. Like so:

<?php

$action = isset($_POST['action']) ? $_POST['action'] : null;
if ($action == "startSearch") {
	$elem = array('status', 'dealername', 'dealer', 'contract', 'lastname',
		      'firstname', 'effdata', 'termdate', 'year', 'make',
		      'model', 'serial', 'termmeder', 'plan');
	$searchTerm = array();

	// $searchTemp[0..13] will be filled with the posted variables or will be null.
	for ($i=0; $i < 14; $i++) {
		$searchTerm[] = isset($_POST['search'.$i]) ? $_POST['search'.$i] : null;
	}

	// build the query
	$at_and = FALSE;
	$sql = "SELECT * FROM contracts WHERE ";
	for ($i=0; $i <14; $i++) {
		if ($searchTerm[$i] <> "") { 
			if ($at_and) {
				$sql .= " AND ";
			}
			$sql .= $elem[$i]." LIKE '".$searchTerm[$i]."' ";
			$at_and = TRUE;
		}
	}
	
// 	If the user typed my-last-name behind lastname and my-first-name behind 
//	firstname, $sql will contain:
//	SELECT * FROM contracts WHERE lastname LIKE 'my-last-name' AND firstname LIKE 'my-first-name' 
	exit();
}

echo "<form method=\"post\" action=\"".$_SERVER['PHP_SELF']."\">
	<input type=\"hidden\" name=\"action\" value=\"startSearch\">
	<table border=\"0\">
	<tr><td>Status</td><td><input type=\"text\" name=\"search0\"/></td></tr>
	<tr><td>Dealername</td><td><input type=\"text\" name=\"search1\"/></td></tr>
	<tr><td>Dealer</td><td><input type=\"text\" name=\"search2\"/></td></tr>
	<tr><td>Contract</td><td><input type=\"text\" name=\"search3\"/></td></tr>
	<tr><td>Lastname</td><td><input type=\"text\" name=\"search4\"/></td></tr>
	<tr><td>Firstname</td><td><input type=\"text\" name=\"search5\"/></td></tr>
	<tr><td>EffDate</td><td><input type=\"text\" name=\"search6\"/></td></tr>
	<tr><td>TermDate</td><td><input type=\"text\" name=\"search7\"/></td></tr>
	<tr><td>Year</td><td><input type=\"text\" name=\"search8\"/></td></tr>
	<tr><td>Make</td><td><input type=\"text\" name=\"search9\"/></td></tr>
	<tr><td>Model</td><td><input type=\"text\" name=\"search10\"/></td></tr>
	<tr><td>Serial</td><td><input type=\"text\" name=\"search11\"/></td></tr>
	<tr><td>TermMeder</td><td><input type=\"text\" name=\"search12\"/></td></tr>
	<tr><td>Plan</td><td><input type=\"text\" name=\"search13\"/></td></tr>
	<tr><td colspan=\"2\" align=\"center\"><input type=\"submit\" value=\"Search\"/></td></tr>
	</table>
	</form>";
?>