Querying a Database

Contact Us or call 1-877-932-8228
Querying 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
<!DOCTYPE HTML>
<html>
<head>
<meta charset="UTF-8">
<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
<!DOCTYPE HTML>
<html>
<head>
<meta charset="UTF-8">
<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>
  1. To connect to the database, we use:
    • 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
---- C O D E   O M I T T E D ----
	<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>
---- C O D E   O M I T T E D ----

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();
	}
?>
<!DOCTYPE HTML>
<html>
<head>
<meta charset="UTF-8">
<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>

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();
?>

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 *********/
---- C O D E   O M I T T E D ----

/*
	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);
	}
}
?>

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 *********/
---- C O D E   O M I T T E D ----
/*
	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;
	}
}
?>

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']);
---- C O D E   O M I T T E D ----
	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']);
	}
---- C O D E   O M I T T E D ----
?>
<?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['Password1']))
				{
					$query .= ", Password='" . $dbEntries['Password1'] . "'";
				}
				$query .= " WHERE EmployeeID = $employeeID";
		$db->query($query);
		echo '<div align="center">Record Updated</div>';
	}
	elseif (!count($errors))
	{
		$showForm = false;
?>

---- C O D E   O M I T T E D ----
	}
	else
	{
		$dbEntries = $_POST;
	}
?>

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 $entrya 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.

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 see the results, open the file in your browser passing a city (e.g, London) via the query string. For example, http://localhost/Webucator/ClassFiles/ManagingData/Demos/PreparedStatement.php?City=London
To illustrate, look at the following example.

Code Sample:

ManagingData/Demos/PreparedStatement.php
<!DOCTYPE HTML>
<html>
<head>
<meta charset="UTF-8">
<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>

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.
Next