Database-Specific Functions in PHP | Advanced PHP for Web Professionals

This section presents some basics in three of the more prominent databases that you can use with PHP. This book assumes that you have some experience with databases and PHP (notably MySQL). This information is included as a brief overview and to point out some of the differences between the functions across different databases. The databases are:

MySQL
PostgreSQL
MS SQL (Microsoft)

Chances are good that you will have at least one of these databases available to you (very good since MySQL and PostgreSQL are available for free download).

There are four basic concepts in PHP for dealing with databases:

Connecting to the database server.
Selecting the proper database.
Querying the database to insert, read, or delete data.
Obtaining the results of your queries to present to the user.

Let's go over these four concepts with the PHP functions used for each database.

Connecting to the Database Server

Before you can do anything in a database-backed application, you need to connect to the database server that contains the actual database that you need to access. For the three databases discussed earlier, this translates into three functions:

mysql_connect to connect to a MySQL Server
mssql_connect to connect to a MS SQL Server
pg_connect to connect to a PostgreSQL Server

mysql_connect() and mssql() connect work identically:

 mysql_connect(SERVER, USER, PASSWORD);

and

 mssql_connect(SERVER, USER, PASSWORD);

In each function, the arguments that need to be defined are:

SERVER The host name or IP address of the host on which the database server is running, for example, "mycompany.com" or "192.168.0.1".
USER The login of the user who has access to the database server, for example, "Joe".
PASSWORD The password of the user.

The connect function for PostgreSQL is much different. pg_connect takes as its argument a single string:

 pg_connect(STRING);

The STRING must contain all of the pertinent information required by your server. The most complete string you could use is:

 pg_connect("host=localhost port=5432 dbname=test  user=username password=password");

It doesn't hurt to use all of the information above, but if the host was localhost, the port was the default port of 5432, and you had no password associated with the user, then you could get by with:

 pg_connect("dbname=test user=username");

Consult your PostgreSQL documentation for your specific implementation. The examples in this chapter assume that you are running your PostgreSQL server on the default port of 5432, so the port is not included in the pg_connect examples.

Selecting the Proper Database

Once you have successfully connected to the database server, you then need to select the database on which you are going to perform your queries.

In the case of PostgreSQL, you have already selected the database in your pg_connect() function, so there is no function to select the database.

However, when using MS SQL or MySQL, you still need to select the database using the respective function:

 mysql_select_db(STRING)

 mssql_select_db(STRING)

In each case, STRING refers to the name of the database to which you are connecting.

Querying the Database to Insert, Read, or Delete Data

Once you've connected to the database server and selected the database, the next logical thing is to do something with the data in the form of a query. Most databases accept any standard SQL command in a query, as well as their own database-specific commands.

The three databases used in the examples all use the same syntax:

mysql_query(STRING);
mssql_query(STRING);
pg_query(STRING);

where STRING is an SQL statement, such as "SELECT name FROM phonebook WHERE name = 'Sarah'."

Obtaining the Results of Your Queries to Present to the User

Once you have made your query, you need to get the result and show it to the user or perform some other processing on it. One of the simplest ways to accomplish this is using the fetch_array functions included in most PHP supported databases.

mysql_fetch_array(RESULT);
mssql_fetch_array(RESULT);
pg_fetch_array(RESULT);

The data from the result is loaded into a field in the array (that is both associative and indexed). Each column in the result is loaded into a field into the array. The keys of the array correspond to the column names of the data returned.

Top