mySQL Search Engine

This tutorial will teach you how to search a mySQL table. It goes along with several others that use the same news database such as Add a Row to mySQL, Deleting a Row, and Edit a Row In mySQL. We have a database called spoono_news, with a table called news. In the mySQL news table, we have 6 fields: id, title, message, who, date, and time. You can see the mySQL code here: mysql.txt.

This useful script can search a mySQL database and spit out the results as you want them. This one in particular goes through the news table and searches for user defined text in the 'message' column. The script has to be broken down into two separate files. One is an HTML form that can be placed on any HTML page. The second is a PHP page that will process the search and spit out the results. Lets work on the form first because it is the easiest.

Here is what we have to write in English for the Form:

1. Declare a form that will send the user to search.php.
2. Make an input field that will let the users enter their search.
3. A submit button that will redirect the user to search.php.

Here it is in PHP:

Search:


<form method="post" action="search.php">
<input type="text" name="search" size=25 maxlength=25>
<input type="Submit" name="Submit" value="Submit">
</form>

And that is basically all the code we have to write for the form. Now the hard part, writing the processing. Open up a new file and save it as search.php that is in the same folder as the form code above. Essentially, this tutorial is exactly the same as the one on "Displaying a Database" tutorial we have, except the mySQL command is different. Here is what we have to write in English for the processing:

1. Connect to the server and select the right database.
2. Convert the $search variable posted in the above form back from the $_POST variables.
3. Grab the result..
4. Run a while loop running the results we got.
5. Display our answers

Here it is in PHP:

<?
//connect to mysql
//change user and password to your mySQL name and password
mysql_connect("localhost","user","password"); 
	
//select which database you want to edit
mysql_select_db("spoono_news"); 

$search=$_POST["search"];

//get the mysql and store them in $result
//change whatevertable to the mysql table you're using
//change whatevercolumn to the column in the table you want to search
$result = mysql_query("SELECT * FROM news WHERE message LIKE '%$search%'");

//grab all the content
while($r=mysql_fetch_array($result))
{	
   //the format is $variable = $r["nameofmysqlcolumn"];
   //modify these to match your mysql table columns
  
   $title=$r["title"];
   $message=$r["message"];
   $who=$r["who"];
   $date=$r["date"];
   $time=$r["time"];
   $id=$r["id"];
   
   //display the row
   echo "$title <br> $message <br> $who <br> $date | $time <br>";
}
?>

Not too tough was it? If you have any questions, post on our board and we'll answer it as soon as we can.