Basic MySQL Search

MrEvil Feb 23, 2009 Internet
Yup, this is a tiny little search engine to search through MySQL tables. Since it's small, it's easily modified to fit what you need.

One- Connect

<?php $host = 'localhost'; //this usually stays as localhost $name = 'MrEvil'; //This sets the username $password = 'lolgremlinrectum'; //and this is the password mysql_connect("$host", "$name", "$password") or die(mysql_error()); //this connects mysql_select_db("blogs") or die(mysql_error()); //this selects the databse


Two- Variables

$fromCat = mysql_real_escape_string($_GET['fromCat']); //name of category $q = mysql_real_escape_string(urlencode($_GET['q'])); //search term $startAt = is_numeric($_GET['startAt']); if(!isset($startAt)){ 	$results = mysql_query("SELECT * FROM `$fromCat` WHERE (`main_text` LIKE '%$q%') ORDER BY postID DESC LIMIT 0 , 30"); 	$num = '1'; 	$numgt = $num *30; 	$numlt = $num -1; } else{ 	$results = mysql_query("SELECT * FROM `$fromCat` WHERE (`main_text` LIKE '%$q%') ORDER BY postID DESC LIMIT $startAt , 30"); 	$num = $startAt; 	$numgt = $startAt +30; 	$numlt = $startAt -30; } $result = mysql_fetch_array($results); // create an array of the results $num_results = mysql_num_rows($results); //count the results $num_results = num_results-1; //I'm not sure why, but it always showed one extra.

$fromCat assumes each category is a different table, if not then it's pretty easy to accomidate to.

$q is the search term, aside from the escape_string function, it'll be URL Encoded. For example, a space will because %20.

$results is what grabs the results from the table. The bold text is what should be changed to match the fields in your database. The if/else() statements check to see if there isn't a startAt value in the URL, and if there is it'll select results starting at the said number. All the $num numbo jumbo is pretty much math equasions to figure out where it needs to start and finish.

$result creates an array from the results.

$num_rows counts the results. The line immediately following it simply subtracts 1 from the number, since it seems to add one more than it displays. It's small, but it annoyed me.

Three- Displaying Results

echo '<strong>Your search returned <em>'.$num_rows.'</em> results.'; 	while ($row = mysql_fetch_array_result($results)); 	 {?>  <p> 	<a href="?postID=<?=$row['postID'];?>"> <?=$row['post_title'];?> </a> <br /> 	<span> <?=$row['short_description'];?> </span>  </p> <? } ?>
First, the echo line is the part that displays the number of results.

After that, we open the while() loop of the results.

The link you see will link to the post. Change the postID URL part according to the $_GET parameter you have for your blog posts, and the $row['postID'] according to the field in your datbase table.

What's within the <span> tags is, in this case, the short description of the post. Once again, change short_description to the coresponding MySQL field.

<? } ?> closes the while() loop.

Four- Paging Results

<a href="<?=$PHP_SELF;?>startAt=<?=$numlt;?>" style="float:left">[ Previous Page ]</a> <a href="<?=$PHP_SELF;?>?startAt=<?=$numgt;?>" style="float:right">[ Next Page ]</a>


Five- Search Form

       <form action="search.php">                 <table>                     <tr>                         <td><b>Search Posts</b></td>                          <td><input type="text" name="q" value="Search..." onclick="this.value='';" /></td>                         <td><b>Categories:</b></td>                         <td><select name="fromCat"> 				<option name="category"> Category1 </option>  				<option name="category2"> Category2 </option> 				<option name="category3"> Category3 </option> 				<option name="category4"> Category4 </option>  				<option name="category5"> Category5 </option>                         </select></td>                         <td><input type="submit" value="Search ->" /></td>                     </tr>                  </table>             </form>
What did you think of this tutorial?
0 CommentsAdd a Comment
Name
Comment