Database search like google

Question

I currently have a search option on my PHP+MYSQL website.

The MYSQL query is currently something like "SELECT pageurl WHERE name LIKE '%$query%'.

The reason I posted here is because I noticed that if the name of one of my products is "Blue Bike" and someone looks for "Bike Blue", no results are returned.

I am looking for a solution to this because I know that if I type on google same word, something appears.

I was thinking to create a PHP function to mix up all the words from the query if the query is having 4 or fewer words, generating around 24 queries.

Is there an easier solution to this?

Thanks for your time

Answer 1

As to not let this go without a working answer:

<?php
    $search = 'this is my search';

    $searchSplit = explode(' ', $search);

    $searchQueryItems = array();
    foreach ($searchSplit as $searchTerm) {
        /*
         * NOTE: Check out the DB connections escaping part 
         * below for the one you should use.
         */
        $searchQueryItems[] = "name LIKE '%" . mysqli_real_escape_string($searchTerm) . "%'";
    }

    $query = 'SELECT pageurl FROM names' . (!empty($searchQueryItems) ? ' WHERE ' . implode(' AND ', $searchQueryItems) : '');
?>

DB connections escaping

mysqli_:

Keep using mysqli_real_escape_string or use $mysqli->real_escape_string($searchTerm).

mysql_:

if you use mysql_ you should use mysql_real_escape_string($searchTerm) (and think about changing as it's deprecated).

PDO:

If you use PDO, you should use trim($pdo->quote($searchTerm), "'").

Answer 2

use full text search instead of like

full text search based on indexed text and is very faster and beter than using like.

see this article for more information about full text search

Answer 3

What you are looking for is fulltext search. Try Sphinx, it is very fast and integrates well with MySQL.

Sphinx website

Answer 4

I wrote a function that approaches Google's operation taking into account the double quotes for the elements to search as a whole block. It does NOT take into account the - or * instructions.

table: MySQL table to consider

cols: array of column to parse

searchParams: search to process. For example: red mustang "Florida 90210"

function naturalQueryConstructor($table, $cols, $searchParams) {

    // Basic processing and controls
    $searchParams = strip_tags($searchParams);
    if( (!$table) or (!is_array($cols)) or (!$searchParams) ) {
        return NULL;
    }
    // Start query
    $query = "SELECT * FROM $table WHERE ";

   // Explode search criteria taking into account the double quotes
    $searchParams = str_getcsv($searchParams, ' ');

   // Query writing
    foreach($searchParams as $param) {
      if(strpos($param, ' ') or (strlen($param)<4)) {
        // Elements with space were between double quotes and must be processed with LIKE.
        // Also for the elements with less than 4 characters. (red and "Florida 90210")
        $query .= "(";
        // Add each column
        foreach($cols as $col) {
            if($col) {
                $query .= $col." LIKE '%".$param."%' OR ";
            }
        }
        // Remove last ' OR ' sequence
        $query = substr($query, 0, strlen($query)-4);
        // Following criteria will added with an AND
        $query .= ") AND ";
      } else {
        // Other criteria processed with MATCH AGAINST (mustang)
        $query .= "(MATCH (";
        foreach($cols as $col) {
            if($col) {
                $query .= $col.",";
            }
        }
        // Remove the last ,
        $query = substr($query, 0, strlen($query)-1);
        // Following criteria will added with an AND
        $query .= ") AGAINST ('".$param."' IN NATURAL LANGUAGE MODE)) AND ";
      }
  }
  // Remove last ' AND ' sequence
  $query = substr($query, 0, strlen($query)-5);
  return $query;
}

Thanks to the stackoverflow community where I found parts of this function!

Answer 5

To have a google like search you'd need many database and index nodes, crazy algorithms.. now you come up with a SELECT LIKE ... lol :D

MySQL is slow in searching, you'd need fulltext and index set properly (MyISAM or Aria Engine). Combinations or different entities to search for are almost not implementable properly AND fast.

I'd suggest to setup an Elasticsearch server which is based on Apache's Lucene. This searchs very fast and is easy to maintain. And you would not have to care about SQL injection and can still use the mysql server fast.

Elasticsearch (or other Lucene based search engines like SolR) can easily be installed on any server because they are written in Java.

Good documentation:

http://www.elasticsearch.org/guide/en/elasticsearch/client/php-api/current/

Answer 6

I would do an explode first:

$queryArray = explode(" ", $query);

and then generate the SQL query something like:

for ($i=0; $i< count($queryArray); $i++) {
    $filter += " LIKE '%" + $queryArray[$i] + "%' AND" ;
}
$filter = rtrim ($filter, " AND");

$sql = "SELECT pageurl FROM ... WHERE name " + $filter

(note: haven't tested/run this code)