[PHP]Search On Multiple Parameters.

Kassem · Dec 11, 2010

Hey everyone,

I'm currently building a web directory website. The user is presented with a form that allows him to search by First Name, Last Name, or Email Address. Or alternatively, search by two or all of the parameters.

Searching by one parameter is very straight forward. But I'm having issues with searching by multiple parameters. I cannot know how many of the textbox the user is going to fill, for that I wrote three functions (one for each). Then I thought I'd check whether the textbox was filled by the user and call its corresponding function. Finally, I thought I'd aggregate the results from the three functions into one array. But what about duplicates? And what will the $result variable be holding when returned from each function (I thought it would be an array but it's not)? Here's what I've got so far:

functions.php

<?php
	require_once 'DBConnection.php';

	function findPersonByFname($name) {
		$db = new DBConnection();
		$db->GetConnection();
		$str = sprintf('SELECT * FROM	people WHERE FirstName LIKE %s', mysql_real_escape_string($name));
		$result = mysql_query($str);
		
		return $result;
	}
	
	function findPersonByLname($name) {
		$db = new DBConnection();
		$db->GetConnection();
		$str = sprintf('SELECT * FROM	people WHERE LastName LIKE %s', mysql_real_escape_string($name));
		$result = mysql_query($str);
		
		return $result;
	}
	
	function findPersonByEmail($email) {
		$db = new DBConnection();
		$db->GetConnection();
		$str = sprintf('SELECT * FROM	people WHERE Email = %s', mysql_real_escape_string($email));
		$result = mysql_query($str);
		
		return $result;
	}
?>

index.php (which contains the search form)

<?php 
			if(isset($_POST['findPerson'])) {
				$allResults = array();
				if($_POST['fname'] != '')
					$allResults = array_merge($allResults, findPersonByFname($_POST['fname']));
				if($_POST['lname'] != '')
					$allResults = array_merge($allResults, findPersonByLname($_POST['lname']));
				if($_POST['email'] != '')
					$allResults = array_merge($allResults, findPersonByEmail($_POST['email']));
				
				$allResults = array_unique($allResults);
				
				if(count($allResults) == 0)
					echo "<p>No results were found!</p>";
				else {
					$counter = 0;
					$tags = sprintf('
                	<table class="resultsTable">
						<tr>
							<td>Click on name to view additional information.</td>
							<td> </td>
							<td class="resultCount">%s Results Found</td>
						</tr>', count($allResults));
					foreach($allResults as $row) {
						if($counter % 2 == 0) {
							$tags .= sprintf('
							<tr class="even">
								<td>%s</td>
								<td>%s</td>
								<td>%s</td>
							</tr>', $row['FirstName']." ".$row['LastName'], $row['Occupation'], $row['Phone']);
						} else {
							$tags .= sprintf('
							<tr class="odd">
								<td>%s</td>
								<td>%s</td>
								<td>%s</td>
							</tr>', $row['FirstName']." ".$row['LastName'], $row['Occupation'], $row['Phone']);
						}
						$counter++;
				}
				$tags .= '</table>';
				echo $tags;
				}
			} else {
				echo '<p class="searchNotice">Please fill in the form above to search for people you are looking for.</p>';
			}
		 ?>

Now when I search the database I get the following errors:

Warning: array_merge() [function.array-merge]: Argument #2 is not an array in C:\wamp\www\nabs\root\index.php on line 46

Warning: array_unique() expects parameter 1 to be array, null given in C:\wamp\www\nabs\root\index.php on line 52

Any suggestions?

P.S: I haven't written any PHP code for too long! I actually struggled to connect to the database! Seems like I got caught in the .NET world xD

Jjsaade · Dec 11, 2010

the 1st error is because your functions in functions.php are not returning arrays, if successful, $result would be equal to a Resource Id, which then should transformed to:

while($row = mysql_fetch_array($result)){

}
//in each loop $row is an array.

and the function array_merge I presume needs to have the second argument as an array.

and the second error is caused by the 1st.

-------------------------------------------
on a side note, to implement the problem you have (searching with several criteria), you can just build one long Search query, does the user just want to search by name or by age or can he mix an match?

why not have a search.php that loops on the POST or GET variables and creates one long search string:
so u can have something like:
search.php?name=toto&age=21&email=bab
or
search.php?email=bab

then in search.php:
$query_str ="";
$queries = 0;
foreach($_GET as $name => $value) {
if($queries > 0) $query_str.=" AND";
$query_str .= " ".$name." = ".$value." ";
}

//add the above to a single select,
//run and return result

Kassem · Dec 11, 2010

@ZeRaW: Thanks for the help man. I was actually trying to avoid that, because the email has to be exactly the same like the one entered by the user, while the first name and last name could be close (hence why I used the LIKE operator in the query). I'll get back for help in case I needed it. Thanks again :)

Kassem · Dec 13, 2010

<?php
	include 'DBConnection.php';
	function findThePerson($fname, $lname, $email) {
		if(isset($_POST['findPerson'])) {
			$db = new DBConnection();
			$connex = $db->GetConnection();
			
			$query = sprintf("SELECT * FROM people WHERE FirstName LIKE %s OR LastName LIKE %s OR Email = %s", 
								mysql_real_escape_string($fname),
								mysql_real_escape_string($lname),
								mysql_real_escape_string($email)
							);
							
			$result = mysql_query($query);
			return $result;
		}
	}
?>

Why is this returning a boolean? Shouldn't $result be a resource that should then be used in mysql_fetch_array($result) to retrieve the data?

Jjsaade · Dec 13, 2010

it will return FALSE in case the query i not successful otherwise it will return a resource ID.
i think the erorr in the querry is that you need to compare string to strings ie:

... LIKE '%s' .... LIKE '%s' ... = '%s'

Kassem · Dec 13, 2010

You're absolutely right! Thanks a lot :)