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 (%).

Downloads

Categories

Tags

Social