Search returning no results in “reverse” order

I have a database filled with addresses. 6 columns (id, Name, Address, City, State, Zip, dt)

My code is run with ajax for live search. Currently I can mostly find what I’m looking for with my queries. The problem I’m running into is this. If I search for “90210 Steve Jones” I get no results but if I search for “Steve Jones 90210” it finds the row(s).

Here is my code:

$query = "SELECT * FROM db";

if($_POST['query'] != '')
{
    $postq = mysql_real_escape_string($_POST['query']);
    $query .= "WHERE CONCAT(Name,Address,City,State,Zip) LIKE '%".str_replace(' ', '%', $postq)."%'";
}


$query .= 'ORDER BY Name ASC, dt DESC ';

$statement = $connect->prepare($query);
$statement->execute();

Any help would be appreciated

Answer

One of the solutions is to split the search string by spaces and then do a multiple like comparison operations

So the code is:

<?php

if($_POST['query'] != '') {
$postq = mysql_real_escape_string($_POST['query']);

$pieces = explode(" ", $postq);

$index=0;
$substring="";

  while ($index < count($pieces))  {
     $substring .=" CONCAT(Name,Address,City,State,Zip) like '%" . $pieces[$index] . "%'"   ;

     if ($index !=count($pieces)-1){ 
         $substring .= " and "; 
     }

    $index++;
  }

$query = "SELECT * FROM db where ";
$query .=  $substring; 
$query .= ' ORDER BY Name ASC, dt DESC ';


$statement = $connect->prepare($query);
$statement->execute();
}

?>

Advertisement

Answer