PHP112
From mi-linux
Jump to navigationJump to searchMain Page >> Web Application Development >> Workbook >> Simple HTML/PHP/MySQL example
A Simple HTML/PHP/MySQL Example
The following example will:
- use a form to gather criteria for a search
- pass the criteria to a PHP script
- build a custom query
- execute the query against a database, returning results
- output the results
1. A form to gather criteria (part1.html)
<html>
<head>
<title>Part 1 - Form</title>
</head>
<body>
<form action="part2.php" method="post">
<p>
Enter pet's name: <INPUT type="text" name="searchName">
</p>
<p>
<INPUT type="submit" value="Search">
</p>
</form>
</body>
</html>
2. The PHP script (part2.php)
<?
$dbServer=mysql_connect("localhost","cp1079_student","");
if (!$dbServer) {echo "Failed to connect to MySQL"; exit; }
mysql_select_db("cp1079",$dbServer);
$sql ="SELECT * FROM pet";
$sql.=" WHERE name=\"".$_POST["searchName"]."\""; // the space before the WHERE is critical
$queryResult=mysql_query($sql);
if (mysql_error())
{
echo "Problem with Query<BR>";
echo "The following error message was returned from MySQL:<BR>";
echo mysql_error();
exit;
}
if (mysql_num_rows($queryResult)==0)
{
echo "No pets with that name.";
}
else
{
while ($dbRecord=mysql_fetch_array($queryResult))
{
echo "found: ".$dbRecord["name"].", ".$dbRecord["species"].", ".$dbRecord["owner"].", ".$dbRecord["age"]."<BR>";
}
}
?>
Deconstruction of the example
part1.html
- setting up the form to direct to part2.php on submit, and pass input fields using POST
<FORM action="part2.php" method="post">
- creating a text field to allow input, and naming the field searchName - this is the name you need to use in part2.php
<INPUT type="text" name="searchName">
part2.php
- connecting to the database and checking that there were no errors in the connection
$dbServer=mysql_connect("localhost","cp1079_student","");
if (!$dbServer) {echo "Failed to connect to MySQL"; exit; }
- choosing which database to use on the MySQL server
mysql_select_db("cp1079",$dbServer);
- constructing the SQL statement, with a WHERE clause to filter the records to just those with the name we choose in part1.html, remembering that searchName was the name of the text field in the HTML file, and was sent using POST, so in the PHP file it is referenced by $_POST["searchName"]
$sql ="SELECT * FROM pet"; $sql.="WHERE name=\"".$_POST["searchName"]."\"";
- execute the query and store the returned results
$queryResult=mysql_query($sql);
- check for any errors when trying to execute the query, and output the error message if any has been sent by MySQL to PHP
if (mysql_error()) { ...echo mysql_error();}
- at this point, the query was successful, but we don't know if any records matched the name we keyed in, so count the number of records that MySQL sent to PHP that match the criteria we entered. if records=0, output a suitable message
if (mysql_num_rows($queryResult)==0)
{
echo "No pets with that name.";
}
- if more than 0 records were returned, loop through them in case there is more than one and print them out. This will still work if only one record is returned.
else
{
while ($dbRecord=mysql_fetch_array($queryResult))
{
echo "found: ".$dbRecord["name"].", ".$dbRecord["species"].", ".$dbRecord["owner"].", ".$dbRecord["age"]."
";
Ready to move on?
Let's tackle this week's mini-task!
