Archive:PHP and MySQL Programming/Database Connectivity

TOC

Introduction

You can only do so much with storing information in files. When you need to store large amounts of data, and perform intensive number crunching on that data, there is nothing better than a good SQL database. In this section, we'll discuss connecting PHP to a MySQL database and perform queries and retrieve data back fom the database.

Opening a Connection to a MySQL Database

The first thing that we need to do before we can interact with any database, is to open up a connection to that database server. This is done by using the mysql_connect() function, which returns a database handler, much like a file handler when dealing with files. The database handler is then used to selecting the active database to use.

Here is the code to setup a connection to the database server, and to select a database to use :

<?
$db = "database1";
$link = mysql_connect("localhost", "username", "password");
mysql_select_db($db, $link);
?>

Creating a Query

Once a connection to a database has been made, you will inevetably want to perform a database query. To create a query on the selected database, we use the mysql_query() function. If you use a SELECT query, then the data returned from that query will be passed to the mysql_query() function, which will in turn return it to a variable which you can specify. In the following example, two queries are made, the first does not return any data, and the second does.

<?
// A Query without any returned data
mysql_query ("INSERT INTO `table1` ('val1', 'val2')");
// A Query with returned data
$query = mysql_query("SELECT * FROM `table1`");
?>

Closing a Database Connection

It is not always necessary to close a connection when you are finished, but it is advised. It is, however, necessary to close the connection to the database if you want to open up a new connection to a different database.

To close a connection to a database, we use the mysql_close() function, as follows :

<?
mysql_close();
?>

Error Handling

It is useful when debugging, and even when you just want to make sure that a database does not behave unexpectedly. Once a query has been created via the mysql_query() function, any error messages generated will be stored in the mysql_error() function. Here is a sample code snippet to display a error message. However, when there is no error messages, a blank string is returned.

<?
print mysql_error();
?>

<< 3. File Handling | TOC | >> 6. Session Handling