Introduction to PHP and MySQL

How they work together.

By Kevin Waterson <kevin@phpro.org>

Contents

1. What is this tutorial?
2. Getting started.
3. Connecting to MySQL
   • Connecting with mysql
   • Connecting with mysqli
   • Connecting with PDO
4. Creating a database
   • Create with mysql
   • Create with mysqli
   • Create with PDO
5. Creating a MySQL user
   • Create user with mysql
   • Create user with mysqli
   • Create user with PDO
6. Creating a Table
   • Create table with mysql
   • Create table with mysqli
   • Create table with PDO
7. INSERT data into MySQL
   • INSERT with mysql
   • INSERT with mysqli
   • INSERT with PDO
8. INSERT multiple records
   • INSERT with mysql
   • INSERT with mysqli
   • INSERT with PDO
9. SELECTING data
   • SELECT with mysql
   • SELECT with mysqli
   • SELECT with PDO
   • SELECT mulitple Conditions
10. DELETING data
    • Delete with mysql
    • Delete with mysqli
    • Delete with PDO
11. UPDATING Data in MySQL
    • UPDATE with mysql
    • UPDATE with mysqli
    • UPDATE with PDO
12. LIMIT data selections
13. Configuration Options
14. Formatting Dates
    • Format Date with PHP
    • Format Date with MySQL
    • Format Date with PHP and MySQL
15. Load a CSV File
16. Preventing SQL Injection
    • Prevent with MySQL
    • Prevent with MySQL
    • Prevent with MySQL
17. Useful Tips

What is this tutorial?

This tutorial is aimed at those new to PHP and MySQL. The object of this tutorials is to show by way of example how to use php to CREATE a database, how to CREATE a table, how to INSERT data into a database, and how to SELECT that data and display it on a web page.

This tutorial does not cover the editting of data or the use of forms when sending data to a mysql database. These topics are covered in the Secure PHP MySQL tutorial. This tutorial will grow in size and complexity as time permits. Lets begin with a brief description of what MySQL and PHP are.

MySQL is a RDBMS. That is a Relational Database Management System. It uses a language called SQL (Structured Query Language) to add, access and process data within a database.

PHP is a scripting language that has revolutionised the way we serve information. Its powerful, yet simple C like syntax has made it a favorite of those just beginning in web technologies and those with strong programming backgrounds. The beauty of these two technologies is how well they come together in making dynamic information available over the net.

Getting Started.
This tutorial requires you to have a basic level of PHP and SQL. It also requires you to have these installed on your machine. It is beyond the scope of this document to detail the installation of PHP or MySQL and if you need help with these we suggest you read the appropriate installation instructions on php.net and mysql.com. PHP comes with three levels of interfacing with MySQL. These three extensions are

1. mysql
2. mysqli
3. PDO

The first is the standard set of MySQL functions. The second, mysqli, is Mysql Improved. This extension contains a feature rich interface to later version of MySQL and has a gnarly OO interface. Thirdly, the ubiquitous PDO (Php Data Object). The PDO extension provides a standard interface for accessing many databases. It is not as feature rich as mysqli but for most uses, it provides a standard method of interacting with all databases.Detailed installation intructions can be found in the very fine PHP manual. During this tutorial we will show how various tasks are accomplished using all three methods.

Connecting to MySQL.

Before we can do anything with MySQL we need to be able to connect to the server. Here we will demonstrate 3 ways of doing this with PHP mysql, mysqli and PDO. First with the standard mysql connection.

Connecting with mysql.

Before we move on with the other extensions, lets look briefly at what has happened above. We have begun by setting some variables for our database, these are the hostname, username, and password. The next line of code shows the use of mysql_connect() function to connect to the MySQL server. Lets look at this line further.

We have created a mysql link resource variable called $link. We see also here the use of the @ symbol to suppress and errors that may arise from a failure to connect, more on this later..., After we use mysql_connect() we check with the is_resource() function that the link variable is indeed a valid resource, and if so, we can continue with our code, else, an error is printed saying Unable to connect. If the connection is valid, then a message is printed to say so, and the database link is closed with the mysql_close() function. The database connection should cease at the end of the script, but with longer running scripts this may be a waste of valuable resources, so we free up some room with mysql_close(). Now, lets see the same thing with mysqli..

Connecting with mysqli.

This could simply be done in the same manner as mysql_connect(), but mysqli offers us an Object Oriented (OO) interface to MySQL so it would be futile to do things "the old way". Lets see how we go.

Here we see the creation of a new mysqli object which passes the hostname, username, and password to the constructor which makes the connection for us. We can then use this new object for other functionality that we will soon use. We have also suppressed error_reporting with the @ symbol once more, this is because a warning will be produced if the connection fails. We have also tested for an error condition (more on errors later) to see if the connection is valid, and if so, it tells us. Now, lets move onto PDO.

Connecting with PDO

PHP has spawned an amazingly neat idea to standardize database connections. This is PDO (Php Data Objects). A full Introduction to PDO is available and is well worth reading. Here we will use some of the concepts from this article to use with mysql. So, lets get connected

In the above code, several variations have taken place. Like the other connections we have supplied a hostname, username, and password. PDO demands that we also have a valid database to connect to. If no database is specified an exception is thrown. In the example above we have used the default mysql database to connect to. The database named mysql contains the tables of users, databases and other vital information. YOU HAVE BEEN WARNED! We use the catch{ } block to catch any thrown exceptions from the try{ } block. Another variation is that we have used root as the username.
Is this a bad idea?
In most circumstances yes, however, here we wish to demonstrate some administrative tasks such as creating users and creating databases. Most MySQL servers, by default, will not allow this to be done by a user other than the root super user. In the following section we will be creating a database, so root access is required.

Creating a Database.

Now we have seen how to connect to a database server, it is just a small step to create a database. You will need to be a user with CREATE privileges to do this. Usually this will be the user root, so thats what we will be using here. Once again we will use the three different PHP mysql extenstions to do this, beginning with the mysql extenstion.

Create with mysql.

In the above code the we have used the function mysql_query() to run the SQL statement
CREATE DATABASE periodic_table
You may ask why we created the variable $sql when it would have been just as easy, and used less code to embed the sql query as an arguement for the mysql_query() function like this:

We created the variable $sql for possible debugging purposes. Should we encounter an error within our sql statement, the error checking will print a message, including the sql statement. Eg: should we run this script twice, we would see an error something like this..

Connected successfully
Unable to create database:
CREATE DATABASE periodic_table
Cannot create database periodic_table; database exists

Create with mysqli.

The mysqli connection provides a smooth object oriented approach for same functionality. Using the connection code from the earlier connect script we can simply use the created mysqli object to run queries.

Should we run this code twice, we would again get an get an error like the following

Connected Successfully
CREATE DATABASE periodic_table
Cannotcreate database periodic_table; database exists

You can see from the message, as in the previous example, we have a message to tell us that we have connected successfully, then the SQL query used followed by a message from the database itself telling us the exact nature of the error.

Create with PDO

Not to be out-done in the Object Oriented approach, PDO makes the code base somewhat cleaner. Using the code from the initial connection we can use the provided DSN object for our query.

As you see, PDO provides a nice exception class to handle any problems that may arise in our database queries. If an exception is thrown within the try{ } block, no further code within the try{ } block is executed and the flows directly to the first catch(){ } block. In the catch block above we echo the SQL statement and any error message that may be generated by the database.

This query is not one you will be running often but is needed in order to show how to create database tables. Below is the SQL statement needed to create the a table and fields within your newly created database.

Creating a MySQL user

Now a database has been created, a user other than the root user needs to be able to work with it. This means a MySQL user, not a system user, needs to be created and given the appropriate permissions to be able to work with data. We will create a user with all permissions on the periodic_table database. If you want real security, you can limit the permissions a user has to just SELECT, thus limitting possible injection attacks. The topic of PHP/MySQL security will be covered in another article. For now, we will GRANT ALL privileges to the new user, except GRANT. Once again we need to have root access to mysql to do this. The code itself should look very familiar if you have read the previous section as it is almost identical. The same principles and connections apply, the only thing that changes is the SQL statement itself.

Create a user with mysql.

As mentioned, this is identical to creating a database, the only change is the SQL statement, we have reproduced the code here for the benifit of familiarity.

Create a user with mysqli.

Here also there are no changes except for the SQL. All the code remains as it was to create a database.

Create a user with PDO

And you guessed it, no changes here either from the CREATE DATABASE example above. Everything remains the same except for the SQL statement itself, the new user is created, and if there is a problem an exception is thrown.

Creating a Table.

Now that a database has been created, and a database user other than root exists, we are able to connect to the database with our shiny new username and password. A database it really needs some data in it for it to be functional. For the purposes of this tutorial we are using the periodic table of elements, (we can all recite those from school, right?) so in our periodic_table database an appropriate table name would be elements. Lets first look at the SQL need to create such a table.

Once again we can use code similar to that which we have already used to create a database and to create a new MySQL user. The same process is repeated here with the difference that we no longer need root access to be able to interface with our new database. The user "username" can be used and this is recommended practice. Never use root if you do not need it. Our new user has all the privileges needed to carry out any interactions with the database in this tutorial. With that in mind, lets get to table creation, first with the mysql extension functions.

Create table with mysql.

Lets go over what we have done here. We have used the CREATE TABLE statement to create a table called elements
Following that we have 4 fields that will contain the data itself. They are:
atomicnumber
latin
english
abbr
These fields will contain different data types and so have different attributes. The atomicnumber field has a type of tinyint or tiny interger and its maximum length is 3 with a default value of zero. More on types can be found in the MySQL documentation and is recommended reading.

Creating a Table with mysqli.

As with the script above, we no longer need to use the root username and password for creating our table. An extra parameter is added to our class instantiation that contains the name of the default database to use for our script. We assign this to a variable at the top of our script with the other variables.

Creating a Table with PDO

The use of PDO will significantly cut down our code. Much of the savings comes from the use of exceptions rather than constantly checking for error conditions. As with the previous table creation examples we are now using a non-root username/password pair. We have also created a new variable called $dbname. This variable holds the name of the database we wish to connect to and is used when we instantiate a new PDO object like this:

With this in mind, our table creation script using PDO is as follows.

You should now have a database called "periodic" with a table named periodic within it. This table is has 4 fields and it is these that will contain the real data we wish to use. First we must INSERT some data into the tables.

INSERT data into MySQL

This will likely be the second most frequent task done with the database. All the data in the database needs to come from somewhere and it is done using the INSERT statement. The process is similar to what we have been doing with other SQL statements and should be familiar by now, so lets get to it...

INSERT data with mysql.

Now you should be getting in the swing of sending SQL to the database. It goes much the same for mysqli and PDO. We connect to the database server, then select the appropriate database, and issue commands. Lets continue with our mysqli example.

INSERT data with mysqli

Once again we have used the same code with just a different SQL statement. The process remains the same for commands that do not return a value or values.

INSERT data with PDO

Not to be out classed, PDO maintains a compact code base for perfoming the same task. Here we see the creation of the same record as above. Again we use the same script as creating the database and table. The only thing that changes is the SQL statement.

INSERT multiple records

In the previous section we can see how to INSERT a record into a database. This works fine for single records, but what if we have over one hundred records. The periodic table of elements holds one hundred and nine known elements. We need a method to INSERT all these records without needing one hundred and nine database queries. This would be a much to heavy drain on resources and time conusumin and time conusuming. The solution comes in several forms, depending on the extension you are using. We will step through each extension and see how the various INSERT methods compares. Lets get started with the mysql extention. MySQL provides a simple query method of handling multiple INSERTs.

INSERT multiple records with mysql

The above code will populate the elements table with all one hundred and nine elements. This is far superior to one hundred and nine seperate INSERT statements and database connections. Be aware that of the Max Allowed Packet size as large SQL statements of several thousand INSERTs or BLOBs may breach this limit.

INSERT multiple records with mysqli

The mysqli extenstion provides an object oriented approach to the same task. The mysqli extension contains a class method named multi_query() for exactly this purpose. This class method basically takes an SQL statement, or multiple SQL statements concatenated by a semicolon ; character. Lets see it in action with a small subset of our periodic table of elements. We use a subset because I had to type that first lot out and it took me hours ...

The above, shortened INSERT will work for the whole table and it is left as an exercise for the reader to complete the table. The mysqli extension provides a second method for achieving the same result for multiple INSERT. We can also use prepared statements and bind parameters. The mysql extension can send a statement or query without any data to the mysql database. You can then associate or "bind" variables to the columns.

We see above a more modular solution to the issue. We can create code blocks also that are easy to read and easy to manage. Just a note on binding parameters. Lets examine the mysqli_stmt_bind_param() line of code...

This function binds the parameters to the query and tells the database what the parameters are. Next is the "isss" arguement. This listes the types of data that the parameters are. The i tells mysql that the parameter is an integer. The s character tells mysql that the parameter is a string. This arguement may be one of four types.

• i - integer
• d - double
• s - string
• b - BLOB

You must have one of these for each parameter. Prepared statements separate data from logic, thereby aiding us in our quest for secure data. By telling mysql what type of data to expect we can help minimise the risk of SQL injection vulnerabilities in our code. Should any values from external sources be used it is critical that they be sanitized and validated. More on this topic later...

INSERT multiple records with PDO

If you have followed the previous example with mysqli, the step to using PDO is small. The PDO interface allows us to use bound parameters and prepared statements also. The great benifit of PDO is exceptions and use of arrays for queries. For multiple a mulitple INSERT statement would could use an array as a parameter. The PDO interface will also give easy access to the use of transactions. In the following example we will see how to quickly deal with a multiple INSERT. Once again we will use only a small portion of the table to demonstrate for obvious reasons.