- Advanced Database Interaction in PHP4
- Database-Specific Functions in PHP
- DBX—PHP Support for Multiple Databases
- Creating Your Own Support for Multiple Databases
- Using Pear::DB
This chapter is from the book
This chapter is from the book
This chapter is from the book
Database-Specific Functions in PHP
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)
or
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.