Difference between revisions of "PHP112"

From mi-linux
Jump to navigationJump to search
 
(12 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: <INPUT type="text" name="searchName">
+
     Enter pet's name: <input type="text" name="searchName">
 
     </p>
 
     </p>
 
     <p>
 
     <p>
−
     <INPUT type="submit" value="Search">
+
     <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", "bdtuser", "bdtuser", "bdtheque");
+
$mysqli = mysqli_connect("localhost", "YOUR USERNAME", "YOUR PASSWORD", "YOUR DB");
−
if (mysqli_connect_errno($mysqli)) {
+
if ($mysqli -> connect_errno) {
−
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
+
  echo "Failed to connect to MySQL: " . $mysqli -> connect_error;
 +
  exit();
 
}
 
}
  
Line 69: Line 72:
  
 
= Deconstruction of the example =
 
= Deconstruction of the example =
−
 
−
A lot of the code above is similar to the previous section, so let's focus on what's new:
 
  
 
== pets-form.html ==
 
== pets-form.html ==
Line 84: Line 85:
  
 
<pre>
 
<pre>
−
Enter pet's name: <INPUT type="text" name="searchName">
+
Enter pet's name: <input type="text" name="searchName">
 
</pre>
 
</pre>
  
Line 91: Line 92:
 
== pets-search.php ==
 
== pets-search.php ==
  
 +
A lot of the code above is similar to the previous section, so let's focus on what's new:
 +
 +
<pre>
 +
// 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'] . "%'";
 +
</pre>
 +
 +
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.
 +
 +
<pre>
 +
$sql = "SELECT name, species, age FROM pet";
 +
</pre>
 +
 +
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.
 +
 +
<pre>
 +
if($_POST['searchName'] != "")
 +
</pre>
 +
 +
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:
 +
 +
<pre>
 +
  $sql.= " WHERE name LIKE '%" . $_POST['searchName'] . "%'";
 +
</pre>
 +
 +
Which equates to adding:
 +
 +
<pre>
 +
WHERE name LIKE '%Bob%'
 +
</pre>
 +
 +
(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>
 +
// 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>");
 +
</pre>
 +
 +
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? =
 
= 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 10: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:

  1. use a form to gather criteria for a search
  2. pass the criteria to a PHP script
  3. build a custom query
  4. execute the query against a database, returning results
  5. 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!

Retrieved from "https://mi-linux.wlv.ac.uk/wiki/index.php?title=PHP112&oldid=10321"