PHP retrieves data from MySQL
Now we'll create another HTML form to search the database. We'll call it searchform.html:
<html>
<body>
<form action=searchform.php method=GET>
Search For:
<p>
First Name: <input type=text name=first_name size=25 maxlength=25>
<p>
Last Name: <input type=text name=last_name size=25 maxlength=25>
<p>
<input type=submit>
</form>
</body>
</html>
You will also have to create the following file
searchform.php:
<html>
<body>
<?php
mysql_connect (localhost, username, password);
mysql_select_db (dbname);
if ($first_name == "")
{$first_name = '%';}
if ($last_name == "")
{$last_name = '%';}
$result = mysql_query ("SELECT * FROM tablename
WHERE first_name LIKE '$first_name%'
AND last_name LIKE '$last_name%'
");
if ($row = mysql_fetch_array($result)) {
do {
print $row["first_name"];
print (" ");
print $row["last_name"];
print ("<p>");
} while($row = mysql_fetch_array($result));
} else {print "Sorry, no records were found!";}
?>
</body>
</html>
Remember to replace "username", "password", "dbname",
and "tablename". When you open searchform.html you should see a form. If you enter a name and click Submit, you should get a new webpage showing the full record or records matching your search terms.
Going over searchform.php carefully, it begins with the familiar lines to open the MySQL database. Then there are four lines:
if ($first_name == "")
{$first_name = '%';}
if ($last_name == "")
{$last_name = '%';}
These lines check if the form fields are empty. The
if function is followed by parentheses, and what's in the parentheses is the statement to be tested. The statement $first_name == "" means "The variable $first_name is empty." Note that the double equals signs means "equals". A single equals sign means "assign the value from what's on the right to the variable on the left." The next line is what is done when the if statement is evaluated as true. What's done is a PHP line, so it ends with a semi-colon. (Note that the if function doesn't end with a semi-colon.) The PHP line is put in curly brackets.
The % is SQL's character string wildcard. (Don't confuse it with SQL's * column name wildcard.) The first two lines together mean "If the 'first name' field is empty, search for any first name." The latter two lines similarly checks if the $last_name variable is empty, it searches for any last name. This enables you to find everyone named "Fred" or everyone named "Flintstone".
$result = mysql_query ("SELECT * FROM tablename
WHERE first_name LIKE '$first_name%'
AND last_name LIKE '$last_name%'"
");
This line does most of the work. When
mysql_query does a QUERY, the result is an integer identifier. The result identifier could be "2". This query selects all the columns from the specified table, then searches for records in which the "first_name" column matches the "$first_name" variable from searchform.html and the "last_name" column matches the "$last_name" variable. Note the % wildcard: this enables the user to type only the first few letters of a name, e.g, "Flint" finds "Flintstone".
if ($row = mysql_fetch_array($result)) {
do {
print $row["first_name"];
print (" ");
print $row["last_name"];
print ("<p>");
} while($row = mysql_fetch_array($result));
} else {print "Sorry, no records were found!";}
The last part of the script throws the search results
onto the new webpage. mysql_fetch_array grabs the first row of the query results. This function's argument is the result identifier ($result). Successive calls to mysql_fetch_array will return the next rows of the query. The array variable $row is created and filled with the first row of the query results.
If a matching record was found, then the block in the outermost curly brackets is done:
do {
print $row["first_name"];
print (" ");
print $row["last_name"];
print ("<p>");
} while($row = mysql_fetch_array($result));
This is a do…while loop. Unlike a
while loop, the block of code is done, then afterwards a decision is made whether to do the block of code again. A while loop decides first whether to do the block of code. What is done is inside the nest set of curly brackets:
print $row["first_name"];
print (" ");
print $row["last_name"];
print ("<p>");
The integer $row specified which record
to print. The first time through, the first "first_name" is printed, followed by a space, and then the first "last_name" is printed, followed by a paragraph mark. Now we get to the while decision. The MySQL database is called again, using the mysql_fetch_array function. mysql_fetch_array calls the next row until no rows are left. The do block is then executed again.
When no rows are left, mysql_fetch_array returns false, the do…while loop is stopped, and the if function is exited.
Why didn't we just implode the results of mysql_fetch_array($result) and print the imploded string? This results in printing each element twice. mysql_fetch_array($result) allows you to specify elements by the field name (e.g., "first_name" or by the number of the field, with the first field being "0", the second field is "1" and so on. Thus we could have written this block of code as:
print $row[0];
print (" ");
print $row[1];
print ("<p>");
We could also shorten these four lines of code into
one line:
echo $row[0], " ", $row[1], "<p>";
If no matching records were found, then
mysql_fetch_array returns nothing and no value can be assigned to $row, and the else clause is executed:
else {print "Sorry, no records were found!";}
Testing whether your query worked
Did your SELECT, DELETE, or other query work? Good question, and not always easy to answer. Testing an INSERT query is relatively simple:
$result = mysql_query ("INSERT INTO tablename (first_name, last_name)
VALUES ('$first_name', '$last_name')
");
if(!$result)
{
echo "<b>INSERT unsuccessful:</b> ", mysql_error();
exit;
}
But that code doesn't work with a
SELECT query. Instead, I use:
$selectresult = mysql_query ("SELECT * FROM tablename
WHERE first_name = '$first_name'
AND last_name = '$last_name'
");
if (mysql_num_rows($selectresult) == 1)
{
print "Your SELECT query was successful.";
}
elseif (mysql_num_rows($selectresult) == 0)
{
print "Your SELECT query was not successful.";
exit;
}
And that code doesn't work with a
DELETE queries. Here's how to test those:
$deleteresult = mysql_query ("DELETE FROM tablename
WHERE first_name = '$first_name'
AND last_name = '$last_name'
");
if (mysql_affected_rows($deleteresult) == 1)
{
print "Your DELETE query was successful.";
}
elseif (mysql_affected_rows($deleteresult) != 1)
{
print "Your DELETE query was not successful.";
exit;
}
PHP 
/ 100 
