How do I broaden the scope of a MySQL LIKE query?

Question

I am building a simple live search based on this tutorial: http://blog.ninetofive.me/blog/build-a-live-search-with-ajax-php-and-mysql/

It works fine for queries like this with one word: Search Example 1

Or for queries with part of a word: search example 2

Or for queries with two words or parts of words where they occur together in the results being searched: enter image description here

But if I search for two words that do not occur next to each other the query fails: enter image description here

How would I modify my query so that I can search for any number of keywords that appear at any location in my results?

For example if my db has a record such as: The quick brown fox jumps over the lazy dog

I want a result to be returned if I search for: quick jumps dog or dog over not just quick brown or fox jumps or fox and so on.

I was thinking something along the lines of exploding my query if it has more than one word in it and then making my $search_string into an array of keywords for MySQL to query but I don't know if that is the best way to go about it.

Query:

// Get Search
$search_string = preg_replace("/[^A-Za-z0-9]+[.]/", " ", $_POST['query']);
$search_string = $tutorial_db->real_escape_string($search_string);

// Check Length More Than One Character
if (strlen($search_string) >= 1 && $search_string !== ' ') {

// Build Query
$query = 'SELECT * FROM search WHERE subsection LIKE "%'.$search_string.'%" OR def LIKE "%'.$search_string.'%" OR exception LIKE "%'.$search_string.'%" ORDER BY subsection ASC';

Full code:

<?php
/************************************************
    The Search PHP File
************************************************/


/************************************************
    MySQL Connect
************************************************/

// Credentials
$dbhost = "localhost";
$dbname = "livesearch";
$dbuser = "live";
$dbpass = "search";

//  Connection
global $tutorial_db;

$tutorial_db = new mysqli();
$tutorial_db->connect($dbhost, $dbuser, $dbpass, $dbname);
$tutorial_db->set_charset("utf8");

//  Check Connection
if ($tutorial_db->connect_errno) {
  printf("Connect failed: %s\n", $tutorial_db->connect_error);
  exit();
}

/************************************************
    Search Functionality
************************************************/

// Define Output HTML Formating
$html = '';
$html .= '<li class="result">';
$html .= '<a target="_blank" href="urlString">';
$html .= '<h2><b> codeString - yearString - chapterString - sectionString - SUBHERE</b></h1>';
$html .= '<h3>defString</h3>';

//try to add exception string
$html .= '</br><h3>exceptionString</h3>';

$html .= '</a>';
$html .= '</li>';

// Get Search
$search_string = preg_replace("/[^A-Za-z0-9]+[.]/", " ", $_POST['query']);
$search_string = $tutorial_db->real_escape_string($search_string);

// Check Length More Than One Character
if (strlen($search_string) >= 1 && $search_string !== ' ') {
  // Build Query
  //$query = 'SELECT * FROM search WHERE function LIKE "%'.$search_string.'%" OR name LIKE "%'.$search_string.'%"';

  $query = 'SELECT * FROM search WHERE subsection LIKE "%'.$search_string.'%" OR def LIKE "%'.$search_string.'%" OR exception LIKE "%'.$search_string.'%" ORDER BY subsection ASC';

  // Do Search
  $result = $tutorial_db->query($query);
  while($results = $result->fetch_array()) {
    $result_array[] = $results;
  }

  // Check If We Have Results
  if (isset($result_array)) {
    foreach ($result_array as $result) {
      // Format Output Strings And Hightlight Matches
      //$display_function = preg_replace("/".$search_string."/i", "<b class='highlight'>".$search_string."</b>", $result['function']);
      //$display_name = preg_replace("/".$search_string."/i", "<b class='highlight'>".$search_string."</b>", $result['name']);
      //$display_url = 'http://php.net/manual-lookup.php?pattern='.urlencode($result['function']).'&lang=en';

      // Format Output Strings And Hightlight Matches
      //Format code - ex IBC
      $display_code = preg_replace("/".$search_string."/i", "<b class='highlight'>".$search_string."</b>", $result['code']);

      //Format year - ex 2012
      $display_year = preg_replace("/".$search_string."/i", "<b class='highlight'>".$search_string."</b>", $result['year']);

      //Format Chapter - ex Means Of Egress
      $display_chapter = preg_replace("/".$search_string."/i", "<b class='highlight'>".$search_string."</b>", $result['chapter']);

      //Format Section - ex Stairs
      $display_section = preg_replace("/".$search_string."/i", "<b class='highlight'>".$search_string."</b>", $result['section']);

      //Format sub Section - ex 1009.4 width
      $display_sub = preg_replace("/".$search_string."/i", "<b class='highlight'>".$search_string."</b>", $result['subsection']);

      //$display_subsection = preg_replace("/".$search_string."/i", "<b class='highlight'>".$search_string."</b>", $result['subsection']);

      $display_def = preg_replace("/".$search_string."/i", "<b class='highlight'>".$search_string."</b>", $result['def']);

      $display_exception = preg_replace("/".$search_string."/i", "<b class='highlight'>".$search_string."</b>", $result['exception']);

      $display_url = 'http://php.net/manual-lookup.php?pattern='.urlencode($result['code']).'&lang=en';

      // Insert Name
      $output = str_replace('nameString', $display_name, $html);

      //Insert Code
      $output = str_replace('codeString', $display_code, $output);

      //Insert Year
      $output = str_replace('yearString', $display_year, $output);

      //Insert Chapter
      $output = str_replace('chapterString', $display_chapter, $output);

      // Insert Section
      $output = str_replace('sectionString', $display_section, $output);

      // Insert Sub Section
      $output = str_replace('SUBHERE', $display_sub, $output);

      // Insert Defenition
      $output = str_replace('defString', $display_def, $output);

      // Insert exceptions
      $output = str_replace('exceptionString', $display_exception, $output);

      // Insert URL
      $output = str_replace('urlString', $display_url, $output);

      // Output
      echo($output);
    }
  }else{
    // Format No Results Output
    $output = str_replace('urlString', 'javascript:void(0);', $html);
    $output = str_replace('nameString', '<b>No Results Found.</b>', $output);
    $output = str_replace('functionString', 'Sorry :(', $output);

    // Output
    echo($output);
  }
}

?>

You might want to look into [**Fulltext Searching**](http://www.tuxradar.com/practicalphp/9/3/18) instead. — Funk Forty Niner, Nov 01 '13 at 17:43
It seems like Fulltext searching doesn't return partial matches as `LIKE` does. If I query for `nic` I wont get results with `nice` as I would like to. — ian, Nov 01 '13 at 19:03
Consider [this answer](http://stackoverflow.com/a/19717773/1415724) and [this answer](http://stackoverflow.com/a/2717263/1415724) or by using `LIKE '%$search_string'` or `LIKE '$search_string%'` — Funk Forty Niner, Nov 01 '13 at 19:31

score 0 · Answer 1 · answered Nov 01 '13 at 17:51

0

Use parenthesis for more than 2 parameters for the WHERE statement.

$query = 'SELECT * FROM search WHERE (subsection LIKE "%'.$search_string.'%" OR def LIKE "%'.$search_string.'%" OR exception LIKE "%'.$search_string.'%") ORDER BY subsection ASC';

answered Nov 01 '13 at 17:51

iam-decoder

2,554
1
13
28

I gave this a try but it didn't function any differently. What is the concept here? – ian Nov 01 '13 at 18:35
the concept is that it actually uses more than 2 parameters, without the parenthesis it would only follow the first 2 and no more. this just allows it use all the paremeters you want. @Artjom Kurapov gave more options than I did. It might be more beneficial to have more than 1 search button that searches for a specific column in your DB. – iam-decoder Nov 01 '13 at 18:39

score 0 · Answer 2 · answered Nov 01 '13 at 18:18

0

For each word have LIKE %word% OR LIKE %word2% etc constructs as @iamde_coder suggested
Use boolean search as @fred-ii suggested
Use specialized search-servers that split words based on language, get rid of stop-words etc. For example SOLR or sphinx

answered Nov 01 '13 at 18:18

Artjom Kurapov

6,115
4
32
42

How is number 1. supposed to function? I tried `iamde_coder`s example but it gave the same results as my query. – ian Nov 01 '13 at 18:36

score 0 · Accepted Answer · answered Nov 01 '13 at 19:17

As others are pointing out, you need to use a LIKE condition for each word in the search.

So, as a first approach to the problem, split your input like this:

$search_string = 'jump lazy dog';
$search_terms = explode(' ', $search_string);
$query = 'SELECT *
    FROM search
    WHERE ';
foreach($search_terms as $term) {
    $query .= '(subsection LIKE "%'.$term.'%" OR def LIKE "%'.$term.'%" OR exception LIKE "%'.$term.'%") AND ';
}
$query = substr($query, 0, -4).'ORDER BY subsection ASC';

echo $query;

This will produce the query you need:

SELECT *
FROM search
WHERE (subsection LIKE "%jump%" OR def LIKE "%jump%" OR exception LIKE "%jump%")
    AND (subsection LIKE "%lazy%" OR def LIKE "%lazy%" OR exception LIKE "%lazy%")
    AND (subsection LIKE "%dog%" OR def LIKE "%dog%" OR exception LIKE "%dog%")
ORDER BY subsection ASC

(I added line breaks and indentation just to make this easier to read.)

There is a huge problem here, however: this is horrifically insecure, due to the risk of SQL injection. You need to use prepared statements to prevent this. See this help page for a variety of approaches to this.

Thanks that works great. My only question is at what point this will become slow or will it? — ian, Nov 01 '13 at 22:41
It will get slow, at some point, but that depends on the amount of data you have, the complexity of the search terms, etc. For example, with even a simple database, `a b c d e f` is a somewhat complicated string for the type of search you are doing. The only way to know is to test it out; if it starts to become a problem, you may need to start reorganizing your data, doing some complex caching, or otherwise getting creative. Unless you have a *lot* of rows, it won't be problem for 99.9% of queries. — elixenide, Nov 02 '13 at 02:53

How do I broaden the scope of a MySQL LIKE query?

3 Answers3