Using PHP with MySQL
There is a TV commercial that shows what would happen to the world if we didn't have plastic. In the commercial objects like telephones, chairs and computers disappear before our eyes. The objective is to make us appreciate what we would happen to our infrastructure without plastic. A similar analogy can be drawn to the need for databases on the world wide web. Without databases many of the web sites we rely on would quickly cease to function. It is not an exageration to suggest that databases form the heart of the web and the internet as we know it. Without some way to store and retrieve data the usefulness of the internet would be a greatly reduced.
One of the many advantages of PHP is the seemless way in which it integrates with the MySQL database. In this chapter we will take a close look at how to access information stored in a MySQL database from a PHP script and present that data to a user's web browser.
Before beginning it is important to note that MySQL is an advanced Relatation Database Management System (RDCMS), and as such, may be more sophisticated than is needed for every data storage need. In the next chapter (PHP and SQLite) we will look at a more lightweight solution that may meet less demanding needs.
This chapter assumes that you have MySQL installed and running on your server. There are many resources on the internet that can help you achieve this. The first place to start is the MySQL documentation.
Creating a MySQL User Account
The first task is to create a MySQL user account we can use for the purposes of this chapter. To set up as user account you will need to log into MySql using an account that has suitable privilages. If you are unsure about which user account to use speak to your system administrator or refer to the MySQL documentation. In this example we will assume you have the root password:
mysql -p -u root Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10411 to server version: 5.0.27 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>
Having logged into MySQL we can now create a new user called phptest. In the example below we assign the password mypassword. It is advised you use your own passowrd in place of this one:
mysql> GRANT ALL PRIVILEGES ON *.* TO 'phptest'@'localhost'
-> IDENTIFIED BY '3579php' WITH GRANT OPTION;
Query OK, 0 rows affected (0.07 sec)
We now have a user account we can use to create a sample database table and can log into MySQL using this account and password:
$ mysql -p -u phptest Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10476 to server version: 5.0.27 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>
Creating and Select MySQL Database
The next step is to create a database to store the data we will use in our PHP database example. We will call the database PHPsampleDb as follows:
mysql> CREATE DATABASE PHPsampleDB; Query OK, 1 row affected (0.00 sec)
We can then select the database using the USE command:
mysql> USE PHPsampleDB; Database changed
Our database is now created and selected. Next we need to add a table to the database.
Creating a MySQL Database Table
For the purposes of our example we will create a very simple database table. It is going to contain just three fields, a customer name, email address and account number. We will call this table customer and create it as follows:
mysql> CREATE TABLE customer (name VARCHAR(30), email VARCHAR(30),
-> account VARCHAR(20));
Query OK, 0 rows affected (0.01 sec)
Inserting Data into a MySQL Database Table
As the final step isn setting our sample database we need to add some data to the table. This is achieved using tyhe INSERT command. We will add three initial rows to the table:
mysql> INSERT INTO customer VALUES ('Gregory House', '[email protected]', '12345678');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO customer VALUES ('Robert Chase', '[email protected]', '87654321');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO customer VALUES ('Lisa Cuddy', '[email protected]', '24688642');
Query OK, 1 row affected (0.00 sec)
We can now check that these records are set up correctly using the SELECT command:
mysql> SELECT * FROM customer; +---------------+-----------------+----------+ | name | email | account | +---------------+-----------------+----------+ | Gregory House | [email protected] | 12345678 | | Robert Chase | [email protected] | 87654321 | | Lisa Cuddy | [email protected] | 24688642 | +---------------+-----------------+----------+ 3 rows in set (0.00 sec)
