PHP/Databases

Database Selection

As with any web scripting language, PHP would not be complete without the capacity to communicate directly to hosted database for data storage and retrieval. This lesson will discuss the basics in that communication. While there are many database options, we will focus strictly on MySQL as the database of choice.

Prerequisites

• This article assumes that you have a basic understanding of databases and their usage.
• This article assumes that you have a MySQL database to which you have access to connect and query.
• If you would like to set up a testing environment to test this or any other PHP lessons, you may do so by installing WAMP or XAMPP.

Making a Connection

Since we are using MySQL for this introduction, let me first direct your attention to the list of MySQL functions in the PHP manual. This page will give you the details about options, settings, versions and other information regarding the usage of MySQL that we just won't take the time to cover at this time.

Function: mysql_connect()

As the function name suggests, this function will create a live database connection through which we may communicate. Once this connection is made, sending, retrieving and manipulation of data is possible. This function allows for up to five optional parameters:

• Database Server (defaults to localhost:3306)
• Database Username
• Database Password
• New Link (if TRUE, this forces a new connection to be established instead of replacing the existing one)
• Client Flags

Typically, the average user will not use the Client Flags option, but if you wish to read up on the flags yourself.

For the most part, a MySQL database connection should look very similar to the following:

<?php
$host = "mysql.mydomain.com";
$user = "root";
$pass = "abc123";

$conn = mysql_connect($host, $user, $pass);
?>

If the database connection is not completed, the function returns a boolean FALSE, otherwise, the connection resource is returned to be used. So, we must always validate that our database connection is successful before going on with our scripts to avoid any unnecessary headaches in debugging down the road:

<?php
if (($conn = mysql_connect) === FALSE) {
  // Failed to connect!
  // Handle your error here...
}
?>

Function: mysql_select_db()

Once the connection to the database server has been successfully made, you must determine the database to which you wish to send your queries. The mysql_select_db() function is for just this purpose. By passing in two parameters—one to declare which connection resource you wish to use and one to declare which database to which you wish to connect—you can select the database and all subsequent queries will be sent to that database:

<?php
$conn = mysql_connect($host, $user, $pass);
if ($conn !== FALSE) {
  // Successful connection to host
  if (mysql_select_db('myDatabaseName', $conn)) {
    // We have successfully selected our database
  }
}
?>

You may have noticed that in the example above, the select db function was called within an if statement. This is because mysql_select_db() returns a boolean TRUE or FALSE upon a successful or failed attempt to carry out your request. In this way, if you check both your initial host connection and your selection process, you will be able to run your queries with confidence that your database connection is solid.

Querying the Database

Simply connecting to a database is pretty useless in and of itself. Without being able to send and retrieve information from the database, there is no point to connecting. This is where one of the most used—if not the most used-function in the mysql set comes in.

Function mysql_query()

Once again, as the name suggests, this function simply passes our query along and processes it via the mysql connection we have created. The function receives one required and one optional parameter. The required parameter is actually our text for our SQL query. Secondly, the optional parameter allows us to declare which connection resource through which to run the query. This allows one to have multiple database connections running in a single script and to keep the queries straight. For simplicity's sake, we will simply assume there is only one connection in our examples. This is the most common type of script you may come across in your studies:

<?php
if (($conn = mysql_connect($host, $user, $pass)) === FALSE) {
  // Database host connection error encountered
} elseif (!mysql_select_db($dbname, $conn)) {
  // Database select error encountered
}
  $q   = "SELECT * FROM myTableName";
  $sql = mysql_query($q);
}
?>

As we have seen with the mysql_connect() function, the return value of this function is a resource. In and of itself, it is fairly useless, so we need to read on to the next section to see how we deal with parsing and handling data returned from the database queries.

Function mysql_insert_id()

One of the most useful functions you may find if you are inserting information into database tables is the mysql_insert_id() function. This function may be called immediately following an insert query processed with mysql_query(), and it will return the AUTO_INCREMENT value that was generated by the query that was just completed. This is especially useful if you are inserting related data into multiple tables and you need to keep your constraints in check.

Handling Database Query Resources

Once you have successfully queried your database and gotten a returned resource, you must do something with it in order to extract the data. One of the most common errors I see when dealing with new PHP developers is the assumption that your data is returned in the result from mysql_query() in some way. Consider the following:

<?php
$sql = mysql_query("SELECT * FROM myTableName");
echo $sql; // Outputs "Resource Id #1"
?>

Notice that by outputting the result of our query, you simply get a Resource Id, not any usable data. It is this resource from which we can now extract our information. The most common way to extract data from the resource is to iterate through the resource and return the data one record set at a time. There are a number of functions that are at your disposal to do this, but we will focus on mysql_fetch_assoc() in this lesson, since it returns a nice associative array that is easy to work with.

Function mysql_fetch_assoc()

This function will return the next row in our result set. Once there are no records left to be returned, the function returns a boolean FALSE. With this knowledge, we can quite easily set up a loop to gather all of our returned records. Let's assume for a moment we have created a table named userList using the following SQL statement:

CREATE TABLE userList (
  `id` INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `username` VARCHAR(20) UNIQUE,
  `password` VARCHAR(50),
  `email` VARCHAR(100) UNIQUE
);

Let's look at how we might be able to query this table and retrieve all usernames and their corresponding email addresses to be displayed in a table in your script:

<?php
$sql = mysql_query("SELECT * FROM userList ORDER BY username");

// Remember that FALSE will be returned when there are no more rows!
while ($row = mysql_fetch_assoc($sql)) {
  $username = $row['username'];
  $email    = $row['email'];
  echo "{$username} - <a href=\"mailto:{$email}\">{$email}</a><br>\n";
}
?>

This simple script is all we need to do to gather all of our users from the userList table and print them to the screen. You'll notice that I added an anchor tag around the email addresses to actually hyperlink them with the mailto protocol. This is a good way to make a very simple contact list and allow for easy access to everyone's email addresses.

Now, what if I were looking for the email address of a specific username? I don't necessarily want to have to loop through the entire table and return the one record that matches. For a case when there is a very specific value you wish to return, you can use the next function.

Function mysql_result()

The mysql_result() function is an interesting animal in some respects because it allows you to select the actual record and column of a specific value to return. This is most useful for things like counting the number of records for a certain filter or other query that is likely to return exactly one row. So, to take into account the example above, we could find the email address for a specific username quite easily with this function rather than looping through the entire table:

<?php
$username = 'obsidian';
$sql = mysql_query("SELECT `email` FROM userList WHERE username = '{$username}'");

// Check to make sure we have exactly one record returned
if (mysql_num_rows($sql) == 1) {
  $email = mysql_result($sql, 0, 'email');
  echo "Email address = {$email}";
} else {
  echo "No matching username found.";
}
?>

Notice how we pass three parameters to the function call. The first is the Resource Id as you would expect. Then, we have two optional parameters we can pass to the function: the row number of the result set to be returned and the column of the data to be retrieved. If either of these are left out, the default value is set to 0 or the first row and column. So, since our query was so defined, we could have actually called our mysql_result() like this, with the same result:

$email = mysql_result($sql);

Conclusion

While many of the very basic principles of database communication through PHP were touched on in this lesson, we have barely scratched the surface of what is available at your fingertips. In addition to simply querying the database, whether that is to insert or retrieve information, there is a world of other knowledge that must be gleaned to have a solid and working understanding of PHP database integration. While querying the database yourself is very helpful, you will undoubtedly get to the point where you wish to query based on user input and give your visitors a more personalized experience. This type of database interaction unveils a whole new level of security issues that must be dealt with... but that will have to be another lesson.

In the meantime, do a little research. Decide which database best fits your needs. Practice the different querying and data retrieval techniques. Research the PHP manual to get a list of other functions related to your database of choice. These few steps will get you well on your way to learning to properly use your PHP database communication.

Ghenson 20:31, 4 June 2007 (UTC)