Establishing a Database Connection with PHP | PHP Essentials, 2nd Edition

Using the built-in database connectivity functions in PHP, you can connect to virtually any database type and access its data (if you have the proper permissions, of course!). If PHP does not contain specific functions for your particular database type, you can make generic ODBC connections using PHP's ODBC functions. Before trying to use any of the database connectivity functions, be sure that you have a database installed and the proper extensions were compiled into PHP or loaded through your php.ini file.

In the next several pages, you'll find function definitions and code samples for the more popular database types. The PHP Manual at http://www.php.net/manual/ is the first place you should look to find a complete list of all the functions for supported databases, not just those listed in this chapter. After searching the manual and the FAQ, search the PHP Mailing List Archives. Chances are good that if you have a database connectivity question, someone else has asked it and a developer has answered it. If not, asking a well-phrased question will undoubtedly elicit numerous responses.

The following sections are by no means all there is to know about connecting to databases with PHP. Instead, these sections detail the basic elements of database connectivity:

Connect to a server
Select a database
Query
View results

Additionally, the code samples provided in the next sections are not the only ways to make a simple connection and print results. Some of the examples are more verbose than they really need to be, but they give you a solid foundation for understanding the steps that follow. When you become an expert with your database type, you can find your own ways to optimize your code, eliminating extra steps and some processing time and resource overhead.

Connecting to a MySQL Database

The MySQL database is one of the most popular among PHP developers. It's my database of choice, as well as the database used in the examples found in this book. Understandably, there are numerous well-documented PHP functions you can use in conjunction with your MySQL databases; see the PHP Manual for a complete list. However, you need only a few of these functions in order to make a simple connection and select some data:

mysql_connect(). Opens a connection to the MySQL server. Requires a hostname, username, and password.
mysql_select_db(). Selects a database on the MySQL server.
mysql_query(). Issues the SQL statement.
mysql_fetch_array(). Puts an SQL statement result row in an array.
mysql_result(). Gets single element result data from a successful query.
mysql_error(). Returns a meaningful error message from MySQL.

First, you must know the name of the server on which the database resides, as well as the valid username and password for that server. In this example, the database name is MyDB on localhost, your username is joeuser, and your password is 34Nhjp. Start your PHP code by creating a connection variable:

 $conn = mysql_connect("localhost","joeuser","34Nhjp") or die(mysql_error());

The die() function is used in conjunction with the mysql_error() function, to print an error message and exit a script when the function cannot perform as required. In this case, die() would execute if the connection failed. The error message would be printed so that you'll know where the error occurred, and no further actions would take place. Using die() properly will alleviate many headaches as you attempt to debug your code.

If you make it through the connection step, the next step is to select the database and issue the SQL statement. Suppose that the FRIEND_INFO table, used in previous examples, exists in a MySQL database called MyDB. Create a database variable like this one:

 $db = mysql_select_db("MyDB", $conn) or die(mysql_error());

Up to this point, you've told PHP to connect to a server and select a database. If you've made it this far, you can issue a SQL statement and hopefully see some results!

Suppose you want use the FRIEND_INFO table to view your friends' names, birthdays, and favorite colors, ordered from oldest to youngest friend. Create a variable that holds your SQL statement:

 $sql = "SELECT FULL_NAME, BIRTH_DATE, FAVE_COLOR FROM FRIEND_INFO ORDER BY BIRTH_DATE ASC";

Next, create a variable to hold the result of the query, carried out by the mysql_query() function. The mysql_query() function takes two arguments: the connection and the SQL statement variables you just created.

 $sql_result = mysql_query($sql,$conn) or die(mysql_error());

To format the results currently held in $sql_result, first separate the results by row, using the mysql_fetch_array() function:

 while ($row = mysql_fetch_array($sql_result)) {               // more code here... }

The while loop creates an array called $row for each record in the result set. To get the individual elements of the record (FULL_NAME, BIRTH_DATE, FAVE_COLOR), create specific variables:

 $full_name = $row["FULL_NAME"]; $birth_date = $row["BIRTH_DATE"]; $fave_color = $row["FAVE_COLOR"];

Suppose you want to print the results in a simple HTML table. Step back and place this statement before the while loop begins, in order to open the table tag and create the row headings:

 echo "<TABLE BORDER=1> <TR> <TH>Full Name</TH> <TH>Birthday</TH> <TH>Favorite Color</TH> </TR>";

After defining the variables within the while loop, print them in table format:

 echo "<TR> <TD>$full_name</TD> <TD>$birth_date</TD> <TD>$fave_color</TD> </TR>";

The new while loop looks like this:

 while ($row = mysql_fetch_array($sql_result)) { $full_name = $row["FULL_NAME"]; $birth_date = $row["BIRTH_DATE"]; $fave_color = $row["FAVE_COLOR"]; echo "<TR> <TD>$full_name</TD> <TD>$birth_date</TD> <TD>$fave_color</TD> </TR>"; }

After the while loop, close the HTML table:

 echo "</TABLE>";

The full script to perform a simple connection and data selection from a MySQL database could look something like this:

 <?php // create connection; substitute your own information! $conn = mysql_connect("localhost","joeuser","34Nhjp") or die(mysql_error()); // select database; substitute your own database name $db = mysql_select_db("MyDB", $conn) or die(mysql_error());              // create SQL statement $sql = "SELECT FULL_NAME, BIRTH_DATE, FAVE_COLOR FROM FRIEND_INFO ORDER BY BIRTH_DATE ASC"; // execute SQL query and get result $sql_result = mysql_query($sql ,$conn) or die(mysql_error()); // start results formatting echo "<TABLE BORDER=1> <TR> <TH>Full Name</TH> <TH>Birthday</TH> <TH>Favorite Color</TH> </TR>"; // format results by row while ($row = mysql_fetch_array($sql_result)) { $full_name = $row["FULL_NAME"]; $birth_date = $row["BIRTH_DATE"]; $fave_color = $row["FAVE_COLOR"]; echo "<TR> <TD>$full_name</TD> <TD>$birth_date</TD> <TD>$fave_color</TD> </TR>"; } echo "</TABLE>"; ?>

This example didn't use the mysql_result() function because mysql_fetch_array() is more useful for retrieving multiple records. If your SQL query was meant to select only one record, you could have used mysql_result().

For example, say your SQL statement was

 SELECT BIRTH_DATE FROM FRIEND_INFO WHERE ID = 1;

After issuing the query

 $sql_result = mysql_query($sql,$conn) or die(mysql_error());

simply use mysql_result() like so:

 $birthday = mysql_result($sql_result, 0, "BIRTH_DATE") or die(mysql_error());

The first argument used in the function is the resource result of the SQL statement. Next in the list comes the row (starting with number 0), and finally, there's the name of the field you selected.

See the PHP Manual, at http://www.php.net/mysql, for the forty or so additional MySQL functions, and try using your own tables and SQL statements in place of the examples shown here. The basic idea of connecting, querying, and retrieving a result is fundamental to all other actions.

Connecting to a PostgreSQL Database

Like MySQL, the PostgreSQL database is quite popular among PHP developers. Understandably, there are numerous well-documented PHP functions you can use in conjunction with PostgreSQL; see the PHP Manual at http://www.php.net/pgsql for a complete list. However, you need only a few of these functions in order to make a simple connection and select some data:

pg_connect(). Opens a connection to PostgreSQL. Requires a hostname, database name, username, and password.
pg_query(). Executes the SQL statement.
pg_fetch_array(). Puts a SQL statement result row in an array.
pg_fetch_result(). Gets single element result data from a successful query.
pg_result_error(). Returns a meaningful error message from PostgreSQL.

First, you must know the name of the server on which the database resides, as well as a valid username and password for that server. In this example, the database name is MyDB on localhost, your username is joeuser, and your password is 34Nhjp. Start your PHP code by creating a connection variable:

 $conn = pg_connect("host=localhost dbname=MyDB user=joseuser password=34Nhjp") or die("Couldn't make a connection.");

The die() function in this case is used to print an error message and exit a script when the function cannot perform as required. In this case, die() would execute if the connection failed. The error message would be printed so you know where the error occurred ("Couldn't make a connection."), and no further actions would take place. Using die() properly will alleviate many headaches as you attempt to debug your code.

If you make it through the connection test, the next step is to create the SQL statement. Using the FRIEND_INFO table, suppose you want to view your friends' names, birthdays, and favorite colors, ordered from oldest to youngest friend. Create a variable that holds your SQL statement:

 $sql = "SELECT FULL_NAME, BIRTH_DATE, FAVE_COLOR FROM FRIEND_INFO ORDER BY BIRTH_DATE ASC";

Next, create a variable to hold the result of the query, carried out by the pg_query() function. This function takes two arguments: the connection and the SQL statement variables you just created.

 $sql_result = pg_query($conn,$sql) or die(pg_result_error());

In this instance, the die() function works in conjunction with the pg_result_error() function, to produce an actual error from PostgreSQL instead of one you just made up.

Now that you have queried the database, you'll want to format the results currently held in $sql_result. Since you have more than one record to loop through, first separate the results by row, using the pg_fetch_array() function:

 while ($row = pg_fetch_array($sql_result)) {              // more code here... }

The while loop creates an array called $row for each record in the result set. To get the individual elements of the record (FULL_NAME, BIRTH_DATE, FAVE_COLOR), create specific variables:

 $full_name = $row["FULL_NAME"]; $birth_date = $row["BIRTH_DATE"]; $fave_color = $row["FAVE_COLOR"];

Suppose you want to print the results in a simple HTML table. Step back and place the following statement before the while loop begins, in order to open the table tag and create the row headings:

 echo "<TABLE BORDER=1> <TR> <TH>Full Name</TH> <TH>Birthday</TH> <TH>Favorite Color</TH> </TR>";

After defining the variables within the while loop, print them in table format:

 echo "<TR> <TD>$full_name</TD> <TD>$birth_date</TD> <TD>$fave_color</TD> </TR>";

The new while loop looks like this:

 while ($row = pg_fetch_array($sql_result)) { $full_name = $row["FULL_NAME"]; $birth_date = $row["BIRTH_DATE"]; $fave_color = $row["FAVE_COLOR"]; echo "<TR> <TD>$full_name</TD> <TD>$birth_date</TD> <TD>$fave_color</TD> </TR>"; }

After the while loop, close the HTML table:

 echo "</TABLE>";

The full script to perform a simple connection and data selection from a PostgreSQL database could look something like this:

 <?php // create connection; substitute your own info $conn = pg_connect("host=localhost dbname=MyDB user=joeuser password=34Nhjp") or die("Couldn't make a connection."); // create SQL statement $sql = "SELECT FULL_NAME, BIRTH_DATE, FAVE_COLOR FROM FRIEND_INFO ORDER BY BIRTH_DATE ASC"; // execute SQL query and get result $sql_result = pg_query($conn,$sql) or die(pg_result_error()); // start results formatting echo "<TABLE BORDER=1> <TR> <TH>Full Name</TH> <TH>Birthday</TH> <TH>Favorite Color</TH> </TR>"; // format results by row while ($row = pg_fetch_array($sql_result)) { $full_name = $row["FULL_NAME"]; $birth_date = $row["BIRTH_DATE"]; $fave_color = $row["FAVE_COLOR"]; echo "<TR> <TD>$full_name</TD> <TD>$birth_date</TD> <TD>$fave_color</TD> </TR>"; } echo "</TABLE>"; ?>

This example didn't use the pg_fetch_result() function, because pg_fetch_array() is more useful for retrieving multiple records. If your SQL query was meant to select only one record, you could have used pg_fetch_result().

For example, say your SQL statement was

 SELECT BIRTH_DATE FROM FRIEND_INFO WHERE ID = 1;

After issuing the query

 $sql_result = pg_query($sql ,$conn) or die(pg_result_error());

simply use pg_fetch_result() like so:

 $birthday = pg_fetch_result($sql_result, 0, "BIRTH_DATE") or die(pg_result_error());

The first argument used in the function is the resource result of the SQL statement. Next in the list comes the row (starting with number 0) and, finally, there's the name of the field you selected.

See the PHP Manual, at http://www.php.net/pgsql, for the numerous other PostgreSQL functions, and try using your own tables and SQL statements in place of the examples shown here. The basic idea of connecting, querying, and retrieving a result is fundamental to all other actions.

Connecting to an Oracle Database

PHP has numerous functions for connecting to Oracle databases. This is good because if you've spent the money to purchase an Oracle database, you'll want to be able to connect to it using PHP. The PHP Manual has the definitive list of PHP-to-Oracle connectivity functions; however, you need only a few in order to make a simple connection and select some data:

OCILogon(). Opens a connection to Oracle. Requires that the environment variable ORACLE_SID has been set and that you have a valid user-name and password.
OCIParse(). Parses a SQL statement.
OCIExecute(). Executes the SQL statement.
OCIFetchStatement(). Gets all the records as the result of a SQL statement and places them in a results buffer.
OCIFreeStatement(). Frees the resources in use by the current statement.
OCILogoff(). Closes the connection to Oracle.

First, you must have a valid username and password for the database defined by ORACLE_SID. In this example, the username is joeuser and your password is 34Nhjp. Start your PHP code by creating a connection variable:

 $conn = OCILogon("joeuser","34Nhjp") or die("Couldn't logon.");

The die() function is used to print an error message and exit a script when the function cannot perform as required. In this case, die() would execute if the connection failed. The error message would be printed so you know where the error occurred ("Couldn't logon."), and no further actions would take place.

If you make it through the connection test, the next step is to create the SQL statement. Suppose that the FRIEND_INFO table, used in previous examples, exists in your Oracle database. Suppose you want to use the FRIEND_INFO table view your friends' names, birthdays, and favorite colors, ordered from oldest to youngest friend. Create a variable that holds your SQL statement:

 $sql = "SELECT FULL_NAME, BIRTH_DATE, FAVE_COLOR FROM FRIEND_INFO ORDER BY BIRTH_DATE ASC";

Next, use the OCIParse() function to parse the statement in the context of your database connection:

 $sql_statement = OCIParse($conn,$sql) or die("Couldn't parse statement.");

The next step is to execute the statement:

 OCIExecute($sql_statement) or die("Couldn't execute query.");

Now that you have executed the query, you'll want to format the results. As you have more than one record to loop through, first get all the records using the OCIFetchStatement() function:

 $my_records = OCIFetchStatement($sql_statement, $results); if ($my_records > 0) {              // more code here... }

The code within the if statement will execute as long as there are records in the result set, so let's put some code inside the loop!

This code assumes you'll want to format the results in a nice HTML table. First, you'll need to start the table:

 echo "<TABLE BORDER=1> <TR>";

Next, you'll look through the result set (which is an array) to gather the field names:

 while (list($key, $val) = each($results)) { echo "<TH>$key</TH>"; } echo "</TR>";

With your labels printed, you'll want to create the rows for the data. Inside a for statement, you'll reset the $results array and then loop through the result set and print out the data:

 for ($i = 0; $i < $my_records; $i++) {       reset($results);       echo "<TR>";       while($row = each($results)) {          $some_data = $row['value'];          echo "<TD>$some_data[$i]</TD>";       } echo "</TR>"; }

Close up the HTML table and then print a message, if there are no records in your result set:

 echo "</TABLE>"; } else {    echo "No records selected."; }

Finally, you'll want to free up the resources used to perform the query and close the database connection. Failing to do so could cause memory leaks and other nasty resource-hogging events to occur.

 OCIFreeStatement($sql_statement); OCILogoff($conn);

The full script to perform a simple connection and data selection from an Oracle database could look something like this:

 <?php // substitute your own username and password $conn = OCILogon("joeuser","34Nhjp") or die("Couldn't logon."); // create SQL statement $sql = "SELECT FULL_NAME, BIRTH_DATE, FAVE_COLOR FROM FRIEND_INFO ORDER BY BIRTH_DATE ASC"; // parse SQL statement $sql_statement = OCIParse($conn,$sql) or die("Couldn't parse statement."); // execute SQL query OCIExecute($sql_statement) or die("Couldn't execute query."); $my_records = OCIFetchStatement($sql_statement, $results); if ($my_records > 0) {              echo "<TABLE BORDER=1> <TR>"; while (list($key, $val) - each($results)) { echo "<TH>$key</TH>"; } echo "</TR>";                   for ($i = 0; $i < $my_records; $i++) {              reset($results);              echo "<TR>";                   while($row = each($results)) {                           $some_data = $row['value'];                   echo "<TD>$some_data[$i]</TD>"; }              echo "</TR>"; } echo "</TABLE>"; } else {              echo "No records selected."; } // free resources and close connection OCIFreeStatement($sql_statement); OCILogoff($conn); ?>

See the PHP Manual, at http://www.php.net/oci8, for the numerous other Oracle functions, and try using your own tables and SQL statements in place of the examples shown here. The basic idea of connecting, querying, and retrieving a result is fundamental to all other actions used in the code driving dynamic Web applications.

Connecting to Microsoft SQL Server

There are numerous PHP functions for Microsoft SQL Server connectivity, documented in detail in the PHP Manual at http://www.php.net/mssql. However, you need only a few of these functions in order to make a simple connection and select some data:

mssql_connect(). Opens a connection to Microsoft SQL Server. Requires a server name, username, and password.
mssql_select_db(). Selects a database on the Microsoft SQL Server.
mssql_query(). Issues the SQL statement.
mssql_fetch_array(). Puts a SQL statement result row in an array.

First, you must know the name of the server on which the database resides, as well as a valid username and password for that database. In this example, the database name is MyDB on localhost, your username is joeuser, and your password is 34Nhjp. Start your PHP code by creating a connection variable:

 $conn = mssql_connect("localhost","joeuser","34Nhjp") or die("Couldn't connect to the server.");

The die() function is used to print an error message and exit a script when the function cannot perform as required. In this case, die() would execute if the connection failed. The error message would be printed so that you'll know where the error occurred ("Couldn't connect to the server."), and no further actions would take place. Using die() properly will alleviate many headaches as you attempt to debug your code.

If you make it through the connection test, the next step is to select the database and create the SQL statement. Suppose that the FRIEND_INFO table, used in previous examples, exists in a database called MyDB. Create a database variable such as this:

 $db = mssql_select_db("MyDB", $connection) or die("Couldn't select database.");

Up to this point, you've told PHP to connect to a server and select a database. If you've made it this far, you can issue a SQL statement and hopefully see some results!

Suppose you want use the FRIEND_INFO table to view your friends' names, birthdays, and favorite colors, ordered from oldest to youngest friend. Create a variable that holds your SQL statement:

 $sql = "SELECT FULL_NAME, BIRTH_DATE, FAVE_COLOR FROM FRIEND_INFO ORDER BY BIRTH_DATE ASC";

Next, create a variable to hold the result of the query, carried out by the mssql_query() function. The mssql_query() function takes two arguments: the connection and the SQL statement variables you just created.

 $sql_result = mssql_query($sql,$conn) or die("Couldn't execute query.");

To format the results currently held in $sql_result, first separate the results by row, using the mssql_fetch_array() function:

 while ($row = mssql_fetch_array($sql_result)) {              // more code here... }

The while loop creates an array called $row for each record in the result set. To get the individual elements of the record (FULL_NAME, BIRTH_DATE, FAVE_COLOR), create specific variables:

 $full_name = $row["FULL_NAME"]; $birth_date = $row["BIRTH_DATE"]; $fave_color = $row["FAVE_COLOR"];

Suppose you want to print the results in a simple HTML table. Step back and place this statement before the while loop begins in order to open the table tag and create the row headings:

 echo "<TABLE BORDER=1> <TR> <TH>Full Name</TH> <TH>Birthday</TH> <TH>Favorite Color</TH> </TR>";

After defining the variables within the while loop, print them in table format:

 echo "<TR> <TD>$full_name</TD> <TD>$birth_date</TD> <TD>$fave_color</TD> </TR>";

The new while loop looks like this:

 while ($row = mssql_fetch_array($sql_result)) {              $full_name = $row["FULL_NAME"];              $birth_date = $row["BIRTH_DATE"];              $fave_color = $row["FAVE_COLOR"];              echo "<TR>              <TD>$full_name</TD>              <TD>$birth_date</TD>              <TD>$fave_color</TD>              </TR>"; }

After the while loop, close the HTML table:

 echo "</TABLE>";

The full script to perform a simple connection and data selection from Microsoft SQL Server could look something like this:

 <?php // create connection; substitute your own information! $conn = mssql_connect("localhost","joeuser","34Nhjp")              or die("Couldn't connect to the server.") // select database; substitute your own database name $db = mysql_select_db("MyDB", $conn) or die("Couldn't select database."); // create SQL statement $sql = "SELECT FULL_NAME, BIRTH_DATE, FAVE_COLOR FROM FRIEND_INFO ORDER BY BIRTH_DATE ASC"; // execute SQL query and get result $sql_result = mssql_query($sql,$conn) or die("Couldn't execute query."); // start results formatting echo "<TABLE BORDER=1> <TR> <TH>Full Name</TH> <TH>Birthday</TH> <TH>Favorite Color</TH> </TR>"; // format results by row              while ($row = mssql_fetch_array($sql_result)) {              $full_name = $row["FULL_NAME"];              $birth_date = $row["BIRTH_DATE"];              $fave_color = $row["FAVE_COLOR"];              echo "<TR>              <TD>$full_name</TD>              <TD>$birth_date</TD>              <TD>$fave_color</TD>              </TR>"; } echo "</TABLE>"; ?>

This example didn't use the mssql_result() function, because mssql_fetch_array() is more useful for retrieving multiple records. If your SQL query was meant to select only one record, you could have used mssql_result().

For example, say your SQL statement was

 SELECT BIRTH_DATE FROM FRIEND_INFO WHERE ID = 1;

After issuing the query

 $sql_result = mssql_query($sql,$conn) or die("Couldn't execute query.");

simply use mssql_result() like so:

 $birthday = mssql_result($sql_result, 0, "BIRTH_DATE") or die("Couldn't get result.");

The first argument used in the function is the resource result of the SQL statement. Next in the list comes the row (starting with number 0), and finally, there's the name of the field you selected.

See the PHP Manual, at http://www.php.net/mssql, for the numerous additional Microsoft SQL Server functions, and try using your own tables and SQL statements in place of the examples shown here. The basic idea of connecting, querying, and retrieving a result is fundamental to all other actions used in the code driving dynamic Web applications.

Using Other Databases with PHP

If you are using a database not referenced in previous sections, have no fear. PHP has extensions for numerous databases (dBase, Informix, and so on), and can also simply use ODBC connections to communicate with IBM DB2, Sybase, and others. Regardless of the type of database in use, the fundamentals you learned at the beginning of this chapter are still relevant.

If you're using Informix, or simply Microsoft Access, you'll still want to create the most efficient, relational tables possible. With SQL, you know that to retrieve data you use SELECT statements and to insert data you use INSERT statements. All of those rules remain the same in PHP; the only differences are in the names and types of functions used to execute your commands. For that specific information, simply peruse the PHP Manual online, at http://www.php.net/manual/.