Use PHP for database access and deliver dynamic Web content
In this article, I'll explain how you can use PHP to connect to a database and retrieve information from it. Then, I'll show you a sample Web portal page that uses a MySQL database for dynamic content generation. For our purposes here, I'll assume that you know and understand the basic syntax of your database.
Database support
PHP offers database support in a couple of ways. First, it provides native support for a number of databases. Below, I've listed some of the more popular ones and provided links to the appropriate function pages in the PHP manual.
Second, in case your database isn't natively supported, you can connect via protocol-based functions. Currently, these include:
To provide functionality for variable database sources within a single system, PHP also offers the experimental module dbx. This set of functions allows you to connect to any database that is supported by your PHP configuration. Generally, experimental modules have been released for developer testing but either have not been released in their final iteration or are still in the development phase.
Connecting to a database
To connect to a database in PHP, you use the connect function from the module that is specific to your database brand. For example, to connect to a MySQL database located on your Web server, you would use this command:
mysql_connect("localhost","username","password")
Since this function returns an identifier, you should associate it with a variable when called, as in the example script below.
Once you've opened a database in this manner, all other functions from the supporting module are available to manipulate the data. If you try to connect to the database again with the same parameters, the existing identifier is returned without opening a new connection.
When you have finished with the database, you can close it using the close function, like this:
mysql_close($database_identifier)
Alternatively, a connection created with a connect function will terminate when the script ends.
While specific connectivity functions will depend on the type of database you are using, for databases that support it, PHP offers the pconnect function. This function allows you to open a persistent connection to your data source. This means that once you have opened a connection, it will remain open, even after the script has ended or you issue a close function. After you issue the pconnect function, you can use query and data handling functions at any time without opening a new connection. Because of this persistence, pconnect can be especially useful when you're using a database for dynamic page generation.
Function types
Typically, three types of PHP functions are available with any database module:
The first type includes functions such as close and change_user.
These functions depend on data contained in the link identifier that was created
when connecting to the database to perform database-level commands.
The
second type consists of functions that use internal database capabilities, such
as error, numfields, and get_server_info, as well as the
important query function. These functions are the most dependant upon
database type and represent data space level features.
The third function
type, and probably the most used of all PHP database commands, are those to be
performed on your query results. These handle output data for each type of
database and include functions such as fetch_array and num_rows.
Returning data
Once you've connected to your database, the functions available will depend on the brand of database. But regardless of database type, all returned data must be parsed in order to be used in a meaningful way within your PHP script.
For example, let's say I've performed the following query on my MySQL database:
$result = mysql_query("SELECT * FROM
userprefs
WHERE favcolor =
'blue'");
In this query, userprefs is the name of my table, and favcolor and name are fields. For the sake of our example, the database contains five entries where favcolor = blue. The proper way to retrieve and display this information, would be:
while ($row = mysql_fetch_array($result)) {
echo $row[name]." likes
".$row[favcolor].".<br>";
}
The while statement steps through each returned row of the data set, and the echo statement displays it to the screen. The output from this would be:
Adam likes blue.
Brent likes blue.
Carl likes blue.
Derek likes blue.
Eric likes blue.
If you wanted to perform a query other than SELECT (UPDATE, DELETE, INSERT, etc.), you would still use the query function for your database. Also, many other commands are available for controlling how data is returned, such as fetch_object, fetch_field, and fetch_row. Refer to the PHP Function Reference section of the PHP Web site for your database to learn more about these functions and when it is appropriate to use each one.
Sample script: portal.php
Now that we know how to connect to a database and retrieve information from it,
let's put theory into action. The sample script in Listing A checks a MySQL database for user preferences
and includes the appropriate page settings, creating database-driven dynamic
content. It then displays some of the user's information on the Web page,
creating database-driven dynamic data. Be sure to take a look at the end result,
posted here,
for reference while reviewing the code. Note that while I have commented the
code where appropriate, I've omitted much of the explanation of this script. If
you have questions about PHP and databases, dynamic content, or the portal.php
script, please post them in the discussion area below.
For this example,
I've created a database with the structure shown in Figure A.
Example database structure
Summary
In this article, I explained how to use PHP to connect to a database and retrieve information from it. The sample script, portal.php, offered an example of how you can use a database to create dynamic content and data.
My final installment in this series on dynamic Web content will discuss how you can optimize dynamic information by using PHP session objects, along with other good PHP coding practices.