Using MySQL Database in PHP

The first step in a database interaction is connecting to the database. You use a PHP function to connect to the database. To make the connection, you need to supply the function with four things:

Location: The database does not need to be on the same computer where PHP is installed. Therefore, you need to tell the PHP connect function the name of the computer where the database is located (the hostname). You can supply either a domain name (such as mycompany.com) or an IP address (such as 172.17.204.2). If the database is on the same computer as PHP, you can use localhost for the hostname.

Account name: You must provide a valid account name that can be used to access the database. The database administrator sets this up. If you’re using a Web hosting company, you will be given a valid account name.

Password: You have to have a valid password to access the database. The database administrator sets this up. If you’re using a Web hosting company, you will be given a valid password for your account.

Database name: An RDBMS can create and maintain many databases, so you need to tell it which database you want to use. For security reasons, it’s best to keep your database connection information in a separate file and use it in your PHP script with an include statement.

As long as your include files are stored in a secure location, your information is more secure than if it were stored in the PHP script itself. For the following examples, I create an include file called info.inc with the following statements:

$host = “localhost”;
$account = “admin”;
$password = “secret”;
$dbname = “Catalog”;

Then I can include this file in any PHP script that needs to access the database by using the following statement:

include(“info.inc”);

The PHP function to connect to the database software is not the same for all RDBMSs. That would be too simple. However, although the functions may differ for different databases, the form is similar. For example, the most popular software for Web site database applications is MySQL. For MySQL 4.0 or earlier you use two statements as follows, to connect to the database:

$connect = mysql_connect($host,$account,$password);
$db = mysql_select_db(“Catalog”,$connect);

The first statement connects to the database management software and the second statement tells it which database you want to access. For MySQL 4.1 or later, the format is slightly different, as follows:

$connect = mysqli_connect($host,$account,$password);
$db = mysqli_select_db($connect,"Catalog");

Notice that the order of the items passed in the function is reversed for the second line. Mysql passes ("Catalog",$connect) and mysqli passes ($connect,"Catalog"). Several databases require two separate functions to connect to the database, as shown for MySQL above.

Sending a query to the database

After PHP has established a connection to the database, you can perform whatever action you desire, such as get data, change data, or insert new data. The SQL query tells the database what action you want to perform. You send the SQL query to the by using another PHP function for your RDBMS. Again, the format of these statements varies, but they are similar. For example, for MySQL, you can send the query by using the following statements:

$sql = “SELECT * FROM Product”;
$result = mysql_query($sql,$connect);

The first statement stores the SQL query in a variable $sql. The SQL statement gets all the data in the table Product. The mysql_query function sends the query in $sql to the database over the database connection established earlier and stored in $connect. The data is stored in a temporary table l, with rows and columns, and $result (or whatever you chose to call this variable) contains a pointer to the temporary table.

The query in above example returns data, but queries that don’t return data are sent with the same function, as in the following example:

$first_name = “John”;
$last_name = “Smith”;
$sql = “INSERT INTO Customer (firstName,lastName) VALUES (‘$first_name’,’$last_name’)”;
$result = mysql_query($sql);

When no data is returned by the query, $result contains TRUE, rather than a pointer to the retrieved data.

Processing data

If you send a query that retrieves data, you undoubtedly intend to use that data in your PHP script. You may want to display a list of check boxes based on data taken from the database, display data in a Web page so users can edit it, use the data from the database as default text in an HTML form, and so on.

To process the returned data, you need to get it from the temporary table where it was placed when the SQL query was executed. You use PHP database functions to get the data from the temporary table.

The data is stored in the temporary table in rows and columns. You can use PHP functions to retrieve one row from the table and store it in an array, with the field names as the array keys. For MySQL, the statement is as follows:

$row = mysql_fetch_array($result);

In the previous section, we saved the results and stored the location in a variable named $result. In this statement, we tell PHP which results to fetch by using $result. The mysql_fetch_array returns one row of data from the temporary table specified by $result.

After this statement, $row is an array containing all the fields in the temporary table, such as the following:

$row[‘firstName’] = John
$row[‘lastName’] = Smith

To process all the data in the temporary table, you can use a loop to get one row at a time, processing each row until the end of the table is reached.

Closing the connection

Any open database connections are closed when the script ends. However, it is good programming practice to close the connections in the script, to avoid any possible problems. You close database connections the same way you open them - with a PHP function. For example, for MySQL, use the following function to close a database connection:

mysql_close($connect);