Subscribe to Tutorial Feed

Flash and PHP Bible

The Flash and PHP Bible has been released! The book can be found on Amazon or wherever fine books are sold in your area.

The Flash and PHP Bible has a forum for quick support.

Scriptplayground » tutorials » php » Search Engine

Search Engine

This tutorial will teach you how to write a simple PHP/MySQL Search Engine.

Note: Eventhough this is said to be a simple example, that shouldn't mean security should be overlooked. We will quickly run down the security concerns in this example.

The first thing is the fact that the database credentials are stored in this file, this is not only a security concern but also a pain if they need to be changed. I like to include a separate file that has this info in it. The other thing to note is properly sanitizing all data/response sent from the user. We will assume everything is bad at first so we do some proper escaping and validity checking.

Additions: As everything in the world advances so does programming. One of our readers has submitted an object oriented programming version of this script, so here it is...

/* CONNECTION VARIABLES */
$db_hostname = "yourhostname"; // Usually "localhost"
$db_username = "yourusername";
$db_password = "yourpassword";
$db_name = "yourdatabasename";

$conn = new mysqli($db_hostname,$db_username,$db_password,$db_name);

$searchString = $_POST['search'];
$query="SELECT * FROM posts WHERE postText LIKE '%$searchString%'"

$result = $conn->query($query);
$num_rows=$result->num_rows;
for($i=0;$<$num_rows;$i++) {
    $row=$result->fetch_assoc();
    $memberId = $row['id'];
    $post = $row['message'];
    $postDate = $row['date'];
    print "$memberId posted this \"$post\" on $postDate";
}


Beginning of original tutorial. We will first display the code in full and then explain each portion in detail.

<?php
/* CONNECTION VARIABLES */
$db_hostname = "yourhostname"; // Usually "localhost"
$db_username = "yourusername";
$db_password = "yourpassword";
$db_name = "yourdatabasename";

$conn = mysql_connect($db_hostname, $db_username, $db_password) or die(mysql_error());
mysql_select_db($db_name, $conn) or die(mysql_error());

// A basic escaping of slashes and check to make sure the search string is not empty.
// You can take this a step further and add more checking or even kick back an error message

$searchString = addslashes($_POST['search']);

if(!empty($searchString)) {
    $result = mysql_query("SELECT * FROM posts WHERE postText LIKE '%$searchString%'");

    while($row = mysql_fetch_array($result)) {	
        $memberId = $row['id'];
        $post = $row['message'];
        $postDate = $row['date'];
        print "$memberId posted this \"$post\" on $postDate <br /><br />";
    }
}
?>


$db_hostname = "yourhostname"; // Usually "localhost"

This variable is your site's hostname. Which is usaully "localhost".



$db_username = "yourusername";

The username your host assigned to you, or you picked if you set up your server.



$db_password = "yourpassword";

The password you were provided by your host or the one you picked.



$db_name = "yourdatabasename";

The name of your database.



$conn = mysql_connect($db_hostname, $db_username, $db_password) or die(mysql_error());

"$conn" is the variable that holds all the connection information. "mysql_connect" is a predefined function in PHP that allows you to connect to MySQL. the "or die" part is saying if the connection to SQL fails kill the script and print the error that was encountered. "mysql_error()" prints the specific error.



mysql_select_db($db_name, $conn) or die(mysql_error());

The "mysql_select_db" selects the database that is filled in the "$db_name" variable. The "$conn" variable holds all the connection information that was filled at the beginning of this script. Once again if this part of the script fails it will print why using "mysql_error()".



$searchString = addslashes($_POST['search']);

This sets the value of $searchString equal to the value of $_POST['search']. We add slashes to escape bad characters in the string.



$result = mysql_query("SELECT * FROM posts WHERE postText LIKE '%$searchString%'");

This is the mysql query, it looks like all other SELECT staments until you get to the "LIKE" part. That is saying find all results with the search string in it.



while( $row = mysql_fetch_array($result) )
	{	
	$memberId = $row['id'];
	$post = $row['message'];
	$postDate = $row['date'];
	print "$memberId posted this \"$post\" on $postDate <br /><br />";
	}

This is a while loop. It says loop through this code until you reach the end of the array that is returned from the DB. As it loops through it fills the "$memberId, $post, $postDate" with the results. It then prints out these variables once they are filled.



That is the end of the php. Now onto the HTML form.



<form method="post" action="searchengine.php" />
<input type="text" name="search" size=40 />
<input type="Submit" name="Submit" value="Search" />
</form>

That is the HTML code for a form. Since this isn't an HTML tutorial I will assume you know what it does.



That is all there is to coding a PHP/MySQL Search Engine. Now you can search your site with ease. The tutorial searcher on this site works along the same lines as this.

Follow Scriptplayground on Twitter (@scriptplay)

Comments: Search Engine

 Matt  Wed Oct 26, 2005 7:26 am  
This is a great website :)

This tutorial is just what I've been looking for!
 Derek  Mon Feb 13, 2006 9:30 pm  
/* CONNECTION VARIABLES */
$db_hostname = "yourhostname"; // Usually "localhost"
$db_username = "yourusername";
$db_password = "yourpassword";
$db_name = "yourdatabasename";

$conn = new mysqli($db_hostname,$db_username,$db_password,$db_name);

$searchString = $_POST['search'];
$query="SELECT * FROM posts WHERE postText LIKE '%$searchString%'"

$result = $conn->query($query);
$num_rows=$result->num_rows;
for($i=0;$<$num_rows;$i++)
{
$row=$result->fetch_assoc();
$memberId = $row['id'];
$post = $row['message'];
$postDate = $row['date'];
print "$memberId posted this \"$post\" on $postDate

";
}
?>


That's a recoded version using Object Oriented Programming ') I like it a lot better than procedure based.
 Chris  Mon Feb 13, 2006 9:37 pm  
You need to filter $_POST input before it is outputted.
 mkeefe  Mon Feb 13, 2006 10:28 pm  
Derek great addition if you don't mind I will add that in the article?

Chris, I agree but wanted to keep this simple. I will add a note though.
 Derek  Tue Feb 14, 2006 2:34 pm  
Yeah, that's fine. I'm thinking about writing some tutorials for this website so you'll probably hear from me again.
 mkeefe  Tue Feb 14, 2006 3:23 pm  
Thank you Derek we would appreciate that. I have added your OOP version to the original article, thank you for your contribution.
 Theodor  Thu Oct 4, 2007 4:12 am  
very nice tut ;)

i want to ask if it's better to use mysql_real_escape_string instead of addslashes ?

By using there addslashes is safer ? ..am asking cause there is lot of trouble you can get in general with the sql injections nowdays :(

Is this script safe ?

thanks
 Randy  Fri May 9, 2008 9:26 pm  
This is a great tutorial! I was wondering if you could show me how to make the search case-insensitive. As it is, if the database has the word "scan" in it and I search for "Scan", the search won't find it.

Thanks!
 Randy  Fri May 9, 2008 9:39 pm  
Funny, a bit more searching and I found the answer, although not the complete answer.

The answer was to change the Collation on the column being search to one that had a "ci" at the end of the type name. And, they gave an example of "latin2_general_ci", and that the "ci" meant "Case insensitive". Cool!

So, I went off to check this out, and found that my collation was set to "latin1_swedish_ci". Hmm, it has the "ci" at the end, but it's not working. So, I changed the one column to "latin2_general_ci" and voila! It's working now.

It's funny how little things like this just make me so happy!
Add a comment
The ability to add new comments has been disabled in this archived version.

Loading
Main | Tutorials | Articles