How MySQL and PHP Work Together

This article is a fraction of a Number of Articles on MySQL, to access them click here.

You interact with the database by passing messages to the MySQL server.

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.

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. I 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  to mysql functions.

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.

Communicating with MySQL

Communicating with MySQL involves the following steps:

1. Connect to the MySQL server.
2. Send the SQL query.

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 assumes

localhost.

✦ acct: The name of any valid MySQL account.

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