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 Hour 1, "Installing and Configuring MySQL," and Hour 3, "Installing and Configuring PHP," you should already have taken care of this.

In all sample scripts in this hour, 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.

graphics/bulb.gif

You can find the section of the PHP Manual that covers all MySQL-related functions at http://www.php.net/manual/en/ref.mysql.php. Use it!


Using mysql_connect()

The mysql_connect() function is the first function you must call when utilizing a PHP script to connect to MySQL without 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 8.1 is a working example of a connection script. It assigns the value of the connection index to a variable called $conn, then prints the value of $conn as proof of a connection.

Listing 8.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 the following in your Web browser:

 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 8.2.

Listing 8.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, this 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(), then use mysql_select_db(), as shown in Listing 8.3.

Listing 8.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 8.4 creates a simple table called testTable.

Listing 8.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: ?> 

graphics/book.gif

When issuing queries using mysql_query(), the semicolon at the end of the SQL statement is not required. The only semicolon in that line should be at the end of the PHP command.


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

 1 

The 1 equals true, and indicates that the query was successfully executed. A 0 would have indicated failure. Access MySQL through the command-line interface to verify the creation of the testTable table:

 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) 

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

Retrieving Error Messages

Take some time to familiarize yourself with the mysql_error() function, as it 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 8.5).

Listing 8.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.



Sams Teach Yourself PHP, MySQL and Apache in 24 Hours
Sams Teach Yourself PHP, MySQL and Apache in 24 Hours
ISBN: 067232489X
EAN: 2147483647
Year: 2005
Pages: 263

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net