Multi-user search in PHP / MySQL

Question

I am trying to create a search that searches for a few words. My first attempt did not produce any results and looks like this:

require_once('database_conn.php'); if($_POST){ $explodedSearch = explode (" ", $_POST['quickSearch']); foreach($explodedSearch as $search){ $query = "SELECT * FROM jobseeker WHERE forename like '%$search%' or surname like '%$search%' ORDER BY userID LIMIT 5"; $result = mysql_query($query); } while($userData=mysql_fetch_array($result)){ $forename=$userData['forename']; $surname=$userData['surname']; $profPic=$userData['profilePicture']; $location=$userData['location']; echo "<div class=\"result\"> <img class=\"quickImage\" src=\"" . $profPic. "\" width=\"45\" height=\"45\"/> <p class=\"quickName\">" . $forename . " " . $surname . "</p> <p class=\"quickLocation\"> " . $location . "</p> </div>"; } } 

I also tried the following, which yielded results, but as you can imagine, I got duplicate results for every word I entered:

 if($_POST){ $explodedSearch = explode (" ", $_POST['quickSearch']); foreach($explodedSearch as $search){ $query = "SELECT * FROM jobseeker WHERE forename like '%$search%' or surname like '%$search%' ORDER BY userID LIMIT 5"; $result .= mysql_query($query); while($userData=mysql_fetch_array($result)){ $forename=$userData['forename']; $surname=$userData['surname']; $profPic=$userData['profilePicture']; $location=$userData['location']; echo "<div class=\"result\"> <img class=\"quickImage\" src=\"" . $profPic. "\" width=\"45\" height=\"45\"/> <p class=\"quickName\">" . $forename . " " . $surname . "</p> <p class=\"quickLocation\"> " . $location . "</p> </div>"; } } } 

I pretty much do not understand how to do this, any help would be greatly appreciated.

EDIT:

 if($_POST){ $quickSearch = $_POST['quickSearch']; $explodedSearch = explode (" ", trim($quickSearch)); $queryArray = array(); foreach($explodedSearch as $search){ $term = mysql_real_escape_string($search); $queryArray[] = "forename like '%" . $term . "%' surname like '%" . $term . "%'"; } $implodedSearch = implode(' or ', $queryArray); $query="SELECT * FROM jobseeker WHERE ($implodedSearch) ORDER BY userID LIMIT 5"; $result = mysql_query($query); while($userData=mysql_fetch_array($result, MYSQL_ASSOC)){ $forename=$userData['forename']; $surname=$userData['surname']; $profPic=$userData['profilePicture']; $location=$userData['location']; echo "<div class=\"result\"> <img class=\"quickImage\" src=\"" . $profPic. "\" width=\"45\" height=\"45\"/> <p class=\"quickName\">" . $forename . " " . $surname . "</p> <p class=\"quickLocation\"> " . $location . "</p> </div>"; } } 

Answer 1

I have been working on the same question (keyword search) for a while, and here is how I did it:

 $words = $_POST['keywords']; if(empty($words)){ //redirect somewhere else! } $parts = explode(" ",trim($words)); $clauses=array(); foreach ($parts as $part){ //function_description in my case , replace it with whatever u want in ur table $clauses[]="function_description LIKE '%" . mysql_real_escape_string($part) . "%'"; } $clause=implode(' OR ' ,$clauses); //select your condition and add "AND ($clauses)" . $sql="SELECT * FROM functions WHERE user_name='{$user_name}' AND ($clause) "; $results=mysql_query($sql,$connection); if(!$results){ redirect("errors/error_db.html"); } else if($results){ $rows = array(); <?php while($rows = mysql_fetch_array($results, MYSQL_ASSOC)) { // echo whatever u want ! } ?> 

- This is what it looks like when I tried to start it using the FULLTEXT search: But you have to set the table type as "MyISAM"

 <?php $words = mysql_real_escape_string($_POST['function_keywords']); if(empty($words)){ redirect("welcome.php?error=search_empty"); } //if the columns(results)>1/2(columns) => it will return nothing!(use "NATURAL LANGUAGE"="BOOLEAN") $sql="SELECT * FROM functions WHERE MATCH (function_description) AGAINST ('{$words}' IN NATURAL LANGUAGE MODE)"; $results=mysql_query($sql,$connection); if(!$results){ redirect("errors/error_db.html"); } else if($results){ $rows = array(); while($rows = mysql_fetch_array($results, MYSQL_ASSOC)) { // echo } } ?> 

Answer 2

It is possible that you are looking for full-text MySQL searches .

In your example, you can do something like:

 if ($_SERVER['REQUEST_METHOD'] == 'POST') { $search = $_POST['quickSearch']; // Todo: escape $search $sql = " SELECT *, MATCH (`forename`) AGAINST ('{$search}' IN NATURAL LANGUAGE MODE) AS `score` FROM `jobseeker` WHERE MATCH (`forename`) AGAINST ('{$search}' IN NATURAL LANGUAGE MODE)"; // Todo: execute query and gather results } 

Note that you need to add the FULLTEXT index to the forename column.

Answer 3

Take a look at MySQL full-text search if you should use MySQL. Otherwise, check out SOLR , which is a full-text search engine. You can use MySQL and SOLR in combination to provide enterprise-level search capabilities for your applications.

Answer 4

that's what i did

 if (isset($_POST['search'])){ $words = mysql_real_escape_string($_POST['searchfield']); $arraySearch = explode(" ", trim($words)); $countSearch = count($arraySearch); $a = 0; $query = "SELECT * FROM parts WHERE "; $quote = "'"; while ($a < $countSearch) { $query = $query."description LIKE $quote%$arraySearch[$a]%$quote "; $a++; if ($a < $countSearch) { $query = $query." AND "; } } $result=mysql_query($query) or die(error); 

// you could just leave it here, short and sweet, but I added an extra code, if it does not have any results, then it searches for a word, not the words of both. //

 $num = mysql_num_rows($result); if ($num == 0){ $a = 0; $query = "SELECT * FROM parts WHERE "; while ($a < $countSearch) { $query = $query."description LIKE $quote%$arraySearch[$a]%$quote "; $a++; if ($a < $countSearch) { $query = $query." OR "; $msg = "No exact match for: $words. Maybe this is what you're looking for though? If not please try again."; } } } $result=mysql_query($query) or die($query); if (mysql_num_rows($result) == 0){ $msg = "No results, please try another search"; } }