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);
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..
