CODEWITHSUNDEEP
phpsearch
Guernica
Guernica

Reputation: 246

Searching mysql database - php

I'm trying to mod a search routing, i have two fields, artist & title, when i search for example Foo Fighters the pretender, i don't get any results, whereas if i search individually i do. Any help would be much appreciated thanks.

  $arraySearch = explode(" ", $searchvalue);
  $arrayFields = array(0 => "Artist", 1 => "Title");
  $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);
  echo '<h1>Your Search Results</h1>'."\n\n";
  if(mysql_num_rows($query_result) < 1)
  {
    echo '<p>No matches found for "'.$searchvalue.'"</p>';
  }
  else
  {
    echo '<p>Search Results for "'.$searchvalue.'":</p>'."\n\n";
    while($row = mysql_fetch_assoc($query_result))
    {
      // output

    }
  }

Upvotes: 1

Views: 831

Answers (3)

drew010
drew010

Reputation: 69977

This search function is very inefficient. Since you are searching on a wildcard %blah%, it has to do a full table scan to find any records. That is, if you have 1 million records, it has to read all 1 million records in order to find your search results. On a busy site with a large table, this can kill your performance and database server.

If you are using the MyISAM engine for the table, I would recommend adding a FULLTEXT index to the artist and title columns and then performing a fulltext search like this:

SELECT * FROM `table` WHERE
MATCH(`artist`, `title`)
AGAINST('foo fighters the pretender');

As written by the OP, here is a query that sorts by match relevance on the search query

$search = $db->escape('foo fighters the pretender');

$query = "SELECT *,
          MATCH(Artist, Title) AGAINST('$search' IN BOOLEAN MODE) AS relevance
          FROM table
          WHERE
            MATCH (Artist, Title)
            AGAINST('$search' IN BOOLEAN MODE)
          ORDER BY relevance DESC";

Upvotes: 3

Leandro
Leandro

Reputation: 376

After you close the parentheses of the query, put a var_dump($query) so you will understand what is happening.

eg: The following code outputs: string(123) "SELECT * FROM WHERE (Artist LIKE '%foo%' AND Artist LIKE '%fighters%') OR (Title LIKE '%foo%' AND Title LIKE '%fighters%')" 

<?php

$searchvalue = 'foo fighters';

$arraySearch = explode(" ", $searchvalue);
  $arrayFields = array(0 => "Artist", 1 => "Title");
  $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.")";

  var_dump($query);
?>

Upvotes: 0

Tim Withers
Tim Withers

Reputation: 12069

Change this:

$query = $query." AND ";

to this:

$query = $query." OR ";

Essentially you are saying SELECT... WHERE (field1 like 'foo%' AND field1 like 'bar%'). You will never find a row where field1 is like both words unless they were the exact same word. Its like saying SELECT ... WHERE id=1 AND id=2, not going to pull any results, where as SELECT ... WHERE id=1 OR id=2 will pull rows 1 and 2.

Upvotes: 1

Related Questions