Connecting to MySQL with PHP

To successfully use the PHP functions to talk to MySQL, you must have MySQL running at a location to which your Web server can connect (not necessarily the same machine as your Web server). You also must have created a user (with a password), and you must know the name of the database to which you want to connect. If you followed the instructions in Chapter 2, "Installing and Configuring MySQL," and Chapter 4, "Installing and Configuring PHP," you should already have taken care of this. If you are using PHP and MySQL as part of a hosting package at an Internet Service Provider, be sure you have the correct username, password, and database name from your system administrator before proceeding.

In all sample scripts in this chapter, the sample database name is testDB, the sample user is joeuser, and the sample password is somepass. Substitute your own information when you use these scripts.

Using mysql_connect()

The mysql_connect() function is the first function you must call when utilizing a PHP script to connect to MySQLwithout an open connection to MySQL, you won't get very far! The basic syntax for the connection is

mysql_connect("hostname", "username", "password");

Using actual sample values, the connection function looks like this:

mysql_connect("localhost", "joeuser", "somepass");

This function returns a connection index if the connection is successful or returns false if the connection fails. Listing 17.1 is a working example of a connection script. It assigns the value of the connection index to a variable called $conn, and then prints the value of $conn as proof of a connection.

Listing 17.1. A Simple Connection Script

 1: <?php
 2: $conn = mysql_connect("localhost", "joeuser", "somepass");
 3: echo $conn;
 4: ?>

Save this script as mysqlconnect.php and place it in the document area of your Web server. Access the script with your Web browser and you will see something like this:

Resource id #1

Connecting to MySQL using the mysql_connect() function is pretty straightforward. The connection closes when the script finishes its execution, but if you would like to explicitly close the connection, simply add the mysql_close() function at the end of the script, as in Listing 17.2.

Listing 17.2. The Modified Simple Connection Script

1: <?php
2: $conn = mysql_connect("localhost", "joeuser", "somepass");
3: echo $conn;
4: mysql_close($conn);
5: ?>

That's all there is to it. The next section will cover the query execution functions, which are far more interesting than simply opening a connection and letting it sit there!

Executing Queries

Half the battle in executing MySQL queries using PHP is knowing how to write the SQL. The mysql_query() function in PHP is used to send your SQL query to MySQL. If it does so successfully, a result index is returned. If a failure occurs, the function returns false.

When you use the mysql_query() function, you'll notice that one piece of the puzzle is missing: picking the database to use. When you connect to MySQL through the command-line interface, the database is specified in the connection string or changed manually after you log in. With PHP, the database selection is done via a separate function called mysql_select_db() with the following syntax:

mysql_select_db(database name, connection index);

To connect to a database named testDB, first use mysql_connect(), and then use mysql_select_db(), as shown in Listing 17.3.

Listing 17.3. Connecting and Selecting a Database

1: <?php
2: $conn = mysql_connect("localhost", "joeuser", "somepass");
3: mysql_select_db("testDB",$conn);
4: ?>

You now have two important pieces of information: the connection index ($conn) and the knowledge that PHP will use testDB as the database throughout the life of this particular script. The connection index is used in mysql_query() syntax:

mysql_query(query, connection index);

In your script, first make the connection, and then execute a query. The script in Listing 17.4 creates a simple table called testTable.

Listing 17.4. A Script to Create a Table

 1: <?php
 2: // open the connection
 3: $conn = mysql_connect("localhost", "joeuser", "somepass");
 4: // pick the database to use
 5: mysql_select_db("testDB",$conn);
 6: // create the SQL statement
 7: $sql = "CREATE TABLE testTable (id int not null primary key auto_increment,
 8: testField varchar (75))";
 9: // execute the SQL statement
10: $result = mysql_query($sql, $conn);
11: // echo the result identifier
12: echo $result;
13: ?>

Because the mysql_query function only returns a true or false result, the boring output of this script is

1

The 1 indicates TRue, and indicates that the query was successfully executed. A 0 would have indicated failure. If you access MySQL through the command-line interface, to verify the creation of the testTable table, you will see:

mysql> describe testTable;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     |      | PRI | NULL    | auto_increment |
| testField | varchar(75) | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

Congratulationsyou have successfully created a table in your MySQL database using PHP!

Retrieving Error Messages

Take some time to familiarize yourself with the mysql_error() functionit will become your friend. When used in conjunction with the PHP die() function, which simply exits the script at the point at which it appears, the mysql_error() function will return a helpful error message when you make a mistake.

For example, now that you have created a table called testTable, you won't be able to execute that script again without an error. Let's try to execute the script again, but modify it first to utilize the mysql_error() function (see Listing 17.5).

Listing 17.5. The Script to Create a Table, with Error Messages

 1: <?php
 2: // open the connection
 3: $conn = mysql_connect("localhost", "joeuser", "somepass");
 4: // pick the database to use
 5: mysql_select_db("testDB",$conn);
 6: // create the SQL statement
 7: $sql = "CREATE TABLE testTable (id int not null primary key auto_increment,
 8: testField varchar (75))";
 9: // execute the SQL statement
10: $result = mysql_query($sql, $conn) or die(mysql_error());
11: // echo the result identifier
12: echo $result;
13: ?>

When you execute the script, you should see something like the following in your Web browser:

Table 'testTable' already exists

How exciting! Move on to the next section to start inserting data into your table, and soon you'll be retrieving and formatting it via PHP.