Apr
28

Keyword search in a MySQL database

28 Apr 2009 by tonie in MySQL, PHP

For the following example, let's suppose that you have a database table, named posts with 5 fields in there: id, title, author, body, tags. What we want to do is create a script, that will allow users to search your database for any keyword matches. First, we need to set up a simple search form, so let's do that

<html>
   <head>
      <title>Search page</title>
   </head>
   <body>
      <h2>Keyword search in a MySQL database</h2>
      <form action="" method="post">
         Keyword: <input type="text" name="q" /> <input type="submit" value="Search" />
      </form>
   </body>
</html>

To perform our keyword search in the database, we need ensure several things first, and then we will employ the following logic:

1. Our search terms are not hazardous. The keywords must be escaped properly to prevent users from performing MySQL injections.

2. Specify the table columns, where the search will take place.

3. Check for an existing search parameter and whether it is longer than or equal to 3 characters.

4. Remove any repetitive keywords.

5. Build a search query and execute it.

6. Print any results, while highlighting the keywords.

Now, I didn't go into deeper details, but just emphasized on the major points. Let's see what the script looks like. The code is well commented and the explanations, I hope, are clear to everyone

<?php
// connect to database
include ('database_config_file.php');
// escape the keywords properly
function escape($s) {
    if (get_magic_quotes_gpc()) {
        $s=stripslashes($s);
    }
    if (!is_numeric($s)) {
        $s=mysql_real_escape_string($s);
    }
    return $s;
}
// highlight the searched keywords in our results
function highlight($keyword, $text) {
    $text=preg_replace("|($keyword)|Ui", "<span style=\"background:#FFCCFF;font-weight:bold;\">$1</span>", $text );
    return $text;
    
}

if (isset($_POST['q'])) {
    if (strlen($_POST['q'])>=3) {
        // where to search?
        $searchin=array('title','body','tags');
        
        // prepare the keyword array
        $q=trim($_POST['q']);
        $q=escape($q);
        $q=explode(' ',$q);
        
        // remove equivalent keywords
        $q=array_unique($q);
        
        // let's create the search query
        $query = "SELECT * FROM `posts` WHERE ";
        foreach ($searchin as $column) {
            foreach ($q as $keyword) {
                // make sure keyword contains at least 2 letters
                if (strlen($keyword)>=2) {
                    $query .= "`".$column."` LIKE '%".$keyword."%' OR ";
                }
            }
        }
        
        // remove the last occurance of 'OR'
        $query=rtrim($query,' OR ');
        
        // Print results (if any) and highlight the searched keywords
        $result=mysql_query($query);
        echo 'Search results for: ';
        foreach ($q as $keyword) {
            echo '<strong>'.$keyword.'</strong> ';
        }
        if (mysql_num_rows($result)) {
            while ($object=mysql_fetch_object($result)) {
                $title=$object->title;
                $body=$object->body;
                foreach ($q as $keyword) {
                    if (strlen($keyword)>=2) {
                        // highlight the keywords
                        $title=highlight($keyword,$title);
                        $body=highlight($keyword,$body);
                    }
                }
                // Print results
                echo '<h4>'.$title.'</h4>';
                echo '<p>'.$body.'</p>';
                echo '<hr />';
            }
        } else {
            echo '<p>Your search did not return any results.</p>';
        }
    } else {
        echo '<p>Please enter a search term, longer than 3 symbols.</p>';
    }
}
?>

OK, that was it. Now, this was a very basic searching method, but efficient as well. As you can see it doesn't return paged results and it shows the contents of all your post body in the result. Imagine if you have 500+ articles and someone types a keyword, which is present in fifty percent of them. The picture gets ugly and it will be fairly difficult for the users to sift out the more important results.

Right, so how do I fix it?

First, you might want to modify the script so that the search returns paged results, like 10 or so per page. Next, you can sort your results by importance. To that, you can approach by several different methods or you can combine all of them. For example, you could divide the search results into 3 categories - results in the Title, Post Body or in the Tags of your posts. Then write a simple function that calculates the occurrences of each keyword. You can also search for whole text occurrence and print those first and then the keyword search. There are many, many things to consider, if you are about to write a good search engine.

search, mysql, php, keyword, tutorial

Speak your mind ( 0 Comments)

If you liked the article and want to contribute to it, please feel free to leave your comment. HTML tags are not allowed, but you can use the following BBCode to enhance your message: [url] [quote] [code] [b] [i] [u] [color].