How to Create a Search Feature with PHP and MySQL / Page 3

How to Create a Search Feature with PHP and MySQL [con't]

Removing Bullets

As you can see from viewing the example file, our matches are coming back in an unordered list, but bullets in this case aren't needed. To remove the bullets, add this CSS rule to the top of your file, inside the opening and closing <head> tags:

This rule simply says to target all list items inside of an unordered list and set their bullet type to none.

Searching by Letter

Since searching by letter is merely adjusting a couple lines of code, let's go ahead and add in functionality that allows our visitor to search our staff member directory for first or last names containing a certain letter. In order to do this, add this code right after the closing <form> tag:

We simply hard-code anchor tags with a query string of by, and set it equal to a particular letter. Depending on how large your database of staff members is you may want to add additional ones. To achieve the search functionality by letter we need to add the following code right after the closing curly brace of our original script as shown below:

}//end of search form script
if(isset($_GET['by'])){
$letter=$_GET['by'];
//connect  to the database
$db=mysql_connect  ("servername", "username",  "password") or die ('I cannot connect to the database  because: ' . mysql_error());
//-select  the database to use
$mydb=mysql_select_db("yourDatabase");
//-query  the database table
$sql="SELECT  ID, FirstName, LastName FROM Contacts WHERE FirstName LIKE '%" . $letter . "%' OR LastName LIKE '%" . $letter ."%'";
//-run  the query against the mysql query function
$result=mysql_query($sql);
//-count  results
$numrows=mysql_num_rows($result);
echo  "<p>" .$numrows . " results found for " . $letter . "</p>";
//-create  while loop and loop through result set
while($row=mysql_fetch_array($result)){
$FirstName  =$row['FirstName'];
            $LastName=$row['LastName'];
            $ID=$row['ID'];
//-display  the result of the array
echo  "<ul>\n";
echo  "<li>" . "<a  href=\"search.php?id=$ID\">"   .$FirstName . " " . $LastName .  "</a></li>\n";
echo  "</ul>";
}
}

The four snippets of code we changed are:

We use the isset Boolean check and pass in the $_GET array and check for a value of by,
We create a variable called letter and assign it to the $_GET array value,
Append the letter variable in our SQL statement and
Concatenate the letter variable inside the statement where the counted number of rows are returned.

Save your file and review the results (search_byletter.php).

Searching by Specific Employee

In order to display the rest of the staff member's information that's passed by the unique id inside our link, we just need to add the following code right after the closing curly brace of our letter script as shown below:

The four snippets of code we changed are:

We use the isset Boolean check and pass in the $_GET array and check for a value of id,
We create a variable called contactid and assign it the $_GET array value,
Select everything from our table, which is indicated by an asterisk (*). An asterisk is shorthand notation in SQL that's translated to "give me all columns and rows from the table". In order to know which information to display we append the contactid variable at the end of our SQL statement, and
Display the additional information about each staff member.

Save your file and review the results (search_byid.php).

As you review the changes, you should notice our functionality is working as intended. When you enter a first or last name in our form field or select a letter, only the staff member's name appears as a hyperlink. As you move our mouse over the link, in the status bar, you should notice that a unique ID has been passed inside our link and appended on our query string. When you click a specific staff member link, the address bar changes and the rest of the employee information is shown according for the unique ID passed.

SQL Injection

The reason we added a regular expression to our search form field was to ensure that no one could manipulate our SQL query to perform abnormal operations on our data or table such as delete operations, at least, not very easily. Such abnormal operations are called SQL injection attacks. These attacks are basically techniques that hackers use to try and manipulate your applications to perform operations they weren't meant to do and as a result, they cause adverse affects on applications and more importantly, database tables. For example, if we were to allow apostrophes in our search form field, a malicious attacker visiting our form could try typing the following:

By executing this query, it could cause My SQL or any database engine to execute a command that would delete your entire staff member table. As mentioned before our regular expression ensures that the first character coming from the $_POST value of our form field has to be either a capital or lowercase letter. As a result, we're able to protect against malicious queries that might be attempted from our search field.

Conclusion

In this article you learned how to do the following:

Create a database and corresponding table
Use a database management system DBMS) to create columns and enter data for the corresponding entities
Create a search form, along with PHP code to:
- Check for form submissions
- Check for appropriate query string variables
- Check for acceptable input from visitors
- Connect, query and display results from a database table
How to protect your application and database table from SQL injection attacks

With the knowledge gained from this article, developers can easily adapt or modify the code presented to perform other operations or enhance functionality for any particular instance of a search feature.

About the Author

Ryan Butler is the founder of Midwest Web Design. His skill sets include HTML, CSS, Flash, JavaScript, ASP.NET, PHP and database technologies.

Original: August 6, 2008

Digg This Add to del.icio.us

[prev]