Difference between revisions of "PHP112"

From mi-linux
Jump to navigationJump to search
Line 10: Line 10:
 
# output the results
 
# output the results
  
−
== 1. A form to gather criteria (part1.html) ==
+
== 1. A form to gather criteria (pets-form.html) ==
  
−
<nowiki><html>
+
<pre>
 +
<html>
 
   <head>
 
   <head>
 
   <title>Part 1 - Form</title>
 
   <title>Part 1 - Form</title>
 
   </head>
 
   </head>
 
   <body>
 
   <body>
−
   <form action="part2.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">
Line 26: Line 27:
 
   </form>
 
   </form>
 
   </body>
 
   </body>
−
</html></nowiki>
+
</html>
 +
</pre>
 
   
 
   
−
== 2. The PHP script (part2.php) ==
+
== 2. The PHP script (pets-search.php) ==
  
−
<nowiki><?
+
<pre>
−
    $dbServer=mysql_connect("localhost","cp1079_student","");
+
<?php
−
    if (!$dbServer) {echo "Failed to connect to MySQL"; exit; }
+
 
−
   
+
// Connect to server/database
−
    mysql_select_db("cp1079",$dbServer);
+
$mysqli = mysqli_connect("localhost", "bdtuser", "bdtuser", "bdtheque");
−
   
+
if (mysqli_connect_errno($mysqli)) {
−
    $sql ="SELECT * FROM pet";
+
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
−
    $sql.=" WHERE name=\"".$_POST["searchName"]."\""; // the space before the WHERE is critical
+
}
−
   
+
 
−
    $queryResult=mysql_query($sql);
+
// Build custom SQL query
−
   
+
$sql = "SELECT name, species, age FROM pet";
−
    if (mysql_error())
+
 
−
    {
+
// Add search criteria, if provided
−
      echo "Problem with Query<BR>";
+
if($_POST['searchName'] != "")
−
      echo "The following error message was returned from MySQL:<BR>";
+
  $sql.= " WHERE name LIKE '%" . $_POST['searchName'] . "%'";
−
      echo mysql_error();
+
 
−
      exit;
+
// Run SQL query
−
    }
+
$res = mysqli_query($mysqli, $sql);
−
   
+
 
−
    if (mysql_num_rows($queryResult)==0)
+
// How many rows were returned?
−
    {
+
$num_pets = mysqli_num_rows($res);
−
      echo "No pets with that name.";
+
 
−
    }
+
if($num_pets == 0)
−
    else
+
  print("<p>No pet with that name, sorry...</p>");
−
    {
+
else {
−
      while ($dbRecord=mysql_fetch_array($queryResult))
+
  print("<p>We found $num_pets pet(s) matching thay name...</p>");
−
      {
+
 
−
        echo "found: ".$dbRecord["name"].", ".$dbRecord["species"].", ".$dbRecord["owner"].", ".$dbRecord["age"]."<BR>";
+
  // Loop through resultset and display each field's value
−
      }
+
  while($row = mysqli_fetch_assoc($res)) {
−
    }
+
    echo $row['name']. " - ". $row['species'] ."<br>";
−
?></nowiki>
+
  }
 +
}
 +
 
 +
?>
 +
</pre>
  
 
= Deconstruction of the example =
 
= Deconstruction of the example =
  
−
== part1.html ==
+
A lot of the code above is similar to the previous section, so let's focus on what's new:
 +
 
 +
== 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>
  
−
* connecting to the database and checking that there were no errors in the connection
+
It is important to note the name of the text field ("searchName"), as we will need it in the script below.
−
$dbServer=mysql_connect("localhost","cp1079_student","");
 
−
if (!$dbServer) {echo "Failed to connect to MySQL"; exit; }
 
  
−
* choosing which database to use on the MySQL server   
+
== pets-search.php ==
−
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"]."<BR>";
 
  
 
= 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]]!

Revision as of 15:15, 29 June 2016

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

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", "bdtuser", "bdtuser", "bdtheque");
if (mysqli_connect_errno($mysqli)) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

// 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

A lot of the code above is similar to the previous section, so let's focus on what's new:

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

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=9071"