Article
Build Your Own Database Driven Web Site Using PHP & MySQL, Part 4: Publishing MySQL Data on the Web
Sending SQL Queries with PHP
In Chapter 2: Introducing MySQL, we connected to the MySQL database server using a program called mysql that allowed us to type SQL queries (commands) and view the results of those queries immediately. In PHP, a similar mechanism exists: the mysqli_query function.
mysqli_query(link, query)
Here query is a string that contains the SQL query you want to execute. As with mysqli_select_db, you must also provide the MySQL link identifier returned by mysqli_connect.
What this function returns will depend on the type of query being sent. For most SQL queries, mysqli_query returns either true or false to indicate success or failure respectively. Consider the following example, which attempts to create the joke table we created in Chapter 2, Introducing MySQL:
$sql = 'CREATE TABLE joke (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
joketext TEXT,
jokedate DATE NOT NULL
) DEFAULT CHARACTER SET utf8';
if (!mysqli_query($link, $sql))
{
$output = 'Error creating joke table: ' . mysqli_error($link);
include 'output.html.php';
exit();
}
$output = 'Joke table successfully created.';
include 'output.html.php';
Note once again we use the same if statement technique to handle possible errors produced by the query. This example also uses the mysqli_error function to retrieve a detailed error message from the MySQL server. The figure below shows the error that’s displayed when the joke table already exists, for example.
For DELETE, INSERT, and UPDATE queries (which serve to modify stored data), MySQL also keeps track of the number of table rows (entries) that were affected by the query. Consider the SQL command below, which we used in Chapter 2: Introducing MySQL to set the dates of all jokes that contained the word “chicken”:
$sql = 'UPDATE joke SET jokedate="2010-04-01"
WHERE joketext LIKE "%chicken%"';
if (!mysqli_query($link, $sql))
{
$output = 'Error performing update: ' . mysqli_error($link);
include 'output.html.php';
exit();
}
When we execute this query, we can use the mysql_affected_rows function to view the number of rows that were affected by this update:
$output = 'Updated ' . mysqli_affected_rows($link) . ' rows.';
include 'output.html.php';
The following figure shows the output of this example, assuming you only have one “chicken” joke in your database.
If you refresh the page to run the same query again, you should see the message change as shown in the figure below to indicate that no rows were updated, since the new date being applied to the jokes is the same as the existing date.
SELECT queries are treated a little differently as they can retrieve a lot of data, and PHP provides ways to handle that information.
Handling SELECT Result Sets
For most SQL queries, the mysqli_query function returns either true (success) or false (failure). For SELECT queries, more information is needed. You’ll recall that SELECT queries are used to view stored data in the database. In addition to indicating whether the query succeeded or failed, PHP must also receive the results of the query. Thus, when it processes a SELECT query, mysqli_query returns a result set, which contains a list of all the rows (entries) returned from the query. false is still returned if the query fails for any reason:
$result = mysqli_query($link, 'SELECT joketext FROM joke');
if (!$result)
{
$error = 'Error fetching jokes: ' . mysqli_error($link);
include 'error.html.php';
exit();
}
As before, errors are displayed using a very simple PHP template:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<title>PHP Error</title>
<meta http-equiv="content-type"
content="text/html; charset=utf-8"/>
</head>
<body>
<p>
<?php echo $error; ?>
</p>
</body>
</html>
Provided that no error was encountered in processing the query, the above code will store a result set into the variable $result. This result set contains the text of all the jokes stored in the joke table. As there’s no practical limit on the number of jokes in the database, that result set can be quite big.
I mentioned back in Chapter 3: Introducing PHP that the while loop is a useful control structure for dealing with large amounts of data. Here’s an outline of the code that will process the rows in a result set one at a time:
while ($row = mysqli_fetch_array($result))
{
// process the row…
}
The condition for the while loop is probably different to the conditions you’re used to, so let me explain how it works. Consider the condition as a statement all by itself:
$row = mysqli_fetch_array($result);
The mysqli_fetch_array function accepts a result set as a parameter (stored in the $result variable in this case), and returns the next row in the result set as an array (we discussed arrays in Chapter 3). When there are no more rows in the result set, mysqli_fetch_array instead returns false.
Now, the above statement assigns a value to the $row variable, but, at the same time, the statement as a whole takes on that same value. This is what lets you use the statement as a condition in the while loop. Since a while loop will keep looping until its condition evaluates to false, this loop will occur as many times as there are rows in the result set, with $row taking on the value of the next row each time the loop executes. All that’s left to figure out is how to retrieve the values out of the $row variable each time the loop runs.
Rows of a result set returned by mysqli_fetch_array are represented as associative arrays. The indices are named after the table columns in the result set. If $row is a row in our result set, then $row['joketext'] is the value in the joketext column of that row.
Our goal in this code is to store away the text of all the jokes so we can display them in a PHP template. The best way to do this is to store each joke as a new item in an array, $jokes:
while ($row = mysqli_fetch_array($result))
{
$jokes[] = $row['joketext'];
}
With the jokes pulled out of the database, we can now pass them along to a PHP template (jokes.html.php) for display.
To summarize, here’s the complete code of the controller for this example:
<?php
$link = mysqli_connect('localhost', 'root', 'password');
if (!$link)
{
$error = 'Unable to connect to the database server.';
include 'error.html.php';
exit();
}
if (!mysqli_set_charset($link, 'utf8'))
{
$output = 'Unable to set database connection encoding.';
include 'output.html.php';
exit();
}
if (!mysqli_select_db($link, 'ijdb'))
{
$error = 'Unable to locate the joke database.';
include 'error.html.php';
exit();
}
$result = mysqli_query($link, 'SELECT joketext FROM joke');
if (!$result)
{
$error = 'Error fetching jokes: ' . mysqli_error($link);
include 'error.html.php';
exit();
}
while ($row = mysqli_fetch_array($result))
{
$jokes[] = $row['joketext'];
}
include 'jokes.html.php';
?>
All that’s left to complete this example is to write the jokes.html.php template.
In this template, for the first time we need to display the contents of an array, rather than just a simple variable. The most common way to process an array in PHP is to use a loop. We have already seen while loops and for loops; another type of loop, which is particularly helpful for processing arrays, is the foreach loop:
foreach (array as $item)
{
// process each $item
}
Instead of a condition, the parentheses at the top of a foreach loop contain an array, followed by the keyword as, and then the name of a new variable that will be used to store each item of the array in turn. The body of the loop is then executed once for each item in the array; each time, that item is stored in the specified variable so that the code can access it directly.
It’s common to use a foreach loop in a PHP template to display each item of an array in turn. Here’s how this might look for our $jokes array:
<?php
foreach ($jokes as $joke)
{
?>
<!-- Code to output each $joke -->
<?php
}
?>
With this blend of PHP code to describe the loop and HTML code to display it, this code looks rather untidy. Because of this, it’s common to use an alternative way of writing the foreach loop when it’s used in a template:
foreach (array as $item):
// process each $item
endforeach;
Here’s how this form of the code looks in a template:
<?php foreach ($jokes as $joke): ?>
<!-- Code to output each $joke -->
<?php endforeach; ?>
With this new tool in hand, we can write our template to display the list of jokes:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<title>List of Jokes</title>
<meta http-equiv="content-type"
content="text/html; charset=utf-8"/>
</head>
<body>
<p>Here are all the jokes in the database:</p>
<?php foreach ($jokes as $joke): ?>
<blockquote><p>
<?php echo htmlspecialchars($joke, ENT_QUOTES, 'UTF-8'); ?>
</p></blockquote>
<?php endforeach; ?>
</body>
</html>
Each joke is displayed in a paragraph (<p>) contained within a block quote (<blockquote>), since we’re effectively quoting the author of each joke in this page.
Because jokes might conceivably contain characters that could be interpreted as HTML code (for example, <, >, or &), we must use htmlspecialchars to ensure that these are translated into HTML character entities (that is, <, >, and &) so that they’re displayed correctly.
This figure shows what this page looks like once you’ve added a couple of jokes to the database.
