Working with PHP and MySQL
(Page 1 of 4 )

Last week, you began learning how to use PHP to display and modify data from a MySQL database. This week, you'll learn how to select the database, fetch and display data, and more. This article is excerpted from chapter 9 of Learning PHP and MySQL, written by Michele Davis and Jon Phillips (O'Reilly, 2006; ISBN: 0596101104). Copyright © 2006 O'Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O'Reilly Media.

Now that you're connected, the next step is to select which database to use with the mysql_select_db command. It takes two parameters: the database name and, optionally, the database connection. If you don't specify the database connection, the default is the connection from the last mysql_connect.

  $db_select = mysql_select_db($db_database);
  if (!$db_select){
  die ("Could not select the database: <br />". mysql_error());
  }

Again, it's good practice to check for an error and display it every time you access the database.

While it's possible to call mysql_select_db multiple times within the same script, it's not considered good practice. Generally, you should be able to do all of your work with one database. Maintaining connections and results to multiple databases are beyond this book's scope.

Now that you've got a good database connection, you're ready to execute your SQL query.

Building a SQL query is as easy as setting a variable to the string that is your SQL query. Of course, you'll need to use a valid SQL query, or MySQL returns with an error when you execute the query. The variable name $query is used, but you can choose anything you'd like for a variable name. The SQL query in this example is SELECT * FROM books .

Unlike when you used the mysql command-line client, the query does not have a semicolon at the end.

You can build up your query in parts using the string concatenate (.) operator:

  $select = ' SELECT ';
  $column = ' * ';
  $from = ' FROM ';
  $tables = ' `books` ';
  $where = '';
  $query = $select.$column.$from.$tables.$where;

Which is a more flexible version of this:

  $query = "SELECT * FROM books";

The query string could also use a variable in the WHERE clause to limit which rows are returned based on user information or another query.

Now that you have your query assigned to a variable, you can execute it. 

To have the database execute the query, use the mysql_query function. It takes two parameters--the query and optionally the database link--and returns the result. Save a link to the results in a variable called, you guessed it, $result! This is also a good place to check the return code from mysql_query to make sure that there were no errors in the query string or the database connection by verifying that $result is not FALSE.

  $result = mysql_query( $query );
  if (!$result)
  {
  die ("Could not query the database:
<br />". mysql_error());
  }

When the database executes the query, all of the results form a result set. These correspond to the rows that you saw upon doing a query using the mysql command-line client. To display them, you process each row, one at a time.

Next: Fetching and Displaying >>
 

More MySQL Articles
More By O'Reilly Media

Comments On: Working with PHP and MySQL

· This article is an excerpt from the book "Learning PHP and MySQL," published by...   >>> Post your comment now!

Buy this book...

This article is excerpted from chapter 9 of Learning PHP and MySQL, written by Michele Davis and Jon Phillips (O'Reilly, 2006; ISBN: 0596101104). Check it out today at your favorite bookstore. Buy this book now.