Searching a MySQL database
To search a database a form needs to made that has two fields, the text to be searched for and the database field to be searched.
<form name="search" id="search" method="post" action="#">
<input name="search" id="search" type="text" value="Search Terms" />
<select name="column" id="column">
<option value="news" selected="selected">News</option>
<option value="subject">Subject</option>
<option value="date">Date</option>
</select>
<input type="submit" name="submit" id="submit" value="Search" />
</form>
This example searches three fields: news, subject and date. We now need to create a function to get rid of any quotes before they are searched in the database.
function clear($message)
{
if(!get_magic_quotes_gpc())
$message = addslashes($message);
$message = strip_tags($message);
$message = htmlentities($message);
return trim($message);
}
Now we need to connect to the database and search for the text.
if($_POST['submit'])
{
$column = clear($_POST['column']);
$search = clear($_POST['search']);
mysql_connect('localhost','username','password');
mysql_select_db('db');
The posted data is checked and a connection is made to the database. Now that we have the search term we need to search the specified field in the database table.
$query1 = mysql_query("SELECT * FROM mytable WHERE $column LIKE '%$search%'");
$numberOfResults = mysql_num_rows($query1);
echo '<div>'.$numberOfResults.' item/s were found.</div>';
while ($output = mysql_fetch_array($query1))
echo '<div>'.$output['title'].'</div>';
This code searches the database using the LIKE
command. The number of rows is calculated and the results are output. Note that the search term is surrounded by percent signs (%).