Howto: MySQL Full Text Search in PHP

On November 6, 2009, in How-to, Scripting, by Cubert aka (Cube Dweller)

This tutorial is intended for Text Searching using MySQL (http://www.MySQL.com/) and PHP (http://www.php.net) and will focus on the Full-text capabilities presented by MySQL.

Synopsis

We run a website that hosts web blogs, we have a database that contains blog posts. We might create a table in our database using a statement like this:

CREATE TABLE blogs (body TEXT, title VARCHAR(250), id INT NOT NULL auto_increment, PRIMARY KEY(id);
 
 
 
 
 

 

How to modify your current database to accommodate Full-text searching.

Now we want to provide a simple search form and the ability to search keywords from the body field in the table “blog” of your database. First you will need to turn on Full Text Search for the table “blog” in your MySql database. Here is how to do that in MySql.

ALTER TABLE blog ADD FULLTEXT(body, title);

How to use a simple Full-text search to quickly gather relevant responses.

Lets create our PHP form and database query to support our search. I normally create a single php file called “search.php” then I “include(“search.php”)” where ever I want my search form and returning search data go but I can see where you may want to split this up and have the form and returning data in different areas so change as you see fit.

search.php
<?php
$keyword = $_POST[‘keyword’];
//we will assume your including this search in your index.php
//if not replace index wiht search.php or any page your going to do the sql query and display
//from.
?>
<h2>Search Our Archives</h2>
<form action=”index.php” enctype=”application/x-www-form-urlencoded” method=”post”>
<input name=”keyword” type=”text” />
<input type=”submit” value=”Search” />
</form>
<br>
<?php
if ($keyword != ”) {

        mysql_connect(“localhost”, “username”, “password”);

        mysql_select_db(“database_name”);

        $sql = ”
            SELECT body,
                MATCH(body) AGAINST(‘$keyword’) AS score
                FROM blog
            WHERE MATCH(body) AGAINST(‘$keyword’)
            ORDER BY score DESC
        “;
        $myresult = mysql_query($sql);
            while($row = mysql_fetch_array($myresult)) {
                echo “<table border=”0″><tbody><tr>”;
                echo “<td>Search – SCORE</td><td>Content</td></tr>”;
                echo “<tr><td><strong>” . $row[‘score’] . “</strong></td>”;
                echo “<td>” . $row[‘body’] . “</td></tr>”;
                echo “</tbody></table>”;
            }
    }
?>

This search.php makes a quick and easy Full Test Search form and display for the data. It checks $keywords to validate data before displaying the data in a table. It includes the MySQL score returned by the Full Text Search. I hope this helps someone out there get a search up and running fast.

Enjoy..

Tagged with:
 

3 Responses to “Howto: MySQL Full Text Search in PHP”

  1. digendong says:

    enjoy what?

    1.its buggy. where’s the other “<?php".it doesnt paired yet.
    2.lets try to copy pasting your code to notepad. you know what happened? you'll see all " symbol is not the same it failed to find its couple.so…its rude to me to call this code a crap.

    i'm beginner in php and learning text searching in php.
    but your tutorial will only make the other beginner being confused. please edit it.thank you

  2. sanderson says:

    Dude (Digendong), I fixed the 1 typo that the HTML blog editor removed during the posting of this script. I am truely sorry that you feel someone’s examples and free scripting must be tore down because it doesn’t do it all for you. I do see you have a brain and are using it thus you found the missing

  3. dan says:

    I spent more time fixing errors than learning anything

Leave a Reply