Home arrow MySQL arrow Working with PHP and MySQL

Working with PHP and MySQL

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.

TABLE OF CONTENTS:
  1. Working with PHP and MySQL
  2. Fetching and Displaying
  3. Putting It All Together
  4. Using PEAR
By: O'Reilly Media
Rating: starstarstarstarstar / 15
May 24, 2007

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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 the SQL SELECT 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. 

Executing the Query

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
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: