Difference between revisions of "PHP112"

From mi-linux
Jump to navigationJump to search
 
(19 intermediate revisions by 3 users not shown)
Line 1: Line 1:
 +
[[Main Page]] >> [[CP2228|Web Application Development]] >> [[PHP|Workbook]] >> Simple HTML/PHP/MySQL example
 +
 
= A Simple HTML/PHP/MySQL Example =
 
= A Simple HTML/PHP/MySQL Example =
  
Line 8: Line 10:
 
# output the results
 
# output the results
  
−
== 1. A form to gather criteria (part1.html) ==
+
See working example [https://mi-linux.wlv.ac.uk/~in9352/php/pets-form.html here].
 +
 
 +
== 1. A form to gather criteria (pets-form.html) ==
  
−
<nowiki><HTML>
+
<pre>
−
   <HEAD>
+
<html>
−
   <TITLE>Part 1 - Form</TITLE>
+
   <head>
−
   </HEAD>
+
   <title>Part 1 - Form</title>
−
   <BODY>
+
   </head>
−
   <FORM action="part2.php" method="post">
+
   <body>
−
     <P>
+
   <form action="pets-search.php" method="post">
−
     Enter pet's name: <INPUT type="text" name="searchName">
+
     <p>
−
     </P>
+
     Enter pet's name: <input type="text" name="searchName">
−
     <P>
+
     </p>
−
     <INPUT type="submit" value="Search">
+
     <p>
−
     </P>
+
     <input type="submit" value="Search">
−
   </FORM>
+
     </p>
−
   </BODY>
+
   </form>
−
</HTML></nowiki>
+
   </body>
 +
</html>
 +
</pre>
 
   
 
   
−
== 2. The PHP script (part2.php) ==
+
== 2. The PHP script (pets-search.php) ==
 +
 
 +
<pre>
 +
<?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);
  
−
<nowiki><?
+
if($num_pets == 0)
−
    $dbServer=mysql_connect("localhost","cp1079_student","");
+
  print("<p>No pet with that name, sorry...</p>");
−
    if (!$dbServer) {echo "Failed to connect to MySQL"; exit; }
+
else {
−
   
+
  print("<p>We found $num_pets pet(s) matching thay name...</p>");
−
    mysql_select_db("cp1079",$dbServer);
+
 
−
   
+
  // Loop through resultset and display each field's value
−
    $sql ="SELECT * FROM pet";
+
  while($row = mysqli_fetch_assoc($res)) {
−
    $sql.=" WHERE name=\"".$_POST["searchName"]."\"";  // the space before the WHERE is critical
+
    echo $row['name']. " - ". $row['species'] ."<br>";
−
   
+
  }
−
    $queryResult=mysql_query($sql);
+
}
−
   
+
 
−
    if (mysql_error())
+
?>
−
    {
+
</pre>
−
      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>";
 
−
      }
 
−
    }
 
−
?></nowiki>
 
  
 
= Deconstruction of the example =
 
= Deconstruction of the example =
  
−
== part1.html ==
+
== pets-form.html ==
  
−
* setting up the form to direct to '''part2.php''' on submit, and pass input fields using POST
+
This line in very important:
−
<FORM action="part2.php" method="post">
+
<pre>
−
* creating a text field to allow input, and naming the field '''searchName''' - this is the name you need to use in '''part2.php'''
+
<form action="pets-search.php" method="post">
−
<INPUT type="text" name="searchName">
+
</pre>
 
   
 
   
−
== part2.php ==
+
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:
 +
 
 +
<pre>
 +
Enter pet's name: <input type="text" name="searchName">
 +
</pre>
 +
 
 +
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:
 +
 
 +
<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.
  
−
* connecting to the database and checking that there were no errors in the connection
+
Everything else is pretty much the same as before.
−
$dbServer=mysql_connect("localhost","cp1079_student","");
 
−
if (!$dbServer) {echo "Failed to connect to MySQL"; exit; }
 
  
−
* choosing which database to use on the MySQL server   
+
= Try it =
−
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.
+
* Implement and test the code above
−
else
+
* 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
−
  while ($dbRecord=mysql_fetch_array($queryResult))
+
= Ready to move on? =
−
  {
+
Let's tackle this week's [[PHPEX05|mini-task]]!
−
    echo "found: ".$dbRecord["name"].", ".$dbRecord["species"].", ".$dbRecord["owner"].", ".$dbRecord["age"]."<BR>";
 

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"