PHP112
Main 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
See working example here.
1. A form to gather criteria (pets-form.html)
<html>
<head>
<title>Part 1 - Form</title>
</head>
<body>
<form action="pets-search.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 (pets-search.php)
<?php
// Connect to server/database
$mysqli = mysqli_connect("localhost", "YOUR USERNAME", "YOUR PASSWORD", "YOUR DB");
if ($mysqli -> connect_errno) {
echo "Failed to connect to MySQL: " . $mysqli -> connect_error;
exit();
}
// Build custom SQL query
$sql = "SELECT name, species, age FROM pet";
// Add search criteria, if provided
if($_POST['searchName'] != "")
$sql.= " WHERE name LIKE '%" . $_POST['searchName'] . "%'";
// Run SQL query
$res = mysqli_query($mysqli, $sql);
// How many rows were returned?
$num_pets = mysqli_num_rows($res);
if($num_pets == 0)
print("<p>No pet with that name, sorry...</p>");
else {
print("<p>We found $num_pets pet(s) matching thay name...</p>");
// Loop through resultset and display each field's value
while($row = mysqli_fetch_assoc($res)) {
echo $row['name']. " - ". $row['species'] ."<br>";
}
}
?>
Deconstruction of the example
pets-form.html
This line in very important:
<form action="pets-search.php" method="post">
It specifies that anything entered in the current form should be posted to a file called pets-search.php. Obviously the latter should match the name of YOUR file.
On this line of code:
Enter pet's name: <input type="text" name="searchName">
It is important to note the name of the text field ("searchName"), as we will need it in the script below.
pets-search.php
A lot of the code above is similar to the previous section, so let's focus on what's new:
// Build custom SQL query $sql = "SELECT name, species, age FROM pet"; // Add search criteria, if provided if($_POST['searchName'] != "") $sql.= " WHERE name LIKE '%" . $_POST['searchName'] . "%'";
The code above is very important, so please make sure you understand is fully.
First, we define our basic SQL query, as per previous example.
$sql = "SELECT name, species, age FROM pet";
Then, we check if a value was typed in the form, and more specifically in the "searchName" field. We do this by accessing the $_POST superglobal, which contains all values POSTED to the current script.
if($_POST['searchName'] != "")
So if the user type "Bob" in the text field on the form, $_POST['searchName'] will contain the value "Bob".
If a value WAS typed (i.e. the field is not blank), we add the following WHERE clause to our basic query:
$sql.= " WHERE name LIKE '%" . $_POST['searchName'] . "%'";
Which equates to adding:
WHERE name LIKE '%Bob%'
(assuming the user typed "Bob" in the form). This will perform a partial search on the value provided. More on the SQL LIKE operator here.
// How many rows were returned?
$num_pets = mysqli_num_rows($res);
if($num_pets == 0)
print("<p>No pet with that name, sorry...</p>");
else {
print("<p>We found $num_pets pet(s) matching thay name...</p>");
Then we check if the search returned anything, by checking the number of rows in the result-set. And display a different message accordingly.
Everything else is pretty much the same as before.
Try it
- Implement and test the code above
- Modify the code above so that it searches on the species rather than the name
- Modify the code above so that it searches on both species and name
Ready to move on?
Let's tackle this week's mini-task!
