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;
}
