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, make sure that 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. By the Way All code in this chapter as well as other chapters moving forward reflect the procedural use of the mysqli_* family of functions. You can also use these functions in an object-oriented way; for more information on that, visit the PHP Manual at http://www.php.net/mysqli. Making a ConnectionThe basic syntax for a connection to MySQL is $mysqli = mysqli_connect("hostname", "username", "password", "database");Using actual sample values, the connection code looks like this: $mysqli = mysqli_connect("localhost", "joeuser", "somepass", "testDB");Listing 18.1 is a working example of a connection script. It creates a new connection in line 2 and then tests to see whether an error occurred. If an error occurred, line 5 prints an error message and uses the mysqli_connect_error() function to print the message. If no error occurs, line 8 prints a message including host information resulting from calling the mysqli_get_host_info() function. Listing 18.1. A Simple Connection Script
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, if the connection was successful: Host information: localhost via TCP/IP If the connection fails, an error message is printed. Line 5 generates an error via the mysqli_connect_error() function; an example is shown here: Connect failed: Access denied for user 'joeuser'@'localhost' (using password: YES) However, if the connection is successful, line 8 prints the output of mysqli_get_host_info(), such as: Host information: localhost via TCP/IP Although the connection closes when the script finishes its execution, it's a good idea to explicitly close the connection. You can see how to do this in line 9 of Listing 18.2, using the mysqli_close() function. Listing 18.2. The Modified Simple Connection Script
That's all there is to it. The next section covers the query execution functions, which are far more interesting than simply opening a connection and letting it sit there! Executing QueriesHalf the battle in executing MySQL queries using PHP is knowing how to write the SQLand you've already learned the basics of this in previous chapters. The mysqli_query() function in PHP is used to send your SQL query to MySQL. In your script, first make the connection and then execute a query. The script in Listing 18.3 creates a simple table called testTable. Listing 18.3. A Script to Create a Table
By the Way When issuing queries via a script, the semicolon at the end of the SQL statement is not required. In lines 8 and 9, the text that makes up the SQL statement is assigned to the variable $sql. This is arbitrary, and you don't even need to place the content of your SQL query in a separate variableit appears as such in the example so that the different parts of this process are clear. The mysqli_query function returns a value of true or false, and this value is checked in the if...else statement beginning in line 12. If the value of $res is true, a success message is printed to the screen. 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)If this is the case, congratulationsyou have successfully created a table in your MySQL database using PHP! However, if the value of $res is not true, an error message will appear, generated by the mysqli_error() function. Retrieving Error MessagesTake some time to familiarize yourself with the mysqli_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 mysqli_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. Try to execute the script again; when you execute the script, you should see something like the following in your web browser: Could not create table: 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. |
EAN: 2147483647
Pages: 327