Working with PHP and MySQL

Selecting the Database

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.

>>> More MySQL Articles          >>> More By O'Reilly Media

blog comments powered by Disqus