Introduction |
|
One of the most important advantages of creating a database driven sites is the ability to perform search queries on the database. Could you imagine searching WeberDev.com if it was written using plain HTML? Not that it couldn't be done, just we would have to rebuild an index of all the pages every time someone adds an example, article and so on. |
|
Now, performing searches on a database driven site is a totally different story (and thankfully much easier). |
|
Grocery list |
|
In order to understand and work a bit with searches we will need a small MySQL driven site. I will use the structure we built at my "Beginners guide to PHP/MySQL - Creating a simple guest book" article. Actually, we will write a search page for the guest book described in the above tutorial, so go on and take a brief look at the PHP code for the guest book, I'm waiting. |
|
Notice that we have a file named links.x, which holds the links to the guest book pages. We will modify it slightly to include a link to the search page (the third <li> statement). |
|
Links.x: |
|
<p></p>
<ul>
<li><a href="index.php3">Display entries</a>
<li><a href="add.php3">Add new entry</a>
<li><a href="search.php3">Search the guest book</a>
</ul> |
|
|
Ok, done with that. |
|
Searching the database |
|
To tell the truth, we don�t actually search the database, but rather select records from it that correspond to a string we choose. Lets assume we want to search all the records where the users' name matches the search string: |
|
Search.php3: |
|
<html>
<head><title>Searching the Guest Book</title>
</head>
<body bgcolor=#ffffff>
<h1>Searching the Database</h1>
<form method="post" action="srch.php3">
<table width=90% align=center>
<tr><td>search for:</td><td><input type=text name='search' size=60 maxlength=255></td></tr>
<td></td><td><input type=submit></td></tr>
</table>
</form>
<?php include ('links.x');?>
</body>
</html> |
|
|
This html is rather simple. Just a small form that sends a search string variable to srch.php3. |
|
Srch.php3: |
|
<?
if ($search) // perform search only if a string was entered.
{
mysql_connect() or die ("Problem connecting to Database");
$query = "select * from visitors WHERE Name='$search'";
$result = mysql_db_query("guest_book", $query);
if ($result)
{
echo "Here are the results:<br><br>";
echo "<table width=90% align=center border=1><tr>
<td align=center bgcolor=#00FFFF>Visit time and date</td>
<td align=center bgcolor=#00FFFF>User Name</td>
<td align=center bgcolor=#00FFFF>Last Name</td>
<td align=center bgcolor=#00FFFF>Email</td>
</tr>";
while ($r = mysql_fetch_array($result)) { // Begin while
$ts = $r["TimeStamp"];
$name = $r["Name"];
$last = $r["Last"];
$email = $r["email"];
$comment = $r["comment"];
echo "<tr>
<td>$ts</td>
<td>$name</td>
<td>$last</td>
<td>$email</td></tr>
<tr> <td colspan=4 bgcolor=\"#ffffa0\">$comment</td>
</tr>";
} // end while
echo "</table>";
} else { echo "problems...."; }
} else {
echo "Search string is empty. <br> Go back and type a string to search";
}
include ('links.x');
?> |
|
|
Some explanations. This scripts performs the following tasks: |
|
- Checks whether a string was entered.
- Retrieves all the records that match the search string.
- Prints all the retrieved records in a formatted table.
|
Clearing all the mumbo jumbo, the actual code that we need to work on is: |
|
$query = "select * from visitors WHERE Name='$search'"; |
|
|
Yes, this line does all the work. We will play with it a bit later. |
|
Ok, this query gets all the records where the Name field is equal to the string search. Please note that an exact match is needed. |
|
Lets assume we want to search for a partial string match (i.e. where the search string appears in the filed but as part of the string and not an exact match). We will have to modify the script as follows: |
|
$srch="%".$search."%";
$query = "select * from visitors WHERE Name LIKE' $srch'"; |
|
|
The LIKE comparison argument will return '1' if the Name field has a partial value of $search. Note that I modified $search and added "%" on both ends. This allows to search for the search to ignore the leading characters and the characters following the search string. |
|
Ok, now lets assume we want to search all the field of the table and not only the Name field. In order to do that we need to choose the records with Name LIKE $srch or Last LIKE $srch etc. The translation to MySQL query is: |
|
$query = "select * from visitors WHERE Name LIKE '$srch' || Last LIKE '$srch' || email LIKE '$srch' || comment LIKE '$srch'"; |
|
|
The complete srch.php3 script top to bottom should look like: |
|
<?
if ($search) // perform search only if a string was entered.
{
mysql_connect() or die ("Problem connecting to DataBase");
$srch="%".$search."%";
$query = "select * from visitors WHERE Name LIKE '$srch' || Last LIKE '$srch' || email LIKE '$srch' || comment LIKE '$srch'";
$result = mysql_db_query("guest_book", $query);
if ($result)
{
echo "Here are the results:<br><br>";
echo "<table width=90% align=center border=1><tr>
<td align=center bgcolor=#00FFFF>Visit time and date</td>
<td align=center bgcolor=#00FFFF>User Name</td>
<td align=center bgcolor=#00FFFF>Last Name</td>
<td align=center bgcolor=#00FFFF>Email</td>
</tr>";
while ($r = mysql_fetch_array($result)) { // Begin while
$ts = $r["TimeStamp"];
$name = $r["Name"];
$last = $r["Last"];
$email = $r["email"];
$comment = $r["comment"];
echo "<tr>
<td>$ts</td>
<td>$name</td>
<td>$last</td>
<td>$email</td></tr>
<tr> <td colspan=4 bgcolor=\"#ffffa0\">$comment</td>
</tr>";
} // end while
echo "</table>";
} else { echo "problems...."; }
} else {
echo "Search string is empty. <br> Go back and type a string to search";
}
include ('links.x');
?> |
|
Posts
