CODEWITHSUNDEEP
phpmysqlfull-text-search
user1040259
user1040259

Reputation: 6509

PHP search multiple fields issue

I'm working through this tutorial online: http://goo.gl/qnk6U

The database table: ajax_search

Firstname | Lastname | Age | Hometown | Job
-------------------------------------------
Joe       | Smith    | 35  | Boulder  | CIA   
Steve     | Apple    | 36  | Denver   | FBI

(Types are all varchar except age is an int.)

My question. Is the sql select statement below written correctly to query "Joe 35"? For some reason, I can only query "Joe" and it works, but not combining search terms. 

$sql = "select * from ajax_search where FirstName like '%$rec%' or LastName like '%$rec%' or Age like '%$rec%' or Hometown like '%$rec%'";

Upvotes: 1

Views: 1021

Answers (2)

castor
castor

Reputation: 583

You need to split that sting from search query:

$columns = array("Firstname", "Lastname", "Age", "Hometown", "Job");
$string = ""; // acquire query string
$split = explode(" ", $string);
$search_words = array();
foreach ($split as $word) {
   $search_words[] = $word;
}

Create query to search all over the fields:

$first = true;
$sql = "select * from ajax_search where";
foreach ($search_words as $word) {
   foreach ($columns as $col) {
      $sql .= (($first) ? " " : " OR") . "`$col` LIKE \"%" . $word . "%\"";
      $first = false;
   }
}

Then run this query.

Another and also a better solution would be more complicated word(tag) based indexing, because this can generate morbid queries when used with more words.

Upvotes: 1

Adam Hopkinson
Adam Hopkinson

Reputation: 28795

Assuming your query is "Joe 35", then no. Your query matches any row where any of the four fields contains "Joe 35" (in a single field). To query for a user with name Joe and age 35, you'd need to split the search query and do something like:

WHERE Firstname LIKE "$firstname" AND Age LIKE "$age"

Upvotes: 1

Related Questions