Please note:

This material is adapted from Build Your Own Database Driven Website Using PHP and MySQL, by Kevin Yank. 3rd Edition, published by Sitepoint Pty. Ltd. ISBN 0-9579218-1-0. The book includes much more information, and you are encouraged to buy it!

Chapter 4 Notes, page 2

Sending MySQL Queries with PHP

In PHP, there is a query function that allows you to send SQL queries and see the results immediately: mysql_query. You would write it like this:

mysql_query(query, connection_id);

The connection_id is optional here, too. "Query" is the string containing the SQL command we want to execute. It will usually return either true or false depending on the result. Here is how you would create a new database:

mysql_query($sql='create table basicinfo (
	ID int not null auto_increment primary key,
	title text,
	author text
	)';
if (@mysql_query($sql)) {
echo ('<p>Table successfully created!<p>');
}
else  {
die('<p>Table not created - mysql error!<p>');

}

Handling SELECT Result Sets

For most queries, the mysql_query function returns "true" or "false," but if you are using SELECT, that is not going to cover many situations. SELECT queries are used to view data stored in the database, so in addition to showing success or failure of the query, PHP must receive the results of the query, which may be extensive. When it processes a SELECT query, mysql_query returns a number that identifies a "result set." This set contains a list of all the rows (entries, records) returned from that query. If the query fails, "false" is still returned. Of course, OUR queries aren't going to fail!

So, let's say that we have entered a bunch of song titles and authors into the database "songs," in the table "basicinfo." We want to see the list of titles and authors. We can use the mysql_query function like this:

$result=@mysql_query('select title, author from basicinfo');
if (!$result) {
die ('<p>Error performing query: ' .mysql_error().'<p>');
}

Here, the mysql_query will look in the table "basicinfo" and select all the rows (records) from the column (field) called "title," and the field called "author." It will return a number that identifies a list of all the records that were returned from the query. The list is contained in a "result set." We are putting the result set into the variable $result. (We have added the "die" message in case the query fails.)

Now we have to get a look at that result set. To do that, we use the function mysql_fetch_array, which accepts as a parameter a result set, and returns the next row in that result set as an array. The rows of the result set are represented as associative arrays, and the indices of the arrays are the column names in the result set. So, if $row is a row in the result set, then $row['title'] is the value in the title column of that row, and $row['author'] is the value in the author column of that row.

We can use a while loop as a control structure for dealing with the result set, which may include large sets of data. Here is an outline of the code to process rows in a result set one at a time. We can also have the rows written to a table, to keep them neat:

echo ('<table> <tr>');
while ($row = mysql_fetch_array($result)){
echo ('<td>' .$row['title'] .'</td><td>' .$row['author'].'</td></tr>');
}
echo ('</table>');

The "mysql_fetch_array" function accepts a result set as a parameter, which in this case is stored in the variable $result. It then returns the next row in the result set as an array. When there are no more rows to process, it returns "false."

Click here.