For our search to work we will have to create two files. One file will be the PHP script to search our form and the other will be an HTML page containing our form and passing the search variable to our PHP file.
I will start by create an search.htm file. Below you will see the basic structure of our simple HTML page.
- <html>
- <head>
- <title>Search the Database</title>
- </head>
- <body>
- </body>
- </html>
<html> <head> <title>Search the Database</title> </head> <body> </body> </html>
Next we will add the form. Notice that the action=”search.php” and the method=”post”. This is basically telling the web server to send Post variables to the search.php page. Another important thing to note is the input box name field. This field, name=”term” , this will pass whatever is in the input box on as a post variable named “term”. We also add a submit button.
- <html>
- <head>
- <title>Search the Database</title>
- </head>
- <body>
- <form action="search.php" method="post">
- Search: <input type="text" name="term" /><br />
- <input type="submit" name="submit" value="Submit" />
- </form>
- </body>
- </html>
<html>
<head>
<title>Search the Database</title>
</head>
<body>
<form action="search.php" method="post">
Search: <input type="text" name="term" /><br />
<input type="submit" name="submit" value="Submit" />
</form>
</body>
</html>
Now on to search.php . The first thing I did was to simple echo the post variable to make sure the information is getting passed from the search form.
- <?php
- echo $_POST['term'];
- ?>
<?php
echo $_POST['term'];
?>
If you variable is not being displayed then something is wrong. If your search term is displayed then you can move on. We can delete the echo code from our php file now.
First make a connection to your database
- <?php
- mysql_connect ("localhost", "testuser","password") or die (mysql_error());
- ?>
Now select your test database
- <?php
- mysql_connect ("localhost", "testuser","password") or die (mysql_error());
- mysql_select_db ("test");
- ?>
<?php
mysql_connect ("localhost", "testuser","password") or die (mysql_error());
mysql_select_db ("test");
?>
Next we are going to store the post variable as $term and build our query. As you can see we are searching the first name field. We are searching for a first name like %$term%. The % character is a wild card for 0 or more charcters. So if we had ‘bob’ in our database and we entered the ‘bob’ in the search box it would return the results. With the % charcters around the term we could also search for ‘ob’ and it would return the results for bob, and any other name containing ‘ob’.
- <?php
- mysql_connect ("localhost", "testuser","password") or die (mysql_error());
- mysql_select_db ("test");
- $term = $_POST['term'];
- $sql = mysql_query("select * from testtable where FName like '%$term%'");
- ?>
<?php
mysql_connect ("localhost", "testuser","password") or die (mysql_error());
mysql_select_db ("test");
$term = $_POST['term'];
$sql = mysql_query("select * from testtable where FName like '%$term%'");
?>
The next step is to execute the query and display the results.
- <?php
- mysql_connect ("localhost", "testuser","password") or die (mysql_error());
- mysql_select_db ("test");
- $term = $_POST['term'];
- $sql = mysql_query("select * from testtable where FName like '%$term%'");
- while ($row = mysql_fetch_array($sql)){
- echo 'ID: '.$row['ID'];
- echo '<br/> First Name: '.$row['FName'];
- echo '<br/> Last Name: '.$row['LName'];
- echo '<br/> Phone: '.$row['Phone'];
- echo '<br/><br/>';
- }
- ?>
<?php
mysql_connect ("localhost", "testuser","password") or die (mysql_error());
mysql_select_db ("test");
$term = $_POST['term'];
$sql = mysql_query("select * from testtable where FName like '%$term%'");
while ($row = mysql_fetch_array($sql)){
echo 'ID: '.$row['ID'];
echo '<br/> First Name: '.$row['FName'];
echo '<br/> Last Name: '.$row['LName'];
echo '<br/> Phone: '.$row['Phone'];
echo '<br/><br/>';
}
?>
Now I will test it out. Since I know ‘Bob’ is in the database I will search for him.
If all goes well you should have these results returned/
So we can search for First names only. If you want to give the user the ability to enter either a first name or a last name change your query to this:
- $sql = mysql_query("select * from testtable where FName like '%$term%' or LName like '%$term%' ");
$sql = mysql_query("select * from testtable where FName like '%$term%' or LName like '%$term%' ");
To test that query I did a simple search for the letter ‘o’. Which should return several first names and last names that contain the letter.
So there you have it a simple basic database search form. I hope this all makes sense, I am writing this at 4:00am and am a bit tired. As always feel free to ask questions.
discuss this topic to forum
