PHP Search a Mysql Database

Nearly every website has some kind of search feature that allows you to quickly find the specific things you are looking for. This can be done a number of ways depending on the language and desired features, but I am going to show you how to search a Mysql database using PHP and a simple HTML form. The form is going to submit two variables – “q” and “submit.” The “submit” variable will tell PHP to initiate the search, and “q” will be the defined search terms that it searches the database for.

First, we need the HTML form. We do this with the “form” tags and “input” tags as the keywords and search button.

[code lang=”html”]

[/code]

This form is sending the two variables to the page “search.php.” This is where we will search our mysql database for rows that match the terms. First we will clean the search terms using trim() and addslashes() which will remove any whitespace and add backslashes “\” before any quotation marks, etc. so it isn’t a threat to our mysql database.

Visit my post on Mysql Database Connect to see how to connect to the database.

[codesyntax lang=”php” title=”search.php Source Code”]
<?php

// search.php
if(isset($_POST[“submit”])){
// Form was submitted, collect the search terms.
$search = trim(addslashes($_POST[“q”]));
if($search){
// We got the search terms, now lets search the database.
// Connect to database here.
$sql = mysql_query(“SELECT * FROM table1 WHERE column1 LIKE ‘%$search%'”);
$count = mysql_num_rows($sql);
// This will search column1 in table1 for a match of the search terms. The parentheses before and after the terms allow the search terms to be a part of other text.
if($count > 0){
// Matches were found!
echo “<p>Search: \”<em>”.$search.”</em>\” – “.$count.” Results Found.</p>”;
echo “<table width=\”100%\” align=\”center\” border=\”1\” cellpadding=\”3\” cellspacing=\”0\”>”;
echo “<tr><th align=\”left\”><strong>Search Results</strong></th></tr>”;
while($row = mysql_fetch_array($sql)){
echo “<tr><td align=\”left\”>”.$row[“column1″].”</td></tr>”;
}
echo “</table>”;
}
} else {
echo “Please enter search terms.”;
}
} else {
// Form wasn’t submitted, redirect to index.php.
header(“Location: index.php”);
}
[/codesyntax]

You can personalize the mysql query however you like of course. Edit the values “table1” and “column1” to match the table you are searching in your database and the columns you want to match the search terms to. You can even match them against multiple columns like so:

[code lang=”php”]
$sql = mysql_query(“SELECT * FROM table1 WHERE column1 LIKE ‘%$search%’ OR column2 LIKE ‘%$search%'”);
[/code]

This will try to match the search terms to column1 or column2. This will make your search range larger, thus bringing more results to your viewers. Of course when displaying your results in the table above it would be smart to include things like: date/time it was submitted, who it was submitted by, category, etc. You can do this by including other columns when you echo the data into the table, like so:

[code lang=”php”]
while($row = mysql_fetch_array($sql)){
echo “

“.$row[“column1″].” “.$row[“column2″].” “.$row[“column3″].”

“;
}
[/code]

So let’s say you wanted to display posts in a table from users, when it was posted, the last reply, etc. You would have it displayed as so:

Title	Last Reply	Posted By	Time Posted
HTML Linking Stylesheets	May 1st, 2010	bgallz	April 16th, 2010
Another Post	May 1st, 2010	bgallz	April 16th, 2010
Another Post	May 1st, 2010	bgallz	April 16th, 2010