Tutorials
Simple SQL Search
Introduction
Often I see many posts about how to do a simple SQL search. Well I finally caved in and decided to write a tutorial just for that. I will try and explain everything in as much detail as I can without being over wordy. This is a basic search, nothing more. If you want a more advanced search I would suggest using Google or hiring someone to code it for you.
The aim of this tutorial is to provide users with a basic layout and the logic behind creating a multiple field search in MySQL. As often time users tend to over do the search and add a lot of unnecessary code. By following and understanding this tutorial you should be able to implement this search into your own site and provide a nice and simple SQL Search of your own database. So roll up your sleeves and be prepared to get dirty.
Setting Up
I use some simple debug tactics in this tutorial, if you would like to use better debugging practices I would suggest reading Debugging, A Beginner's Guide [http://www.phpfreaks.com/tutorial/debugging-a-beginners-guide]. I have changed all the or die() to or trigger_error() as an update.
First I will assume that you have a valid installation of PHP, Apache (or IIS) and MySQL installed and already working on your system. I will also expect you to know how to create the table structure provided and add the data to it in MySQL. I will not go through the trouble of how to do that, you can find yourself a basic MySQL/PHP Tutorial and follow that.
And now for the SQL test data:
Table Structure:
Create table simple_search (
sid INT NOT NULL auto_increment,
stitle VARCHAR(50) NOT NULL,
sdescription VARCHAR(255) NOT NULL,
sbody text NOT NULL,
primary key (`sid`),
INDEX (`stitle`),
INDEX(`sdescription`)
);Test Data:
INSERT INTO simple_search (`stitle`, `sdescription`, `sbody`) VALUES ('Test Simple Search 1', 'This is a test result for our MySQL simple search tutorial.', 'MySQL searching is a valueable item to be used in coordination with PHP. The data provided here will be easily searchable to users. Granted it will only provide the users with the basic, hence simple, search.'),
('Searching Made Easy 101', 'Searching through MySQL sets is made easy in PHP', 'PHP provides us with a means to access Relational databases with ease. Through the functions that are provided a user is able to create a form and allow their clients to browse through postings that other\'s have posted and find relevant articles to their searches. Because this is a search made easy it will not be exact or concise instead pull up a catch all to the terms searched.'),
('Gateway to Information', 'The web provides us with many tools to access loads of information that are stored in a database on our server.', 'Storing information on your server is a great way to provide clients with different information. A client can now find information they were looking for all In one place. Utilizing a scripting language often makes access to this data easier and provides the server owner with a means to backup extreme amounts of data with ease. Not to mention access that data.'),
('The Gaming World as we Know it', 'The gaming world as we know it has undergone many changes in the past. But what changes lay in the future?', 'Some say the future of gaming on computers relies in a virtual reality, but not like the 80\'s virtual reality. This new virtual reality allows the user to use their mind to control elements of a video game, vs using a controller. This new aspect of gaming will put the user right in to the game by providing a more efficient way of controlling their character. How long before this technology is readily available, no body knows. But the gaming world as we know it is changing rapidly and only time will tell when this great feature will be added to the next gaming console.'),
('Hundreds of Ants Attacking', 'Hundreds, if not thousands of Ants are starting to rage war against the human race. Can we stop it?', 'In recent news, Ants have been found to attack random people. A mom in Brazil reported that her child was just playing nicely outside when an Ant decided to go and bite her. The mom reported this incident to the police immediately, but as was told, the police were busy with nearly two dozen other Ant related reports. "I was scared for my childs life" report Janet Marget from Pawtucket, IL. "The ant just came running out of the bushes and charged me, I nearly escaped by stamping hard on the ground and crushing the ant." While some are over come by fear, others are embracing the new "regime" as it is being called. Jack Oldswell said, "I welcome the ants to take over. I mean come on, with ants out numbering humans, millions if not billions to one, they have the numbers.". This pandemic has caused quite a stir in the government agencies who have set up a task force to go and do recon on Ant colonies. Unfortunately, in this line of work, every employee has been bitten by multiple ants and this line of work is highly dangerous. As such the government raised the employees salary to three times the amount of a normal persons salary. As for this lowly reporter, I welcome the ants and offer them this statement so that they might spare me and save my life when they rule the planet.');Database Connection
Now that we have our data entered into MySQL and made sure our PHP Server works it is time to start writing code. I would recommend a decent PHP Editor, Notepad++ for a simple project like this, or for a major project I would choose NetBeans IDE. Those are just the editors I recommend, feel free to choose whichever editor you are comfortable with. Now onto the code.
First up, we have to make a connection to the database. Since MySQLi is not on servers with a PHP version < 5 I will use MySQL to write out the database connections.
<?php
$dbHost = 'localhost'; // localhost will be used in most cases
// set these to your mysql database username and password.
$dbUser = 'searchuser';
$dbPass = 'searchpass';
$dbDatabase = 'searchdb'; // the database you put the table into.
$con = mysql_connect($dbHost, $dbUser, $dbPass) or trigger_error("Failed to connect to MySQL Server. Error: " . mysql_error());
mysql_select_db($dbDatabase) or trigger_error("Failed to connect to database {$dbDatabase}. Error: " . mysql_error());
?>Remember to change the $dbUser, $dbPass, $dbDatabase and $dbHost to the values of your database. If you do not the script will error out and tell you what is wrong.
Search Form
Now we have the database connection setup we need to setup our actual form code:
<html>
<title>My Simple Search Form</title>
<style type="text/css">
#error {
color: red;
}
</style>
<body>
<?php echo (count($error) > 0)?"The following had errors:<br /><span id=\"error\">" . implode("<br />", $error) . "</span><br /><br />":""; ?>
<form method="GET" action="<?php echo $_SERVER['PHP_SELF'];?>" name="searchForm">
Search For: <input type="text" name="search" value="<?php echo isset($searchTerms)?htmlspecialchars($searchTerms):''; ?>" /><br />
Search In:<br />
Body: <input type="checkbox" name="body" value="on" <?php echo isset($_GET['body'])?"checked":''; ?> /> |
Title: <input type="checkbox" name="title" value="on" <?php echo isset($_GET['title'])?"checked":''; ?> /> |
Description: <input type="checkbox" name="desc" value="on" <?php echo isset($_GET['desc'])?"checked":''; ?> /><br />
Match All Selected Fields? <input type="checkbox" name="matchall" value="on" <?php echo isset($_GET['matchall'])?"checked":''; ?><br /><br />
<input type="submit" name="submit" value="Search!" />
</form>
<?php echo (count($results) > 0)?"Your search term: {$searchTerms} returned:<br /><br />" . implode("", $results):""; ?>
</body>
</html>You will notice that this is a simple form, no formatting. The PHP is mixed in with it. If you do not know, the ? and : are ternary operators [http://www.php.net/ternary] which act like a short If/else statement.
Example: (condition is true/false) (then ?) do this (else : ) do that.
If you want to read more on HTML Forms, google [http://www.google.com/search?q=HTML+Forms] it. What we are doing with the form code is checking different variables. First off, we see if there was an error by counting the error array. (This part of the code comes later). If there was an error, show the error to the users so they can fix it. Next we have different variables being echo'd. The first simply points the form to the search page for submission. The next are items that were previously on the form if they contain values. Should be pretty straight forward.
Finally we check to see if the $results array [http://php.net/array] has any items. If the results array does, then we will display those results to the user along with the search terms that were used to grab these results. So now you hopefully understand the form and why the code is how it is.
Search Code
Next step is the actual PHP code for the search:
<?php
// Set up our error check and result check array
$error = array();
$results = array();
// First check if a form was submitted.
// Since this is a search we will use $_GET
if (isset($_GET['search'])) {
$searchTerms = trim($_GET['search']);
$searchTerms = strip_tags($searchTerms); // remove any html/javascript.
if (strlen($searchTerms) < 3) {
$error[] = "Search terms must be longer than 3 characters.";
}else {
$searchTermDB = mysql_real_escape_string($searchTerms); // prevent sql injection.
}
// If there are no errors, lets get the search going.
if (count($error) < 1) {
$searchSQL = "SELECT sid, sbody, stitle, sdescription FROM simple_search WHERE ";
// grab the search types.
$types = array();
$types[] = isset($_GET['body'])?"`sbody` LIKE '%{$searchTermDB}%'":'';
$types[] = isset($_GET['title'])?"`stitle` LIKE '%{$searchTermDB}%'":'';
$types[] = isset($_GET['desc'])?"`sdescription` LIKE '%{$searchTermDB}%'":'';
$types = array_filter($types, "removeEmpty"); // removes any item that was empty (not checked)
if (count($types) < 1)
$types[] = "`sbody` LIKE '%{$searchTermDB}%'"; // use the body as a default search if none are checked
$andOr = isset($_GET['matchall'])?'AND':'OR';
$searchSQL .= implode(" {$andOr} ", $types) . " ORDER BY `stitle`"; // order by title.
$searchResult = mysql_query($searchSQL) or trigger_error("There was an error.<br/>" . mysql_error() . "<br />SQL Was: {$searchSQL}");
if (mysql_num_rows($searchResult) < 1) {
$error[] = "The search term provided {$searchTerms} yielded no results.";
}else {
$results = array(); // the result array
$i = 1;
while ($row = mysql_fetch_assoc($searchResult)) {
$results[] = "{$i}: {$row['stitle']}<br />{$row['sdescription']}<br />{$row['sbody']}<br /><br />";
$i++;
}
}
}
}
function removeEmpty($var) {
return (!empty($var));
}
?>Although the code may look "complex" it really is not. First we start off by defining our arrays for errors and results. This will prevent any errors from coming of error or results not being an array, because we defined them as an array already.
Next we test to see if the form was submitted by checking to see if "search" was populated. This will prevent issues from occurring in case this is the first page load. Trimming [http://www.php.net/trim] the value of the form search will gurantee that it will not search for white spaces at the beginning or end of the terms. Stripping the tags [http://www.php.net/strip_tags] will remove any HTML code present (you may want to omit this, it depends on what your need is). Finally we do some validation checks to make sure that some terms were actually passed through and that they contained enough characters [http://www.php.net/strlen] to not return everything.
Once we are passed the validation it is time to take a precautionary measure by sanitizing our data with mysql_real_escape_string [http://www.php.net/mysql_real_escape_string] for database input to prevent SQL Injection [http://en.wikipedia.org/wiki/Sql_injection] from occurring. Next we will setup our SQL statement. We start out by defining the statement. Next we setup an array for types, this is because the search can be done on multiple fields. By setting up the search this way you just need the user to select a checkbox and those fields will be included in the search. Once we have populated the types array we want to remove any of the types that were not populated. This is being done by the function removeEmpty() which is custom defined. Sometimes taking a break to play some free flash games [http://www.aeonity.com/ab/games] or listen to free flash soundboards [http://www.aeonity.com/ab/soundboards] can help ease the tension. The removeEmpty function in use with array_filter [http://www.php.net/array_filter] will go through each item in the array and test if that item has a value using empty [http://www.php.net/empty]. If not it removes it from the array, which makes less code and work for us. The filtering of the array is to prevent a whitespace being used as a condition and returning everything.
Now if the types contained more than 1 item in the array, we can do our search. Imploding [http://www.php.net/implode] the array types will allow us to use our checkbox to see what the user wants matched. To do this we define a dynamic variable $andOr which changes depending on what the user selects. If that want it any one of the fields we use " OR " if they want it in all of the fields we use " AND " which ensures that we get the results the users want. Finally we run the query and grab our results, populate the results array and viola. We have our search results. Thanks goes to the Technology Blog [http://www.aeonity.com/frost] resource.
The End Result
Put the script together and it will look something like this:
<?php
/*****************************
* Simple SQL Search Tutorial by Frost
* of Slunked.com
******************************/
$dbHost = 'localhost'; // localhost will be used in most cases
// set these to your mysql database username and password.
$dbUser = 'searchuser';
$dbPass = 'searchpass';
$dbDatabase = 'searchdb'; // the database you put the table into.
$con = mysql_connect($dbHost, $dbUser, $dbPass) or trigger_error("Failed to connect to MySQL Server. Error: " . mysql_error());
mysql_select_db($dbDatabase) or trigger_error("Failed to connect to database {$dbDatabase}. Error: " . mysql_error());
// Set up our error check and result check array
$error = array();
$results = array();
// First check if a form was submitted.
// Since this is a search we will use $_GET
if (isset($_GET['search'])) {
$searchTerms = trim($_GET['search']);
$searchTerms = strip_tags($searchTerms); // remove any html/javascript.
if (strlen($searchTerms) < 3) {
$error[] = "Search terms must be longer than 3 characters.";
}else {
$searchTermDB = mysql_real_escape_string($searchTerms); // prevent sql injection.
}
// If there are no errors, lets get the search going.
if (count($error) < 1) {
$searchSQL = "SELECT sid, sbody, stitle, sdescription FROM simple_search WHERE ";
// grab the search types.
$types = array();
$types[] = isset($_GET['body'])?"`sbody` LIKE '%{$searchTermDB}%'":'';
$types[] = isset($_GET['title'])?"`stitle` LIKE '%{$searchTermDB}%'":'';
$types[] = isset($_GET['desc'])?"`sdescription` LIKE '%{$searchTermDB}%'":'';
$types = array_filter($types, "removeEmpty"); // removes any item that was empty (not checked)
if (count($types) < 1)
$types[] = "`sbody` LIKE '%{$searchTermDB}%'"; // use the body as a default search if none are checked
$andOr = isset($_GET['matchall'])?'AND':'OR';
$searchSQL .= implode(" {$andOr} ", $types) . " ORDER BY `stitle`"; // order by title.
$searchResult = mysql_query($searchSQL) or trigger_error("There was an error.<br/>" . mysql_error() . "<br />SQL Was: {$searchSQL}");
if (mysql_num_rows($searchResult) < 1) {
$error[] = "The search term provided {$searchTerms} yielded no results.";
}else {
$results = array(); // the result array
$i = 1;
while ($row = mysql_fetch_assoc($searchResult)) {
$results[] = "{$i}: {$row['stitle']}<br />{$row['sdescription']}<br />{$row['sbody']}<br /><br />";
$i++;
}
}
}
}
function removeEmpty($var) {
return (!empty($var));
}
?>
<html>
<title>My Simple Search Form</title>
<style type="text/css">
#error {
color: red;
}
</style>
<body>
<?php echo (count($error) > 0)?"The following had errors:<br /><span id=\"error\">" . implode("<br />", $error) . "</span><br /><br />":""; ?>
<form method="GET" action="<?php echo $_SERVER['PHP_SELF'];?>" name="searchForm">
Search For: <input type="text" name="search" value="<?php echo isset($searchTerms)?htmlspecialchars($searchTerms):''; ?>" /><br />
Search In:<br />
Body: <input type="checkbox" name="body" value="on" <?php echo isset($_GET['body'])?"checked":''; ?> /> |
Title: <input type="checkbox" name="title" value="on" <?php echo isset($_GET['title'])?"checked":''; ?> /> |
Description: <input type="checkbox" name="desc" value="on" <?php echo isset($_GET['desc'])?"checked":''; ?> /><br />
Match All Selected Fields? <input type="checkbox" name="matchall" value="on" <?php echo isset($_GET['matchall'])?"checked":''; ?><br /><br />
<input type="submit" name="submit" value="Search!" />
</form>
<?php echo (count($results) > 0)?"Your search term: {$searchTerms} returned:<br /><br />" . implode("", $results):""; ?>
</body>
</html>And now we run the script and see the search results work their magic! Enjoy.