Mysql "like" get most relevant multiple searchwords results

Question

I want a query that would return most close to the keywords from the search field. I search in 4 columns. title, description, author, take_place. This is the current code, that returns results from all columns. i want a more specific search result.

$v = explode(' ', strtolower($searchwords));
if($c != ''){ $and = 'AND cat_id = '.$c; }
foreach($v as $val){
  $query parts[] = "'%".mysql_real_escape_string($val)."%'";
}
$title = implode(' OR title LIKE ', $query_parts);
$desc = implode(' OR desc LIKE ', $query_parts);
$author = implode(' OR author LIKE ', $query_parts);
$take_place = implode(' OR take_place LIKE ', $query_parts);

$sql_res=mysql_query("select * from nt_seminars where ((title like {$title}) or
 (description like {$description}) or (author like {$author}) or 
(take_place like {$take_place})) $and ORDER BY date;");

This query returns every result that has any of the words, i want to retrieve most relevant combo search keywords. it would be like:

if results in author and take_place, retrieve those only. Somehow, replace the OR with AND, but then it wouldn't return any results at all. This query is triggered by ajax.

Got something better, but still not what i wanted..

$q = $_POST['searchword'];
  $arraySearch = explode(" ", $_POST['searchword']);
  // table fields to search
  $arrayFields = array(0 => "title", 1 => "city", 2=> "author");
  $countSearch = count($arraySearch);
  $a = 0;
  $b = 0;
  $query = "SELECT * FROM ".$table." WHERE (";
  $countFields = count($arrayFields);
  while ($a < $countFields)
   {
    while ($b < $countSearch)
    {
      $query = $query."$arrayFields[$a] LIKE '%$arraySearch[$b]%'";
      $b++;
      if ($b < $countSearch)
      {
        $query = $query." AND ";
      }
    }
    $b = 0;
    $a++;
    if ($a < $countFields)
    {
      $query = $query.") OR (";
    }
  }
  $query = $query.")";
  $query_result = mysql_query($query);

  if(mysql_num_rows($query_result) < 1)
  {
   echo '<span class="no-borders">No results found</span>';
  }
  else
  {
   echo '<ul class="list-group no-radius m-b-none m-t-n-xxs list-group-lg no-border">';
    // output list of articles
    while($row = mysql_fetch_assoc($query_result))
    {
//output loop
    }
  }

This returns keywords found only at one of columns. I need an elimination type of query..

This will help: http://stackoverflow.com/questions/12344795/count-the-number-of-occurences-of-a-string-in-a-varchar-field — gvee, Dec 13 '13 at 16:18
I want to search in multiple columns. if found hit on "author" get all rows that contain author="value", BUT if found hit on author="michael" and take_place="Greece", show me all these rows. The more hits are found, the more relevant the query will be. — DecoderNT, Dec 13 '13 at 17:31

score 0 · Accepted Answer · answered Dec 14 '13 at 04:37

FOUND IT.! in order to search on multiple columns, with multiple keywords using ajax keyup function i used

    $table = "nt_seminars";
// explode search words into an array
$q = $_POST['searchword'];
if($_GET[cat_id] != ''){ $and = 'AND cat_id='.$_GET[cat_id]; }
      $arraySearch = explode(" ", $_POST['searchword']);
  // table fields to search
  $arrayFields = array(0 => "title", 1 => "city", 2=> "author");
  $countSearch = count($arraySearch);
  $a = 0;
  $b = 0;
  $query = "SELECT *, CONCAT_WS(' ',title,author,city) FROM ".$table." WHERE (";
  $countFields = count($arrayFields);
  while ($a < $countFields)
  {
    while ($b < $countSearch)
    {
      $query = $query."CONCAT_WS(' ',title,author,city) LIKE '%$arraySearch[$b]%'";
      $b++;
      if ($b < $countSearch)
      {
        $query = $query." AND ";
      }
    }
    $b = 0;
    $a++;
    if ($a < $countFields)
    {
      $query = $query.") OR (";
    }
  }
  $query = $query." $and AND visible = 1 ORDER BY date desc)";
  $query_result = mysql_query($query) or die ($e = mysql_error());

  if(mysql_num_rows($query_result) < 1)
  {
   echo '<span class="no-borders">No results found</span>';
  }
  else
  {
    while($row = mysql_fetch_assoc($query_result))
    {
      // output whatever you want here for each search result
    }
  }

basically, it is all about, combining all the columns, separating them with a space, so as to retrieve results by letters and not words(FULLTEXT). i used it for live search outputting to list Hope that helps!

Mysql "like" get most relevant multiple searchwords results

1 Answers1