Publishing MySQL Data on the Web - Sending SQL Queries with PHP

[sivaji]

Sending SQL Queries with PHP

In Chapter 2, 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 mysql_query function.
mysql_query(query[, connection_id])

Here query is a string that contains the SQL command we want to execute. As with mysql_select_db, the connection identifier parameter is optional. What this function returns will depend on the type of query being sent. For most SQL commands, mysql_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:

$sql = 'CREATE TABLE joke (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
joketext TEXT,
jokedate DATE NOT NULL
)';
if (@mysql_query($sql)) {
echo '<p>joke table successfully created!</p>';
} else {
exit('<p>Error creating joke table: ' .
mysql_error() . '</p>');
}

Again, we use the @ trick to suppress any error messages produced by mysql_query, and instead print out a friendlier error message of our own. The mysql_error function used here returns a string of text that describes the last error message that was sent by the MySQL server.

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 to set the dates of all jokes that contained the word "chicken":

$sql = "UPDATE joke SET jokedate='1994-04-01'
WHERE joketext LIKE '%chicken%'";

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:

if (@mysql_query($sql)) {
echo '<p>Update affected ' . mysql_affected_rows() .
' rows.</p>';
} else {
exit('<p>Error performing update: ' . mysql_error() .
'</p>');
}

SELECT queries are treated a little differently, as they can retrieve a lot of data, and PHP must provide ways to handle that information. Handling SELECT Result Sets For most SQL queries, the mysql_query function returns either true (success) or false (failure). For SELECT queries, this just isn't enough. 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, mysql_query returns a number that identifies 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 = @mysql_query('SELECT JokeText FROM Jokes');
if (!$result) {
exit('<p>Error performing query: ' . mysql_error() .
'</p>');
}

Provided that no error was encountered in processing the query, the above code will place a number into the variable $result. This number corresponds to a result set that 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 pretty big.

We mentioned before 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 = mysql_fetch_array($result)) {
// process the row...
}

The condition for the while loop probably doesn't resemble the conditions you're used to, so let me explain how it works. Consider the condition as a statement all by itself:

$row = mysql_fetch_array($result);

The mysql_fetch_array function accepts a result set number as a parameter (stored in the $result variable in this case), and returns the next row in the result set as an array (see Chapter 3 for a discussion of arrays). When there are no more rows in the result set, mysql_fetch_array instead returns false.

Now, the above statement assigns a value to the $row variable, but, at the same time, the whole statement itself 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 get the values out of the $row variable each time the loop runs.

Handling SELECT Result Sets

Rows of a result set returned by mysql_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. So here's what our while loop should look like if we want to print the text of all the jokes in our database:

while ($row = mysql_fetch_array($result)) {
echo '<p>' . $row['joketext'] . '</p>';
}

To summarize, here's the complete code of a PHP Web page that will connect to our database, fetch the text of all the jokes in the database, and display them in HTML paragraphs:

File: jokelist.php

<!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">
<head>
<title>Our List of Jokes</title>
<meta http-equiv="content-type"
content="text/html; charset=iso-8859-1" />
</head>
<body>
<?php
// Connect to the database server
$dbcnx = @mysql_connect('localhost', 'root', 'mypasswd');
if (!$dbcnx) {
exit('<p>Unable to connect to the ' .
'database server at this time.</p>');
}
// Select the jokes database
if (!@mysql_select_db('ijdb')) {
exit('<p>Unable to locate the joke ' .
'database at this time.</p>');
}
?>
<p>Here are all the jokes in our database:</p>

<?php
// Request the text of all the jokes
$result = @mysql_query('SELECT joketext FROM joke');
if (!$result) {
exit('<p>Error performing query: ' . mysql_error() . '</p>');
}
// Display the text of each joke in a paragraph
while ($row = mysql_fetch_array($result)) {
echo '<p>' . $row['joketext'] . '</p>';
}
?>

</body>
</html>