PHP, MySQL, JavaScript & HTML5 All-in-One For Dummies (2013)

Book V: MySQL

Chapter 5: Communicating with the Database from PHP Scripts

In This Chapter

Using PHP built-in functions to access MySQL

Sending SQL queries to the MySQL server

Understanding how to handle MySQL errors

Using other helpful functions

Changing functions from mysqli to mysql

PHP and MySQL work well together, and this dynamic partnership is what makes PHP and MySQL so attractive for web database application development. Whether you have a database full of information that you want to make available to users (such as a product catalog) or a database waiting to be filled by users (for example, a customer database), PHP and MySQL work together to implement your application.

This chapter describes accessing MySQL from PHP scripts.

Knowing How MySQL and PHP Work Together

You interact with the database by passing messages to the MySQL server. As explained in Chapter 1 of this minibook, the messages are composed in the SQL language, a standard computer language understood by most database management systems.

PHP doesn’t understand SQL, but it doesn’t need to: PHP just establishes a connection with the MySQL server and sends the SQL message over the connection. The MySQL server interprets the SQL message, follows the instructions, and sends a return message that states its status and what it did (or reports an error if it couldn’t understand or follow the instructions).

The PHP language provides functions that make communicating with MySQL extremely simple. You use PHP functions to send SQL queries to the database. You don’t need to know the details of communicating with MySQL; PHP handles the details. You only need to know the SQL queries and how to use the PHP functions.

We describe the general syntax for SQL queries in Chapter 1 of this minibook. Individual specific queries are described in detail where we describe how to use MySQL for a specific purpose. For example, we describe how to create MySQL accounts in Chapter 2 in this minibook, so the SQL query for creating accounts is described at that location. On the other hand, we describe how to retrieve data from a MySQL database in Chapter 4 in this minibook, so the SQL query used for that purpose is described in detail in that chapter.

PHP Functions That Communicate with MySQL

PHP provides two sets of functions for communicating with MySQL — the mysql functions and the mysqli (MySQL Improved) functions. Which functions you use depends on the version of MySQL and PHP you’re using.

The mysqli functions were added in PHP 5 for use with MySQL versions 4.1 and later. If you’re using a web hosting company, you need to know whether it offers PHP 5, which version of MySQL it provides, and whether it makes the mysqli functions available. In this book, we assume that you’re using PHP 5 or later, MySQL 5.0, and the mysqli functions. If your web host doesn’t offer the mysqli functions, you need to convert the mysqli functions in this book to mysql functions. The section Converting mysqli Functions to mysql Functions, later in this chapter, explains the differences.

If you installed PHP and MySQL yourself on your own computer planning to develop your PHP scripts locally and upload the finished scripts to your web hosting company, you need to install the same versions and activate the same MySQL support functions that your web host provides. Otherwise, if you install different versions, even newer ones, the scripts may not behave in the same way on your web host’s computer as they do on your local computer.

You can find a discussion of the issues about and instructions for installing your web development environment in Book I.

Communicating with MySQL

This chapter describes accessing MySQL from PHP scripts. (Accessing MySQL databases outside of PHP scripts is discussed in Chapters 1–4 in this minibook.) SQL queries are sent to MySQL using PHP functions. Communicating with MySQL involves the following steps:

1. Connect to the MySQL server.

2. Send the SQL query.

In this section, we tell you how to do both steps, and we tell you how to send multiple queries.

Connecting to the MySQL server

Before you can store or get any data, you need to connect to the database, which might be on the same computer as your PHP scripts or on a different computer. You don’t need to know the details of connecting to the database because PHP handles the details. All you need to know is the name and location of the database, along with a username and password to access it. Think of a database connection in the same way that you think of a telephone connection. You don’t need to know the details about how the connection is made — that is, how your words move from your telephone to another telephone — you need to know only the area code and phone number. The phone company handles the details.

To connect to the MySQL server, you need to know the name of the computer on which the database is located and your MySQL account’s user ID and password. For most queries, you also need to know the name of the database with which you want to interact.

To open the connection, use the mysqli_connect function:

$cxn = mysqli_connect("host","acct","password","dbname")

or die ("message");

Fill in the following information:

host: The name of the computer on which MySQL is installed — for example, databasehost.example.com. If the MySQL database is on the same computer as your website, you can use localhost as the computer name. If you leave this information blank (""), PHP assumeslocalhost.

acct: The name of any valid MySQL account. (We discuss MySQL accounts in detail in Chapter 2 of this minibook.)

password: The password for the MySQL account specified by acct. If the MySQL account doesn't require a password, don't type anything between the quotes: "".

dbname: The name of the database with which you want to communicate. This parameter is optional — you can select the database later, with a separate command, if you prefer. You can select a different database at any point in your script.

If you're using the mysql functions, you can't select the database in the connect function. You must use a separate function — mysql_select_db — to select the database.

message: The message sent to the browser if the connection fails. The connection fails if the computer or network is down, or if the MySQL server isn't running. It also may fail if the information provided isn't correct — for example, if the password contains a typo.

You might want to use a descriptive message during development, such as Couldn't connect to server, but a more general message suitable for customers after you put the application in use, such as The Catalog is not available at the moment. Please try again later.

The host includes a port number that's needed for the connection. Almost always, the port number is 3306. On rare occasions, the MySQL administrator needs to set up MySQL so that it connects on a different port. In these cases, the port number is required for the connection. The port number is specified as hostname:portnumber. For instance, you might use localhost:8808.

With these statements, mysqli_connect attempts to open a connection to the named computer, using the account name and password provided. If the connection fails, the script stops running and sends message to the browser.

The following statement connects to the MySQL server on the local computer, using a MySQL account named phpuser that doesn't require a password:

$cxn = mysqli_connect("localhost","phpuser","","Customer")

or die ("Couldn't connect to server.");

For security reasons, you should store the connection information in variables and use the variables in the connection statement, as follows:

$host="localhost";

$user="phpuser";

$password="";

$dbname = "Customer";

$cxn = mysqli_connect($host,$user,$password,$dbname)

or die("Couldn't connect to server.");

For even more security, you can put the assignment statements for the connection information in a separate file in a hidden location so that the account name and password aren't even in the script. You insert the account information from the file by using an include statement, as described in Book IV, Chapter 2.

The variable $cxn contains information that identifies the connection. You can have more than one connection open at a time by using more than one variable name.

A connection remains open until you close it or until the script ends. You close a connection as follows:

mysqli_close($connectionname);

For instance, to close the connection in the preceding example, use this statement:

mysqli_close($cxn);

Sending an SQL statement

After you have an open connection to the MySQL server, you send your SQL statement query. You can find details of the SQL statements and queries that you need for specific purposes in the other chapters in this minibook.

To interact with the database, put your SQL statement into a variable and send it to the MySQL server with the function mysqli_query, as in the following example:

$query = "SELECT * FROM Customer";

$result = mysqli_query($cxn,$query)

or die ("Couldn't execute query.");

The query is executed on the currently selected database for the specified connection.

The variable $result holds information on the result of executing the query but not the actual results. The information in $result depends on whether or not the query gets information from the database:

For queries or statements that don't get any data: The variable $result contains information about whether the query or statement executed successfully or not. If it's successful, $result is set to true; if it's not successful, $result is set to false. Some queries and statements that don't return data are INSERT and UPDATE.

For queries that return data: The variable $result contains a result identifier that specifies where the returned data is located, not the returned data itself. Some queries that return data are SELECT and SHOW.

The use of single and double quotes can be a little confusing when assigning the query or statement to the $query variable. You're actually using quotes on two levels: the quotes that assign the string to $query and the quotes that are part of the SQL language itself. The following guidelines can help you avoid any problems with quotes when working with SQL:

Use double quotes at the beginning and end of the string.

Use single quotes before and after variable names.

Use single quotes before and after literal values.

The following statements show examples of assigning SQL strings to variables in PHP:

$query = "SELECT firstName FROM Customer";

$query = "SELECT firstName FROM Customer WHERE lastName='Smith'";

$query = "UPDATE Customer SET lastName='$last_name'";

The SQL statement itself doesn't include a semicolon (;), so don't put a semicolon inside the final quote. The only semicolon appears at the very end, as shown in the previous examples; this is the PHP semicolon that ends the statement.

Sending multiple queries

Sometimes, you want to send two or more queries at the same time. MySQL allows you to do so, but you need to use a different function to send the queries. You can send multiple queries with the following function:

mysqli_multi_query($cxn,$query)

You send the queries in a single string with the queries separated by a semicolon:

$query = "SELECT * FROM Cust;SELECT * FROM OldCust";

mysqli_multi_query($cxn,$query);

The multiple_query function isn't available with the mysql functions, only with the mysqli functions.

Sending queries can be less secure than sending one query. Whenever you use data from an outside source, be sure you validate the outside data thoroughly. For instance, suppose you display a form asking the user for a table name, and you create a query from the table name that the user enters, as follows:

$query = "SELECT * FROM Friend";

The user enters the table name Friend. The query is fine. However, suppose the user enters the following into the form:

Friend;DELETE TABLE Friend

Your query then is

$query = "SELECT * FROM Friend;DELETE TABLE Friend";

If you send this query, the query is not so fine. You won’t like the results. You probably didn’t want the table deleted. Be sure to always sanitize data before sending it to MySQL!

Selecting a Database

If you don't select the database in the connect function, you can select the database by using the mysqli_select_db function. You can also use this function to select a different database at any time in your script. The format is

mysqli_select_db($cxn,"databasename")

or die ("message");

If you're using the mysql functions, rather than the mysqli functions, you must select the database in a separate function, using mysql_select_db. The section Converting mysqli Functions to mysql Functions, later in this chapter, explains in more detail.

Fill in the following information:

cxn: The variable that contains the connection information.

databasename: The name of the database.

message: The message that's sent to the browser if the database can't be selected. The selection might fail because the database can't be found, which is usually the result of a typo in the database name.

For instance, you can select the database Customer with the following statement:

mysqli_select_db($cxn,"Customer")

or die ("Couldn't select database.");

If mysqli_select_db can't select the database, the script stops running and the message Couldn't select database. is sent to the browser.

The database stays selected until you select a different database. To select a different database, just use a new mysqli_select_db function statement.

Handling MySQL Errors

You use the mysqli functions of the PHP language, such as mysqli_connect and mysqli_query, to interact with the MySQL database. Things will sometimes go wrong when you use the statements. You may make an error in your typing, such as mistyping a database name. Sometimes, problems arise that you can't avoid, such as the database or the network being down. You need to include code in your script that handles error situations.

You can read about PHP error handling in Book IV, Chapter 1. That chapter describes the types of errors that PHP displays and how to turn them on and off. As discussed in Book IV, you usually want to make your error handling more descriptive to assist with troubleshooting problems during development, but you don’t want the extra information displayed to the public.

For instance, suppose that you're using an account called root to access your database and you make a typo, as in the following statements:

$host = "localhost";

$user = "rot";

$password = "";

$cxn = mysqli_connect($host,$user,$password)

Because you type "rot" rather than "root", you see a warning message similar to this one:

Warning: Access denied for user: 'rot@localhost' (Using password: NO) ...

The preceding error message contains the information that you need to figure out the problem — it shows your account name that includes the typo. However, after your script is running and customers are using it, you don't want your users to see a technical error message that shows your user ID. You want to turn the PHP errors off or send them to an error log file. You could then use a die statement to stop the script and display a polite message to the user, as follows:

$cxn = mysqli_connect($host,$user,$password)

or die("The Catalog is not available at the moment. Please try again later.");

When a mysqli_query() function fails, MySQL returns an error message that contains information about the cause of the failure. However, this message isn't displayed unless you specifically display it. Again, you may want to see these messages when you're developing the script, but you may not want to display them to the public. You can display the MySQL error that's returned by using the following function:

mysqli_error($cxn)

For example, you might include the function in your code, as follows:

$query = "SELECT * FROM Cust";

$result = mysqli_query($cxn,$query)

or die ("Error: ".mysqli_error($cxn));

In this example, if the function call fails, the die statement displays the MySQL error, which might be something like this:

Error: Table 'catalog.cust' doesn't exist

Occasionally, you may want to perform additional actions if the function fails, such as delete variables or close the database connection. You can perform such actions by using a conditional statement:

if(!$result = mysqli_query($cxn,$query))

{

echo mysqli_error($cxn);

unset($auth);

exit();

}

If the function call fails, the statements in the if block are executed. The echo statement displays the MySQL error returned by the function. A variable is removed, and the script exits.

Notice the ! (exclamation point) in the if statement. ! means "not". In other words, the if statement is true if the assignment statement is not true.

Using Other Helpful mysqli Functions

Other useful mysqli functions are available for you to use in your PHP scripts. The following subsections describe how to use mysqli functions to count the number of rows returned by a query, determine the last automatically made entry, count rows affected by a query, and escape characters.

Counting the number of rows returned by a query

Often, you want to know how many rows your SQL query returned. Your query specifies criteria that the information must meet to be returned, such as state must equal TX or lastName must equal Smith. The function mysqli_num_rows tells you how many rows were found that meet the criteria.

Login pages frequently use this function. When a user attempts to log in, he or she types a username and a password into an HTML form. Your PHP script then checks for the username and password in a database. If it is found, the username and password are valid. You might use code similar to the following:

$query = "SELECT * FROM ValidUser

WHERE acct = '$_POST[userID]

AND password = '$password'";

$result = mysqli_query($cxn,$query);

$n = mysql_num_rows($result);

if($n < 1)

{

echo "User name and password are not valid";

exit();

}

In this code, the SQL query looks for a row with the username (called acct in this example) and password provided by the user in the form. The code then tests the query result to see how many rows it contains. If the result doesn't contain any rows, that is less than one row, a user with the provided username and password doesn't exist in the database, and thus, the account information is not valid and the user is not allowed to log in.

Determining the last auto entry

Many database tables contain an AUTO_INCREMENT field. This is a serial field in which MySQL adds the field value automatically. When a row is added, MySQL gives the AUTO_INCREMENT field the next serial value after the preceding row. Such fields are often defined as a unique identifier or primary key for a table.

Because MySQL adds the auto value, you do not necessarily know which value was stored in the field for the new row. In some situations, you need to know what the number was so that you can use it later in the script. The function mysqli_insert_id returns the number that was last added to an AUTO_INCREMENT field.

One situation in which you need to know the number MySQL stored in the field is when you store an order and order items in separate tables. For example, if you define the orderID field as an AUTO_INCREMENT field, MySQL adds the number to the orderID field. However, you need to store this number in the OrderItem table so that you can connect the items to the order. You might use code similar to the following:

$query = "INSERT INTO CustomerOrder (customerID,orderDate)

VALUES ($customerID,$date)";

$result = mysqli_query($cxn,$query);

$orderID = mysqli_insert_id($cxn);

$query = "INSERT INTO OrderItem (orderID,color,size,price)

VALUES ($orderID,$color,$size,$price)";

$result = mysqli_query($cxn,$query);

In the first query, orderID is not specified, so MySQL stores the next serial number in that field. In the second query, the orderID inserted in the previous query is inserted into the second table.

Counting affected rows

Some SQL queries change the database, but don't return any data. For instance, an UPDATE query can change the data in a table, but it doesn't return any data. In this case, an UPDATE statement may affect one, many, or zero rows. For instance, the following is an UPDATE statement:

$stmt = "UPDATE Customer SET lastName = "Smyth"

WHERE lastName = "Smith";

This statement will change any last names in the table with the value Smith to Smyth.

In some cases, you may need to know how many rows were changed by the statement. In this example, there may be no one in the database with the name Smith or there may be hundreds. You can find out how many rows were updated with the mysqli_affected_rows function. This function returns the number of rows that were affected by the last UPDATE, INSERT, REPLACE, or DELETE statement.

Suppose you want to set a field in a table that identifies students who passed a test. You might also want to know how many of the students passed. You might use code similar to the following:

$query = "UPDATE Student SET status='pass' WHERE score > 50";

$result = mysqli_query($cxn,$query);

$passed = mysqli_affected_rows($cxn);

echo "$passed students passed";

In this code, any student in the table whose score is higher than 50 passed the test. The variable $passed contains the number of students whose score was high enough for their status field to be updated to "pass".

Escaping characters

When you store any string information in your database, you need to escape special characters. This is an essential security measure.

PHP versions before version 6 provide a feature called magic quotes that automatically escapes all strings in the $_POST and $_GET arrays. Single quotes, double quotes, backslashes, and null characters are escaped. This feature, designed to help beginning users, is controlled by themagic_quotes-gpc setting in php.ini and is turned on by default in PHP 4 and PHP 5. In PHP 6, the magic quotes feature is no longer available.

The magic quotes feature results in a great deal of inefficient, unnecessary escaping. It also results sometimes in undesirable escaping. In general, we recommend you turn off magic quotes in your php.ini file. Making changes to the php.ini is discussed in more detail in Book IV, Chapter 1.

Because it is essential that you escape your data before storing it, if the magic quotes feature is turned off, you must escape your data manually. The function mysqli_real_escape_string is provided for this purpose. Before storing any data in a database, apply the function to it. The following lines show some possible code that escapes data so it is safe to store in a database:

$lastName = mysqli_real_escape_string($lastName);

$lastName = mysqli_real_escape_string($_POST['lastName']);

Converting mysqli Functions to mysql Functions

This book assumes you’re using PHP 5 or later with the mysqli functions to interact with MySQL 5.0 or 5.1. If you’re using PHP 4, the mysqli functions aren’t available. Instead, you use the mysql functions, even with later versions of MySQL. The mysql functions can communicate with the later versions of MySQL, but they can’t access some of the new features added in the later versions of MySQL. The mysql functions are activated automatically in PHP 4.

Throughout this book, the examples and scripts use MySQL 5.0 and the mysqli functions to communicate with MySQL. The PHP functions for use with MySQL 5.0 have the following general format:

mysqli_function(value,value,...);

The i in the function name stands for improved (MySQL Improved). The second part of the function name is specific to the function, usually a word that describes what the function does. In addition, the function usually requires one or more values to be passed, specifying details such as the database connection or the data location. Here are two of the mysqli functions discussed earlier in this chapter:

mysqli_connect(connection information);

mysqli_query($cxn,"SQL statement");

The corresponding mysql functions are

mysql_connect(connection information);

mysql_query("SQL statement",$cxn);

The functionality and syntax of the functions are similar, but not identical, for all functions. In particular, mysqli functions use a different process for connecting to the MySQL server than mysql functions do. The format of the mysqli function is

mysqli_connect($host,$user,$password,$dbname);

The connection process for mysql functions requires two function calls:

mysql_connect($host,$user,$password);

mysql_select_db($dbname);

If you need to use the mysql functions, rather than the mysqli functions, you need to edit the scripts in this book, replacing the mysqli functions with mysql functions. Table 5-1 shows mysqli function syntax and their equivalent mysql function syntax.

Table 5-1 Syntax for mysql and mysqli Functions