Searching mysql database - php

Question

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

    }
  }

drew010 · Accepted Answer · 2012-07-25 19:01:41Z

3

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";

edited Jul 25, 2012 at 19:01

answered Jul 25, 2012 at 0:07

drew010

70.3k11 gold badges144 silver badges174 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

Guernica Over a year ago

Thanks man this seems the way forward for me, although i don't really have time to redo things just now, its a mobile site/app, so it will be getting loads of updates and will deffo be implemented in the next major one, currently there is 80,000 records and around 30 users max at any one time i would estimate, do you think my script would suffice for now?

drew010 Over a year ago

Run some EXPLAIN SELECT ... queries on a typical search to see how many rows its looking at. While running under normal load, check your uptime and see what the load average is. Looking at disk IOPS is also a good metric. This blog post on profiling by Percona is a good thing to try as well. If your searches and/or site start to run slow, it could likely be due to this issue. It largely depends on the hardware and disks your server is running on.

Guernica Over a year ago

no need for that, i'll just take your suggestion, your obviously really intelligent and you know more than me in this area ha :P That is just pretty much garbage the script i was using. Fulltext it is.

drew010 Over a year ago

You can alter the table to have a fulltext index like this: ALTER TABLE your_table ADD FULLTEXT(title, artist); to add the index. Then its just a matter of modifying your searches to use MATCH...AGAINST syntax.

Guernica Over a year ago

i've tried it and i keep getting an error, mysql fetch array, invalid argument, any ideas ? $query = "SELECT * FROM table WHERE MATCH(Artist, Title) AGAINST ('foo fighters pretender')"; $result = mysql_query($query); var_dump($query); while ($myrow = mysql_fetch_array($result)) { echo $myrow[Title]; }

|

Tim Withers · Accepted Answer · 2012-07-25 00:00:19Z

1

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.

answered Jul 25, 2012 at 0:00

Tim Withers

12.1k5 gold badges45 silver badges68 bronze badges

Comments

Leandro · Accepted Answer · 2012-07-25 00:15:39Z

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);
?>

Collectives™ on Stack Overflow

Searching mysql database - php

3 Answers 3

12 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

12 Comments

Comments

Comments

Related