Difference between revisions of "PHP112"
| (9 intermediate revisions by 2 users not shown) | |||
| Line 9: | Line 9: | ||
# execute the query against a database, returning results | # execute the query against a database, returning results | ||
# output the results | # output the results | ||
| + | |||
| + | See working example [https://mi-linux.wlv.ac.uk/~in9352/php/pets-form.html here]. | ||
== 1. A form to gather criteria (pets-form.html) == | == 1. A form to gather criteria (pets-form.html) == | ||
| Line 20: | Line 22: | ||
<form action="pets-search.php" method="post"> | <form action="pets-search.php" method="post"> | ||
<p> | <p> | ||
| − | Enter pet's name: < | + | Enter pet's name: <input type="text" name="searchName"> |
</p> | </p> | ||
<p> | <p> | ||
| − | < | + | <input type="submit" value="Search"> |
</p> | </p> | ||
</form> | </form> | ||
| Line 36: | Line 38: | ||
// Connect to server/database | // Connect to server/database | ||
| − | $mysqli = mysqli_connect("localhost", " | + | $mysqli = mysqli_connect("localhost", "YOUR USERNAME", "YOUR PASSWORD", "YOUR DB"); |
| − | if | + | if ($mysqli -> connect_errno) { |
| − | + | echo "Failed to connect to MySQL: " . $mysqli -> connect_error; | |
| + | exit(); | ||
} | } | ||
| Line 82: | Line 85: | ||
<pre> | <pre> | ||
| − | Enter pet's name: < | + | Enter pet's name: <input type="text" name="searchName"> |
</pre> | </pre> | ||
| Line 116: | Line 119: | ||
So if the user type "Bob" in the text field on the form, $_POST['searchName'] will contain the value "Bob". | 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 | + | If a value WAS typed (i.e. the field is not blank), we add the following WHERE clause to our basic query: |
<pre> | <pre> | ||
| Line 126: | Line 129: | ||
<pre> | <pre> | ||
WHERE name LIKE '%Bob%' | WHERE name LIKE '%Bob%' | ||
| − | <pre> | + | </pre> |
| − | (assuming the user typed "Bob" in the form). This will perform a partial search on the value provided. | + | (assuming the user typed "Bob" in the form). This will perform a partial search on the value provided. More on the SQL LIKE operator [http://www.w3schools.com/sql/sql_like.asp here]. |
<pre> | <pre> | ||
| Line 144: | Line 147: | ||
Everything else is pretty much the same as before. | 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? = | = Ready to move on? = | ||
Let's tackle this week's [[PHPEX05|mini-task]]! | Let's tackle this week's [[PHPEX05|mini-task]]! | ||
Latest revision as of 11:47, 20 September 2023
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!
