Simple Multiple Word Search Engine Concept With PHP & MYSQL

| 14 Comments

Searching the database for a single word is easy. How about if you want to have a multiple word search? With ordinary search it will only show the result if there is an exact match for the word sequence. What if you want the search results to include all the records having the search terms in any sequence? This is one simple solution.

$searchQuery = ''; // search query is empty by default
$searchFieldName = 'searchField'; // name of the field to be searched
if(isset($_REQUEST['searchQuery']) { // check if a query was submitted
     $searchQuery = trim($_REQUEST['searchQuery']); // getting rid of unnecessary white space

     $searchTerms = explode(" ", $searchQuery); // Split the words
     $searchCondition = "$searchFieldName LIKE '%" . implode("%' AND $searchFieldName LIKE '%", $searchTerms) . "%'"; // Forming the condition for the sql

     $sql = "SELECT * FROM theTable WHERE $searchCondition;";
     // the rest is just database connection and retrieving the results
     $dblink = mysql_connect("localhost", "mysql_user", "mysql_password");
     mysql_select_db("theDatabase", $dblink);
     $result = mysql_query($sql, $dblink);
     while ($row = mysql_fetch_assoc($result)) {
          //Show your results here eg: $row['field1']
     }

}

For the search query ‘hello world today‘, the SQL generated would look like
SELECT * FROM theTable WHERE searchField LIKE ‘%hello%’ AND searchField LIKE ‘%world%’ AND searchField LIKE ‘%today%’;

This post only introduce the simple idea that the same field can be in the SQL condition multiple times within a single SQL query. This code can be easily changed to query from multiple fields. Now I am specifically referring to MYSQL Database Version 5.0.51 that I am using. I am not sure if other database or prior versions of MYSQL support this feature.

No related content found.

14 Responses to Simple Multiple Word Search Engine Concept With PHP & MYSQL

  1. limit and offset not possible after this querry

    selin
  2. Hi Selin, I have tried this query
    SELECT * FROM mdl_user WHERE `description` LIKE ‘%hi%’ AND `description` LIKE ‘%my%’ LIMIT 3, 5
    and it works fine.
    Can you be more specific about your problem?

  3. Thank you very much indeed, very handy – most appreciated!

    Lawrence
  4. It helps a lot! :D Thanks! :D

    webDesigner
  5. Thank you so much! I just used your code to modify a search and it worked great. Very helpful!!

  6. this code do the search withe one word but no with two or more, can you help me?

    chavez
  7. Can you run some SQL like this directly to see if its the DB engine issue or not.
    SELECT * FROM theTable WHERE searchField LIKE ‘%hello%’ AND searchField LIKE ‘%world%’ AND searchField LIKE ‘%today%’;
    An older version of MYSQL may not run it properly.

  8. How to add a second search field in dB?

    $secondsearchFieldName = ‘secondsearchField’;

    Christian
    • Create a second search condition
      $secondSearchCondition = “$secondsearchFieldName LIKE ‘%” . implode(“%’ AND $secondsearchFieldName LIKE ‘%”, $searchTerms) . “%’”;

      Combine the two search conditions together
      $sql = “SELECT * FROM theTable WHERE $searchCondition [concatterm] $secondSearchConditio;”;

      [concatterm] must be ‘AND’ or ‘OR’ depending on if you want to get a term present in both fields or in either of the fields.
      I have explained it very briefly.

  9. Great article and thanks for the code!

    I made some updates that some may find useful.

    to correct the typo on line 3 – if(isset($_REQUEST['searchQuery'])) {

    Added an escape check to prevent SQL injection using mysqli_real_escape_string

    Updated to use two different search fields that will return a multiple word search using OR instead of an exact match using AND.

    Then I just pop the $searchCondition to the end of my existing WHERE to the SQL in my pagination and off we go!


    // Begin Search

    $searchQuery = ''; // search query is empty by default
    $searchCondition = "(articletitle LIKE '%%' OR articlebody LIKE '%%')";
    $searchFieldName = 'articletitle'; // name of the field to be searched
    $searchFieldName2 = 'articlebody';
    if(isset($_REQUEST['text']))
    { // check if a query was submitted
    $searchQ = trim($_REQUEST['text']); // getting rid of unnecessary white space
    $searchQuery = mysqli_real_escape_string($link, $searchQ); // Prevent SQL Injection
    $searchTerms = explode(" ", $searchQuery); // Split the words
    $searchCondition = "($searchFieldName LIKE '%" . implode("%' OR $searchFieldName LIKE '%", $searchTerms) . "%')"; // Forming the condition for the sql
    $searchCondition .= " OR ($searchFieldName2 LIKE '%" . implode("%' OR $searchFieldName2 LIKE '%", $searchTerms) . "%')";
    }
    // End Search

  10. i have a code
    = 1){
    echo ‘

    rinex version
    program
    date
    maker name

    maker number
    observer
    agency
    position_X_Y_Z
    delta
    wavelength
    rinex file
    ‘;
    while($row = mysqli_fetch_array($results)){

    echo ‘
    ‘.$row['rinex_version'].’
    ‘.$row['pgm'].’
    ‘.$row['date'].’
    ‘.$row['marker_name'].’
    ‘.$row['marker_no'].’
    ‘.$row['observer'].’
    ‘.$row['agency'].’
    ‘.$row['position_X_Y_Z'].’
    ‘.$row['delta'].’
    ‘.$row['wavelenth'].’
    ‘.$row['rinex_file'].’
    ‘;

    }
    echo ”;

    }else{

    echo “There was no matching record for the name ” . $searchTerm;

    }
    the above search key is Rinex_file ,for ex: abcd1999.0b i need to b able to search for abcd( first four chars) , next four digits and a digit and a char which is followed by a “.”. Can u please help me make the chances

    madhu
    • Hi Madhu, if I understand your problem correctly, then the solution is simple. In the where clause use a normal string comparison/search:

      “WHERE rinex_file LIKE ‘$searchKey’”
      and the $searchKey must be
      $searchKey = $fourchars.$fourdigits.’.'; // constructing the string to search for

      Hope that’s what you asked for.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

 
Designed and developed by Alfie Punnoose @ Blokeish.com