In one of the my project I require to search news by its summary and description, I use following mysql query.

SELECT * FROM news WHERE summary LIKE ’%$keyword%’;

This was slow and inefficient.Every time i searched for an news, they got far too many results, and as the database grew the system became downright.

So i tried to find the solution for this problem by using MYSQL Full-text Searching.

There are full explanation given to use MYSQL FULL TEXT SEARCHING:

Full-text Search is a feature introduced to MySQL in version 3.23.23.

I started out with an update to my table:

ALTER TABLE news ADD FULLTEXT(description, summary);

This set ups our Full-text index. The (description, summary) part tells us that we can search the descrption and summary for keywords later on. We’ll find out how to use this later, once we’ve overcome a potential problem.

How get results:

<?php

MySQL_connect(“hostname”, ”username”, ”password”);

MySQL_select_db (“our_db”);

$query = ”

SELECT * FROM news

WHERE MATCH(summary, description) AGAINST (‘common wealth game’) ”;

$sql = MySQL_query($query);

/* output results */

?>

According to the MySQL manual, Full-text is a “natural language search”; it indexes words that appear to represent the row, using the columns you specified. As an example, if all your rows contain “MySQL” then “MySQL” won’t match much. It’s not terribly unique, and it would return too many results. However, if “MySQL” were present in only 5% of the rows, it would return those rows because it doesn’t appear too often to be known as a keyword that’s very common. (If you have “MySQL” in none of your rows, it’ll return nothing; duh.)

MySQL also does something pretty useful. It creates a score. This score is usually something like .9823475 or .124874, but always larger than zero. It can range up above 1, and I have seen it at 4 sometimes. (Don’t try to multiply it by 100 and portray it as a % value; people will wonder why their keyword matches an article 431%!)

MySQL will also order a row by its score, descending.

If you use MATCH() AGAINST() Change the document style for this to “Inline Code” twice in a query, as we will, there is no additional speed penalty. You might expect that because you are executing the same search twice the query would take twice as long, but in fact MySQL remembers the results from the first search as it runs the second.

So, let’s talk about the actual query: We are taking every column from news, and searching “summary” and “description” for $keyword This is also Inline Code. Pretty simple.

And if we want to display the score too:

<?php

/* connect to MySQL (same as always) */

$query = ”

SELECT *,

MATCH(summary, descrption) AGAINST (‘PHP’) AS score

FROM news

WHERE MATCH(summary, description) AGAINST(‘PHP’) “;

$sql = MySQL_query($query);

/* display the results… */

?>

When most people meet up with a search box they don’t type in only one word. Not knowing the backend, they just type in as many words as they feel like!

MySQL realizes this and deals with it. If I were you, the only thing I would do is remove the commas that might be there, using str_replace. MySQL will take all the words, split them up, and then match using a natural language search.

As a secondary note, you should never send input directly from the user to the MySQL prompt because any number of characters could terminate your MySQL query and begin another dastardly statement. (This is presuming you replace PHP with a $keyword in the above script.)

At a glance code for basic search: This bare bones application will search for a phrase or a keyword that the user inputs:

<?php

/* call this script “this.php” */

if ($c != 1) { ?>

<form action=”this.php?c=1″>

<input type=”text” name=”keyword”>

<input type=”submit” value=”Search!”>

</form>

<?php

} else if ($c==1) {

MySQL_connect (“hostname”, ”username”, ”password”);

MySQL_select_db (“database”);

$sql = ” SELECT *,  MATCH(summary, description) AGAINST(‘$keyword’) AS score FROM news

WHERE MATCH(summary, description) AGAINST(‘$keyword’)

ORDER BY score DESC “;

$res = MySQL_query($sql);

?>

<table>

<tr><td>SCORE</td><td>summary</td><td>ID#</td></tr>

<?php

while($row = MySQL_fetch_array($rest)) {

echo ”<tr><td>{$sql2['score']}</td>”;

echo ”<td>{$sql2['summary']}</td>”;

echo ”<td>{$sql2['id']}</td></tr>”;

}

echo ”</table>”;

}

?>

What does this script do? First, it checks $c to see if user input has been sent. If it has not, the form is displayed. If it has, the script moves onwards.

The same query that we’ve been using is used here: we match against what the user inputs. We then draw a table and display it in [semi-]pretty form. The ORDER BY score DESC

Code Inline makes sure that the best scores (the most accurate matches) are shown first.

Important note: Never use this simple script in any production form because I have done absolutely no error checking. The $query variable provides an easy opening for an intruder to input something nasty into your query that might destroy your data.

Advanced Boolean Searching

Boolean searching provide more options in MYSQL searching, explanation as follows:

As of Version 4.0.1, MySQL can also perform Boolean full-text searches using the IN BOOLEAN MODE modifier.

The only thing you change to use Boolean mode is the AGAINST() part of your query. You add IN BOOLEAN MODE to the very end of it, and place the arguments right before it. E.g. to search for all the articles that contain the word PHP, you could write:

SELECT * FROM news WHERE MATCH(summary, description) AGAINST(‘PHP’ IN BOOLEAN MODE) DR[10]

That will find all the news that contain the word “PHP” somewhere in them. It’s a fairly simple search. If you were to get more complex, and wanted everything that has to do with PHP, but not with MySQL, then you could execute this statement:

SELECT * FROM articles WHERE MATCH(summary, descrption) AGAINST(‘+PHP -MySQL’ IN BOOLEAN MODE);

There are more modifiers that one can use to search with, and I will quote from the MySQL manual since I see no point in typing out a synopsis of the manual:

Operator Meaning
By default (when neither plus nor minus is specified) the word is optional, but the rows that contain it will be rated higher. This mimics the behavior of MATCH() … AGAINST()DR
+ A leading plus sign indicates that this word must be present in every row returned.
- A leading minus sign indicates that this word must not be present in any row returned.
< > These two operators are used to change a word’s contribution to the relevance value that is assigned to a row. The < operator decreases the contribution and the > operator increases it. See the example below.
( ) . Parentheses are put round sub-expressions to give them higher precedence in the search.
~ A leading tilde acts as a negation operator, causing the word’s contribution to the row relevance to be negative. It’s useful for marking noise words. A row that contains such a word will be rated lower than others, but will not be excluded altogether, as it would be with the minus operator.
* An asterisk is the truncation operator. Unlike the other operators, it is appended to the word, or fragment, not prepended.
Double quotes at the beginning and end of a phrase, matches only rows that contain the complete phrase, as it was typed.

At a Glance Code for Boolean Searching :

<?php

/* call this script “advs.php” */

if(!$c) {

?>

<form action=”advs.php?c=1″ method=POST>

<b>Find Results with: </b><br>

Any of these words: <input type=”text” length=40 name=”any”>

All of these words: <input type=”text” length=40 name=”all”>

None of these words: <input type=”text” length=40 name=”none”>

<input type=”submit” value=”Search”>

</form>

<?

} else if($c) {

MySQL_connect (“hostname”, ”username”, ”password”);

MySQL_select_db (“database”);

if((!$all) || ($all == ”")) { $all = ”"; } else { $all = ”+(“.$all.”)”; }

if((!$any) || ($any == ”")) { $any = ”"; }

if((!$none) || ($none == ”")) { $none = ”"; } else { $none = ”-(“.$none.”)”; }

$query = ” SELECT *, MATCH(summary, description) AGAINST (‘$all $none $any’ IN BOOLEAN MODE) AS score

FROM news

WHERE MATCH(summary, description) AGAINST (‘$all $none $any’ IN BOOLEAN MODE)”;

$artm1 = MySQL_query($query);

if(!$artm1) {

echo MySQL_error().”<br>$query<br>”;

}

echo ”<b>Article Matches</b><br>”;

if(MySQL_num_rows($artm1) > 0) {

echo ”<table>”;

echo ”<tr><td>Score </td><td>summary </td><td>descrption</td></tr>”;

while($artm2 = MySQL_fetch_array($artm1)) {

$val = round($artm2['score'], 3);

$val = $val*100;

echo ”<tr><td>$val</td>”;

echo ”<td>{$artm2['summary']}</td>”;

echo ”<td>{$artm2['description']}</td></tr>”;

}

echo ”</table>”;

}

else {

echo ”No Results were found in this category.<br>”;

}

echo ”<br>”;

}

After we get the input from the form, $c Code Inlineis set to 1 and we start the real work.

First we check our input. If it’s empty, we leave it empty, if it’s not, we append the proper + or – to it. The parentheses are to allow for the user typing more than 1 word in a given field.

$query = ” SELECT * MATCH(summary, description) AGAINST (‘$all $none $any’ IN BOOLEAN MODE) AS score FROM news WHERE

MATCH(summary, description) AGAINST (‘$all $none $any’ IN BOOLEAN MODE)”;

That’s the final query that we use. $all, $none Code Inline, and $any have already been prepared for the query, and they are inserted. Score is returned as a column to order them by (if we wanted to do that), and from there on, we just have to output the results.

if(MySQL_num_rows($artm1) > 0) {

echo ”<table>”;

echo ”<tr><td>Score </td><td>Summary </td><td>Description</td></tr>”;

while($artm2 = MySQL_fetch_array($artm1)) {

$val = round($artm2['score'], 3);

$val = $val*100;

echo ”<tr><td>$val</td>”;

echo ”<td>{$artm2['summary']}</td>”;

echo ”<td>{$artm2['description']}</td></tr>”;

}

echo ”</table>”;

That’s the output code. If there’s less than 1 row to output, we send a “no records found” message out.

That’s about it for Full-Text searching.