Search returning no results in "reverse" order

Question

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

The like operator doesn't mix the order of the words. Maybe [Is there a combination of like and in sql](https://stackoverflow.com/questions/3014940/is-there-a-combination-of-like-and-in-in-sql) will answer your question. — Juan Eizmendi, Jul 21 '21 at 16:48
Juan, I've set all columns as full index and it hasn't changed the results. — drodsher, Jul 21 '21 at 22:08

score 1 · Answer 1 · answered Jul 21 '21 at 16:52

You could break up your query by spaces and test for each.

$query = "SELECT * FROM db";
$where = [];
$values = [];
$ss = [];
if($_POST['query'] != '')
{ 
    foreach( explode(' ', $_POST['query']) as $p) {
      $postq = mysql_real_escape_string($p);
      $where[]= "(CONCAT(Name,Address,City,State,Zip) LIKE ? )";
      $values[] = "%$postq%";
      $ss[]='s';
    }
    $query .= " WHERE " . implode(" OR ", $where);
}


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

$statement = $connect->prepare($query);
if(count($values)>0) $statement->bind_param(implode('',$ss), ...$values);

$statement->execute();

I've tried implementing this code but it's not retrieving any results at all. It's likely just not agreeing with the rest of this ajax code for display. — drodsher, Jul 21 '21 at 22:10

score 1 · Accepted Answer · answered Jul 22 '21 at 00:47

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();
}

?>

Thank you! That is exactly what I needed but couldn't wrap my brain around. Kudos — drodsher, Jul 22 '21 at 16:18

Search returning no results in "reverse" order

2 Answers2