Database Functions-PHP

• 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:

1. Connecting to the database server.
2. Selecting the proper database.
3. Querying the database to insert, read, or delete data.
4. 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.

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.

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

or

mssql_select_db

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.