PHP & MySql Database

Managing Data

In this lesson of the PHP tutorial, you will learn… 

  1. To retrieve and safely display records from a database.
  2. To insert new records into a database.
  3. To update existing records in a database.

Querying a Database

The steps for querying a database in a PHP script are as follows:

  1. Connect to the database.
  2. Send query to the database.
  3. Retrieve and store results sent back from the database.
  4. Output the results of the query to the browser.
  5. Free up resources and disconnect from the database.

mysqli() Overview

New in PHP5 is the mysqli library, which works with MySQL versions 4.1.3 and above and takes advantage of a new faster connection protocol in MySQL. The mysqli library provides two interfaces: an object-oriented interface and a procedural interface. The following two scripts use the two different interfaces to accomplish the same result.

Code Sample: ManagingData/Demos/SimpleQuery-OO.php

<html>
<head>
<title>Simple Query - OO</title>
</head>
<body>
<?php
@$DB = new mysqli('localhost','root','pwdpwd','Northwind');
if (mysqli_connect_errno())
{
 echo 'Cannot connect to database: ' . mysqli_connect_error();
}
else
{
 $Query = 'SELECT * FROM Employees';
 $Result = $DB->query($Query);
 $NumResults = $Result->num_rows;

 echo "<b>$NumResults Employees</b>";
?>
 <table border="1">
 <tr>
  <th>First Name</th>
  <th>Last Name</th>
  <th>Title</th>
  <th>Email</th>
  <th>Extension</th>
 </tr>
<?php
 while ($Row = $Result->fetch_assoc())
 {
  echo '<tr>';
  echo '<td>' . $Row['FirstName'] . '</td>';
  echo '<td>' . $Row['LastName'] . '</td>';
  echo '<td>' . $Row['Title'] . '</td>';
  echo '<td>' . $Row['Email'] . '</td>';
  echo '<td align="right">x' . $Row['Extension'] . '</td>';
  echo '</tr>';
 }
?>
 </table>
<?php
 $Result->free();
 $DB->close();
}
?>
</body>
</html>

Code Sample: ManagingData/Demos/SimpleQuery-Procedural.php

<html>
<head>
<title>Simple Query - Procedural</title>
</head>
<body>
<?php
@$DB = mysqli_connect('localhost','root','pwdpwd','Northwind');
if (mysqli_connect_errno())
{
 echo 'Cannot connect to database: ' . mysqli_connect_error();
}
else
{
 $Query = 'SELECT * FROM Employees';
 $Result = mysqli_query($DB,$Query);
 $NumResults = mysqli_num_rows($Result);

 echo "<b>$NumResults Employees</b>";
?>
 <table border="1">
 <tr>
  <th>First Name</th>
  <th>Last Name</th>
  <th>Title</th>
  <th>Email</th>
  <th>Extension</th>
 </tr>
<?php
 while ($Row = mysqli_fetch_assoc($Result))
 {
  echo '<tr>';
  echo '<td>' . $Row['FirstName'] . '</td>';
  echo '<td>' . $Row['LastName'] . '</td>';
  echo '<td>' . $Row['Title'] . '</td>';
  echo '<td>' . $Row['Email'] . '</td>';
  echo '<td align="right">x' . $Row['Extension'] . '</td>';
  echo '</tr>';
 }
?>
 </table>
<?php
 mysqli_free_result($Result);
 mysqli_close($DB);
}
?>
</body>
</html>
Code Explanation
  1. To connect to the database, we use: (see footnote)
    • Object-oriented
      @$DB = new mysqli('localhost','root','pwdpwd','Northwind');
    • Procedural:
      @$DB = mysqli_connect('localhost','root','pwdpwd','Northwind');
  2. To see if the connection was successful we check mysqli_connect_errno(), which returns an error number if there is a connection error or 0 if there is no error. If an error occurs, we output a message with mysqli_connect_error(). We use the procedural interface in both cases, because a connection object doesn’t get created if the connection fails.
  3. To send the query to the database and store the results in a variable, we use:
    • Object-oriented: $Result = $DB->query($Query);
    • Procedural: $Result = mysqli_query($DB,$Query);
  4. To output the results of the query, we use:
    • Object-oriented:
      while ($Row = $Result->fetch_assoc())
      {
        echo "<tr>";
        echo "<td>" . $Row['FirstName'] . "</td>";
        echo "<td>" . $Row['LastName'] . "</td>";
        echo "<td>" . $Row['Title'] . "</td>";
        echo "<td>" . $Row['Email'] . "</td>";
        echo "<td align='right'>x" . $Row['Extension'] . "</td>";
        echo "</tr>";
      }
    • Procedural:
      while ($Row = mysqli_fetch_assoc($Result))
      {
        echo "<tr>";
        echo "<td>" . $Row['FirstName'] . "</td>";
        echo "<td>" . $Row['LastName'] . "</td>";
        echo "<td>" . $Row['Title'] . "</td>";
        echo "<td>" . $Row['Email'] . "</td>";
        echo "<td align='right'>x" . $Row['Extension'] . "</td>";
        echo "</tr>";
      }
  5. To free up resources and disconnect from the database, we use:
    • Object-oriented:
      $Result->free();
      $DB->close();
    • Procedural:
      mysqli_free_result($Result);
      mysqli_close($DB);

As you can see, the two scripts are pretty similar. We will use the object-oriented interface in future examples.

mysqli Methods and Properties

Connection Methods and Properties
Object-oriented Procedural Description
new mysqli() mysqli_connect() Connects to a MySQL server.
mysqli_connect_errno() Returns connection error number or 0 if there’s no error.
mysqli_connect_error() Returns connection error message.
$DB->host_info mysqli_get_host_info() Returns information on the connection.
Query Functions
Object-oriented Procedural Description
$DB->query() mysqli_query() Sends a query to the database and returns results.
$DB->multi_query() mysqli_multi_query() Sends multiple queries to the database and returns results.
Fetch Functions
Object-oriented Procedural Description
$Result->fetch_row() mysqli_fetch_row() Returns a result row from a query result object or resource as an indexed array.
$Result->fetch_assoc() mysqli_fetch_assoc() Returns a result row from a query result object or resource as an associative array.
$Result->fetch_object() mysqli_fetch_object() Returns a result row from a query result object or resource as an object.

Inserting and Updating Records

Records are inserted and updated with SQL queries using the same mysqli library we used to generate a report. Review the following scripts.

Code Sample: ManagingData/Demos/EmployeeReport.php

---- Code Omitted ----
<table border="1"> <tr> <th>First Name</th> <th>Last Name</th> <th>Title</th> <th>Email</th> <th>Extension</th> <th>Edit</th> </tr> <?php while ($Row = $Result->fetch_assoc()) { echo '<tr>'; echo '<td>' . $Row['FirstName'] . '</td>'; echo '<td>' . $Row['LastName'] . '</td>'; echo '<td>' . $Row['Title'] . '</td>'; echo '<td>' . $Row['Email'] . '</td>'; echo '<td align="right">x' . $Row['Extension'] . '</td>'; echo '<td><form method="post" action="EditEmployee.php"> <input type="hidden" name="EmployeeID" value="' . $Row['EmployeeID'] . '"/> <input type="submit" name="Editing" value="Edit"/> </form></td>'; echo '</tr>'; } ?> </table>
---- Code Omitted ----
Code Explanation

This file is similar to the SimpleQuery examples we saw earlier in this lesson. The only difference is that each row now has an edit form in the last column, which sends the employee’s EmployeeID to EditEmployee.php.

Code Sample: ManagingData/Demos/EditEmployee.php

<?php
 require 'Includes/fnFormValidation.php';
 require 'Includes/fnFormPresentation.php';
 require 'Includes/fnStrings.php';
 require 'Includes/fnDates.php';
 require 'Includes/init.php';
 @$DB = new mysqli('localhost','root','pwdpwd','Northwind');
 if (mysqli_connect_errno())
 {
  echo 'Cannot connect to database: ' . mysqli_connect_error();
 }
?>
<html>
<head>
<title>Edit Employee</title>
<style type="text/css">
 .Error {color:red; font-size:smaller;}
</style>
</head>
<body>
<?php
 require 'Includes/Header.php';

 if (array_key_exists('Updating',$_POST))
 {
  require 'Includes/ProcessEmployee.php';
 }

 require 'Includes/EmployeeData.php';
 require 'Includes/EmployeeForm.php';

 require 'Includes/Footer.php';

 $DB->close();
?>
</body>
</html>
Code Explanation

This file is similar to the AddEmployee.php file we worked on earlier in the course. This one goes a step further though by connecting to the database to retrieve data to populate the form. It works as follows:

  1. At the very top, we include several files we will need for the application and we connect to the database.
  2. In the body, we include:
    • our header and footer files.
    • code that checks whether the user has already made updates and, if so, includes the processing file.
    • the file with code to retrieve the specified employee’s data.
    • the file with code to display the filled-in form.

Code Sample: ManagingData/Demos/Includes/EmployeeData.php

<?php
 $EmployeeID = $_POST['EmployeeID'];

 $Query = "SELECT FirstName, LastName, Title,
   TitleOfCourtesy, Email,
   MONTH(BirthDate) AS BirthMonth,
   DAYOFMONTH(BirthDate) AS BirthDay,
   YEAR(BirthDate) AS BirthYear,
   MONTH(HireDate) AS HireMonth,
   DAYOFMONTH(HireDate) AS HireDay,
   YEAR(HireDate) AS HireYear,
   Address, City, Region, PostalCode, Country,
   HomePhone, Extension, Notes, ReportsTo, Password
   FROM Employees
   WHERE EmployeeID = $EmployeeID";
 $Result = $DB->query($Query);
 $DbEntries = $Result->fetch_assoc();

 $Result->free();
?>
Code Explanation

This file contains the query that selects the specified employee’s data and populates the $DbEntries array with the results.

Code Sample: ManagingData/Demos/Includes/fnStrings.php

<?php
/********** STRING FUNCTIONS *********/
---- Code Omitted ----
/* Function Name: dbString Arguments: $String Returns: trimmed and escaped string for database entry */ function dbString($String) { $String=trim($String); if (get_magic_quotes_gpc()) { return $String; } else { return addslashes($String); } } ?>
Code Explanation

This file is the same as the fnStrings.php file we saw before except that the dbString() function has been updated. It now checks to see if magic quotes are turned on. If they are, then form entries will be made database-safe “automagically”, so it just returns the trimmed string. If they are not, then it uses addslashes() to make the string safe for database queries.

Code Sample: ManagingData/Demos/Includes/fnFormValidation.php

<?php
/********* FORM VALIDATION FUNCTIONS *********/
---- Code Omitted ----
/* Function Name: checkPassword Arguments: $PW1,$PW2,$CheckLength? Returns: false if $PW1 has fewer than 6 characters false if $PW1 has more than 12 characters false if $PW1 and $PW2 do not match true otherwise */ function checkPassword($PW1,$PW2,$CheckLen=true) { $PW1 = trim($PW1); $PW2 = trim($PW2); if ($CheckLen) { return checkLength($PW1,6,12) && strcmp($PW1,$PW2) == 0; } else { return strcmp($PW1,$PW2) == 0; } } ?>
Code Explanation

This file is the same as the fnFormValidation.php file we saw before except that the checkPassword() function now takes an additional parameter: $CheckLen, which when set to false, will prevent the function from returning false if a blank password is entered.

Code Sample: ManagingData/Demos/Includes/ProcessEmployee.php

<?php
 $DbEntries = $_POST;
 foreach ($DbEntries as &$Entry)
 {
  $Entry = dbString($Entry);
 }
 $DbEntries['Title'] = ucwords($DbEntries['Title']);
---- Code Omitted ----
if (array_key_exists('EmployeeID',$_POST)) { $PwCheckLen = false; } else { $PwCheckLen = true; } if ( !checkPassword($_POST['Password1'],$_POST['Password2'],$PwCheckLen) ) { $Errors['Password'] = 'Passwords do not match or are not the right length.'; } else { $BrowserEntries['Password'] = browserString($_POST['Password1']); }
---- Code Omitted ----
?> <?php if (!count($Errors) && array_key_exists('EmployeeID',$_POST)) { $EmployeeID = $_POST['EmployeeID']; $Query = "UPDATE Employees SET FirstName='" . $DbEntries['FirstName'] . "', LastName='" . $DbEntries['LastName'] . "', Title='" . $DbEntries['Title'] . "', TitleOfCourtesy='" . $DbEntries['TitleOfCourtesy'] . "', Email='" . $DbEntries['Email'] . "', BirthDate='" . $DbEntries['BirthYear'] . '-' . $DbEntries['BirthMonth'] . '-' . $DbEntries['BirthDay'] . "', HireDate='" . $DbEntries['HireYear'] . '-' . $DbEntries['HireMonth'] . '-' . $DbEntries['HireDay'] . "', Address='" . $DbEntries['Address'] . "', City='" . $DbEntries['City'] . "', Region='" . $DbEntries['Region'] . "', PostalCode='" . $DbEntries['PostalCode'] . "', Country='" . $DbEntries['Country'] . "', HomePhone='" . $DbEntries['HomePhone'] . "', Extension='" . $DbEntries['Extension'] . "', Notes='" . $DbEntries['Notes'] . "', ReportsTo=" . $DbEntries['ReportsTo']; if (CheckLength($DbEntries['Password'])) { $Query .= ", Password='" . $DbEntries['Password'] . "'"; } $Query .= " WHERE EmployeeID = $EmployeeID"; $DB->query($Query); echo '<div align="center">Record Updated</div>'; } elseif (!count($Errors)) { $ShowForm = false; ?>
---- Code Omitted ----
} else { $DbEntries = $_POST; } ?>
Code Explanation

This file is the same as the ProcessEmployee.php file we saw before with a few important changes:

  1. Instead of assigning values from $_POST to $DbEntries one by one, we simply copy $_POST into $DbEntries and then loop through the array to pass each element through dbString(). Notice the use of the & to make $Entry a reference to rather than a copy of the array element.
  2. The call to checkPassword() now contains a third parameter to specify whether the function should return false if the password fields are not filled out. We only want to check the password length for new entries, not for updates.
  3. If the EmployeeID key exists in the $_POST array, then the Edit Employee form has been submitted and a database query will be executed to update the employee record.

Exercise: Inserting Records

Duration: 30 to 40 minutes.

In this exercise, you will work on the InsertEmployee.php script to insert new employees.

  1. Open ManagingData/Exercises/InsertEmployee.php for editing.
  2. Add code at the top to populate $DbEntries with SQL-safe entries from the form.
  3. Make the connection to the database.
  4. The SQL insert query has been started for you. Finish it.
  5. Write code to execute the query.
    • If the query succeeds, return “Employee Added” and a link to EmployeeReport.php. You will also need to set $ShowForm to false to prevent the form from reappearing.
    • If the query fails, return “Insert failed”.

Code Sample: ManagingData/Exercises/Includes/InsertEmployee.php

<?php
 //Add code to populate $DbEntries with
 //SQL-safe entries from the form.

 //Connect to the database

 //Finish the query below
 $Query = "INSERT INTO Employees
  (FirstName, LastName, Title,
   TitleOfCourtesy, Email, BirthDate, HireDate,
   Address, City, Region, PostalCode, Country,
   HomePhone, Extension, Notes, ReportsTo, Password)...

 
 //Execute the query.
 //If the query succeeds, return 'Employee Added'
 // and provide a link to EmployeeReport.php
 // and set $ShowForm to false
 //If it fails, return 'Insert failed'
?>

Instead of hiding the form after an employee is inserted. Provide a new empty form, so that the user can enter another employee.

mysqli Prepared Statements

With mysqli it is possible to create prepared statements, which improve performance and improve security. Prepared statements are essentially templated queries to which you can bind input and output variables. To illustrate, look at the following example. (see footnote)

Code Sample: ManagingData/Demos/PreparedStatement.php

<html>
<head>
<title>Prepared Statement</title>
</head>
<body>
<?php
$CompanyID = $_GET['City']; //Try London

@$DB = new mysqli('localhost','root','pwdpwd','Northwind');
if (mysqli_connect_errno())
{
 echo 'Cannot connect to database: ' . mysqli_connect_error();
}
else
{
 $Query = 'SELECT CompanyName,ContactName,Phone
   FROM Customers WHERE City=?';
 $Stmt = $DB->prepare($Query);
 $Stmt->bind_param('s',$CompanyID);
 $Stmt->execute();
 $Stmt->bind_result($Company,$Contact,$Phone);
?>
 <table border="1">
 <tr>
  <th>Company</th>
  <th>Contact</th>
  <th>Phone</th>
 </tr>
<?php
 while ($Stmt->fetch())
 {
  echo '<tr>';
  echo "<td>$Company</td>";
  echo "<td>$Contact</td>";
  echo "<td>$Phone</td>";
  echo '</tr>';
 }
?>
 </table>
<?php
 $Stmt->close();
 $DB->close();
}
?>
</body>
</html>
Code Explanation

Using the object-oriented interface, the process is as follows:

  1. A query is created with question marks used as placeholders for parameters (input variables).
  2. A statement is prepared using the prepare() method, which takes a query as an argument.
  3. Parameters can be bound to the statement with the bind_param() method. The first argument of this method is a string that has one character for each subsequent argument. The character specifies the data type of that input parameter. For example, “isd” would specify that the input parameters are an integer, string, and double, in that order. The only other option is b for blob.
  4. The statement is executed with the execute() method.
  5. If the query returns results (e.g, SELECT queries), they can be bound to the statement with the bind_result() method.
  6. The fetch() method can be used to fetch rows from the result set.

Prepared statements are most useful in cases where you have to loop through a dataset to do bulk inserts or updates.

Prepare Functions
Object-oriented Procedural Description
$db->prepare() mysqli_prepare() Prepares a SQL statement for execution.
$Stmt->bind_result() mysqli_stmt_bind_result() Binds variables to a prepared statement results.
$Stmt->bind_param() mysqli_stmt_bind_param() Binds variables to a prepared statement.
$Stmt->execute() mysqli_stmt_execute() Executes a prepared statement.
$Stmt->fetch() mysqli_stmt_fetch() Fetches results into the bound variables.
$Stmt->close() mysqli_stmt_close() Closes prepared statement.

Managing Data Conclusion

In this lesson of the PHP tutorial, you have learned to integrate PHP with a database to create reports and insert and update records.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s