Home

MySQL PHP tutorial

About this tutorial

This is a PHP programming tutorial for the MySQL database. It covers the basics of MySQL programming with PHP. It uses the generic mysql module. The examples were created and tested on Ubuntu Linux. There is a similar MySQL C API tutorial, MySQL Python tutorial and MySQL Visual Basic tutorial on ZetCode.

If you need to refresh your knowledge of the PHP language, there is a full PHP tutorial on ZetCode.

About MySQL database

MySQL logo MySQL is a leading open source database management system. It is a multi user, multithreaded database management system. MySQL is especially popular on the web. It is one of the parts of the very popular LAMP platform. Linux, Apache, MySQL, PHP. Currently MySQL is owned by Oracle. MySQL database is available on most important OS platforms. It runs under BSD Unix, Linux, Windows or Mac. Wikipedia and YouTube use MySQL. These sites manage millions of queries each day. MySQL comes in two versions. MySQL server system and MySQL embedded system.

Before we start

We need to install several packages to execute the examples in this tutorial. php5-cli, php5-mysql, mysql-server, mysql-client.

The php5-cli is the command line interpreter for the PHP5 programming language. All examples in this tutorial are created on the console. I have intentionally skipped the web interface to make the examples simpler and focus only on PHP and MySQL.

If you don't already have MySQL installed, we must install it. 

$ sudo apt-get install mysql-server

This command installs the MySQL server and various other packages. While installing the package, we are prompted to enter a password for the MySQL root account.

Next, we are going to create a new database user and a new database. We use the mysql client. 

$ service mysql status
mysql start/running, process 1238

We check if the MySQL server is running. If not, we need to start the server. On Ubuntu Linux, this can be done with the service mysql start command. 

$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 30
Server version: 5.0.67-0ubuntu6 (Ubuntu)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| mysql              | 
+--------------------+
2 rows in set (0.00 sec)

We connect to the database using the root account. We show all available databases with the SHOW DATABASES statement. 

mysql> CREATE DATABASE testdb;
Query OK, 1 row affected (0.02 sec)

We create a new testdb database. We will use this database throughout the tutorial. 

mysql> CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'test623';
Query OK, 0 rows affected (0.00 sec)

mysql> USE testdb;
Database changed

mysql> GRANT ALL ON testdb.* TO 'testuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> quit;
Bye

We create a new database user. We grant all privileges to this user for all tables of the testdb database.

php5-mysql

In order to connect to the MySQL database from the PHP language, we must have php5-mysql package installed. This is a package name for Debian/Ubuntu Linux. On other derivatives the name might differ. This package has three modules. They are also called extensions.

The generic mysql module is the original PHP API for the MySQL database. Our tutorial covers this module. The API is procedural. This module does not provide all the latest features of the newer MySQL databases. The MySQL improved mysqli module is the recommended module for MySQL versions 4.1.3 or later. It provides both object oriented and procedural APIs. It has several benefits and enhancements over the original mysql module.

The pdo_mysql, PHP Data Objects module is a database abstraction layer for PHP applications. This module is beneficial if we write portable database PHP scripts.

First script

The following script is a simple PHP script. If this small script runs OK, we have everything needed installed. 

<?php

mysql_connect('localhost', 'testuser', 'test623') 
    or die("cannot connect to database\n");

echo mysql_get_server_info() . "\n"; 
echo phpversion() . "\n";

?>

We connect to the database and get some info about the MySQL server. 

mysql_connect('localhost', 'testuser', 'test623') 
    or die("cannot connect to database\n");

We use the mysql_connect() function to connect to the database. The function has 3 parameters. The first is the host, where the server is installed. The second and third parameters are the user name and user password. The die() function is used in error checking. If and only if the mysql_connect() function fails, the die() function is executed. It prints a message to the console and terminates the script. 

echo mysql_get_server_info() . "\n"; 
echo phpversion() . "\n";

The two functions print some info about the MySQL server version and PHP version. 

$ php version.php
5.1.41-3ubuntu12.6
5.3.2-1ubuntu4.5

On my system, I got the following output.

A more complex PHP script follows. 

<?php

mysql_connect('localhost', 'testuser', 'test623') 
    or die("cannot connect to database\n");

mysql_select_db("testdb") or die(mysql_error());

$stm = "SELECT VERSION()";

$result = mysql_query($stm)
    or die(mysql_error());

$row = mysql_fetch_row($result);

echo $row[0];
echo "\n";

mysql_close();

?>

We check for the version of the MySQL database. This time using an SQL query. 

mysql_select_db("testdb") or die(mysql_error());

The mysql_select_db() function selects a database on which we are going to execute queries. 

$stm = "SELECT VERSION()";

This is the SQL SELECT statement. It returns the version of the database. The VERSION() is a built-in MySQL function. 

$result = mysql_query($stm)
    or die(mysql_error());

The mysql_query() function executes an SQL query on the database. This is a SELECT query, so the result is a result set, containing some data. The mysql_error() function returns the error message from the last MySQL function, or '' (empty string) if no error occurred. 

$row = mysql_fetch_row($result);

We fetch a row from the result set. The $row variable is an array containing data. 

echo $row[0];
echo "\n";

We print the data from the array. We know from the nature of our query, that we have only one item it the array, the MySQL version string. 

mysql_close();

The mysql_close() function closes the connection to the database. Closing connection in our case is not necessary, as non-persistent open links are automatically closed at the end of the script's execution. However, it is a good programming practice. 

$ php version2.php
5.1.41-3ubuntu12.6

Output of the script on my system.

Creating and populating a table

In the first example, we will create a database table. In the second example, we will populate it with data. 

<?php

mysql_connect('localhost', 'testuser', 'test623') 
    or die("cannot connect to database\n");

mysql_select_db("testdb") or die(mysql_error());

$stm = "CREATE TABLE IF NOT EXISTS " . 
        "Writers(Id INT PRIMARY KEY AUTO_INCREMENT, Name VARCHAR(25))";

$ok = mysql_query($stm);

if (!$ok) {
   echo mysql_error();
   die("Cannot execute query. \n");
}

echo "Database Writers created successfully\n";

mysql_close();

?>

We create a Writers table with two columns. 

$stm = "CREATE TABLE IF NOT EXISTS " . 
        "Writers(Id INT PRIMARY KEY AUTO_INCREMENT, Name VARCHAR(25))";

This is the SQL statement to create the Writers table. 

$ok = mysql_query($stm);

We execute the query. In case of a CREATE TABLE statement, the mysql_query() function returns True on success or False on error. 

if (!$ok) {
   echo mysql_error();
   die("Cannot execute query. \n");
}

In case of an error, we print the error message and terminate the script. 

mysql> SHOW TABLES;
+------------------+
| Tables_in_testdb |
+------------------+
| Writers          |
+------------------+
1 row in set (0.00 sec)

We check in the mysql client program if the table was created.

This is the second script, in which we fill the table with data. 

<?php

mysql_connect('localhost', 'testuser', 'test623') 
    or die("cannot connect to database\n");

mysql_select_db("testdb") or die(mysql_error());

mysql_query("INSERT INTO Writers(Name) VALUES('Jack London')")
    or die(mysql_error());
mysql_query("INSERT INTO Writers(Name) VALUES('Honore de Balzac')")
    or die(mysql_error());
mysql_query("INSERT INTO Writers(Name) VALUES('Lion Feuchtwanger')")
    or die(mysql_error());
mysql_query("INSERT INTO Writers(Name) VALUES('Emile Zola')")
    or die(mysql_error());
mysql_query("INSERT INTO Writers(Name) VALUES('Truman Capote')")
    or die(mysql_error());

mysql_close();

?>

We use the INSERT SQL statement to insert 5 authors into the Writers table. 

mysql_query("INSERT INTO Writers(Name) VALUES('Jack London')")
    or die(mysql_error());

We insert a new writer into the table and check for possible error. 

mysql> SELECT * FROM Writers;
+----+-------------------+
| Id | Name              |
+----+-------------------+
|  1 | Jack London       |
|  2 | Honore de Balzac  |
|  3 | Lion Feuchtwanger |
|  4 | Emile Zola        |
|  5 | Truman Capote     |
+----+-------------------+
5 rows in set (0.00 sec)

After executing the script, we select data from the table.

Retrieving data

Now, that we have inserted some data into the database, we want to get it back. 

<?php

mysql_connect('localhost', 'testuser', 'test623') 
    or die("cannot connect to database\n");

mysql_select_db("testdb") or die(mysql_error());

$stm = "SELECT Id, Name From Writers";

$result = mysql_query($stm)
    or die(mysql_error());

while ($row = mysql_fetch_assoc($result)) {
    echo $row['Id'];
    echo ": ";
    echo $row['Name'];
    echo "\n";
}

mysql_close();

?>

In this example, we retrieve all data from the Writers table. 

$stm = "SELECT Id, Name From Writers";

This SQL statement selects Id, Name columns from all rows of the Writers table. 

$result = mysql_query($stm)
    or die(mysql_error());

We execute the query and retrieve the result set. 

while ($row = mysql_fetch_assoc($result)) {
    echo $row['Id'];
    echo ": ";
    echo $row['Name'];
    echo "\n";
}

We loop through the result set and print the data to the console. The mysql_fetch_assoc() function returns an associative array of strings that corresponds to the fetched row, or FALSE if there are no more rows. In other words, the function call returns a row from the result set. This row is in the form of an associative array. The column names are keys to the associative array. When there are no more rows in the result set, the function returns FALSE and the while loop terminates. 

$ php query.php
1: Jack London
2: Honore de Balzac
3: Lion Feuchtwanger
4: Emile Zola
5: Truman Capote

This is the output of the example.

There are several ways, how we can retrieve data. The following example provides another way to do it. 

<?php

mysql_connect('localhost', 'testuser', 'test623') 
    or die("cannot connect to database\n");

mysql_select_db("testdb") or die(mysql_error());

$stm = "SELECT Id, Name From Writers LIMIT 3";

$result = mysql_query($stm)
    or die(mysql_error());

$rows = mysql_num_rows($result);

for ($i = 0; $i < $rows; $i++) {
    $row = mysql_fetch_row($result);
    echo $row[0];
    echo ": ";
    echo $row[1];
    echo "\n";
}

mysql_close();

?>

We get first three rows from the Writers table. 

$stm = "SELECT Id, Name From Writers LIMIT 3";

This is the statement that limits the result set to three rows. 

$rows = mysql_num_rows($result);

The mysql_num_rows() function gets the number of rows from the result set. 

for ($i = 0; $i < $rows; $i++) {
    $row = mysql_fetch_row($result);
    echo $row[0];
    echo ": ";
    echo $row[1];
    echo "\n";
}

We use the for loop to iterate over the three returned rows. The mysql_fetch_row() function retrieves the row from the result set in the form of an enumerated array. 

$ php query2.php
1: Jack London
2: Honore de Balzac
3: Lion Feuchtwanger

Output.

In the following example, we show how to retrieve a specific row from a table. 

<?php

mysql_connect('localhost', 'testuser', 
     'test623', 'testdb') or die("cannot connect to database\n");

mysql_select_db("testdb") or die(mysql_error());

$name = "Jack London";

$stm = sprintf("SELECT Id, Name From Writers Where Name = '%s'", 
    mysql_real_escape_string($name));

$result = mysql_query($stm)
    or die(mysql_error());

while ($row = mysql_fetch_object($result)) {
    echo $row->Id;
    echo ": ";
    echo $row->Name;
    echo "\n";
}

mysql_close();

?>

Developers must take secutiry concerns into account when working with input from users. We must alwasy process the data sent from outside world. Check for validity of the data. 

$name = "Jack London";

In the script, we check, if we have "Jack London" in the Writers table. This value might come from an xml file or a web form. We will show, how to check it. 

$stm = sprintf("SELECT Id, Name From Writers Where Name = '%s'", 
    mysql_real_escape_string($name));

We build the SQL statement using the sprintf() function. We process the $name variable with the mysql_real_escape_string() function. This function escapes special characters in a string for use in an SQL statement. This prevents SQL injection attacks and data corruption. After the variable was processed, it is put into the SQL statement string. 

while ($row = mysql_fetch_object($result)) {
    echo $row->Id;
    echo ": ";
    echo $row->Name;
    echo "\n";
}

We fetch the data using the mysql_fetch_object() function. The function fetches a result row as an object. And we use the object notation to get the table columns. 

$ php query3.php 
1: Jack London

The output of the example. We found the author and printed the whole row to the console.

Escaping characters

We will have a small example demonstrating how to escape characters. There are some characters which are considered to be unsafe in a database environment. One of them is a single quote character. 

<?php

mysql_connect('localhost', 'testuser', 'test623') 
    or die("cannot connect to database\n");

mysql_select_db("testdb") or die(mysql_error());

$name = "O'Neill";
$name_es = mysql_real_escape_string($name);

$stm = "INSERT INTO Writers(Name) VALUES('$name_es')";

$result = mysql_query($stm)
    or die(mysql_error());

mysql_close();

?>

We insert a new author to the Writers table. Author's name is O'Neill. The name has an unsafe single quote character. 

$name_es = mysql_real_escape_string($name);

Thay is why we use the mysql_real_escape_string() function to escape this character. 

$stm = "INSERT INTO Writers(Name) VALUES('$name_es')";

$result = mysql_query($stm)
    or die(mysql_error());

We create the statement and execute it. 

mysql> SELECT * FROM Writers;
+----+-------------------+
| Id | Name              |
+----+-------------------+
|  1 | Jack London       |
|  2 | Honore de Balzac  |
|  3 | Lion Feuchtwanger |
|  4 | Emile Zola        |
|  5 | Truman Capote     |
|  6 | O'Neill           |
+----+-------------------+
6 rows in set (0.00 sec)

The name has been successfully written to the table.

Column headers

Next we will show, how to print column headers with the data from the database table. 

<?php

mysql_connect('localhost', 'testuser', 'test623') 
    or die("cannot connect to database\n");

mysql_select_db("testdb") or die(mysql_error());

$stm = "SELECT Id, Name From Writers LIMIT 5";
    
$result = mysql_query($stm)
    or die(mysql_error());

$num_fields = mysql_num_fields($result);

for ($i = 0; $i < $num_fields; $i++) {
    $field = mysql_fetch_field($result, $i);
    echo $field->name . " ";
}

echo "\n";

while ($row = mysql_fetch_object($result)) {
    echo $row->Id;
    echo ": ";
    echo $row->Name;
    echo "\n";
}

mysql_close();

?>

Again, we print the contents of the Writers table to the console. Now, we include the names of the columns too. 

$num_fields = mysql_num_fields($result);

The mysql_num_fields() function gets the number of fields from the result set. A field is a table column name. 

for ($i = 0; $i < $num_fields; $i++) {
    $field = mysql_fetch_field($result, $i);
    echo $field->name . " ";
}

Here we go through all the fields and print them to the console. To get a specific field name, we utilize the mysql_fetch_field() function. 

while ($row = mysql_fetch_object($result)) {
    echo $row->Id;
    echo ": ";
    echo $row->Name;
    echo "\n";
}

This loop prints the actual data. 

$ php columns.php
Id Name 
1: Jack London
2: Honore de Balzac
3: Lion Feuchtwanger
4: Emile Zola
5: Truman Capote

Ouput of the script.

Writing images

Some people prefer to put their images into the database, some prefer to keep them on the file system for their applications. Technical difficulties arise when we work with millions of images. Images are binary data. MySQL database has a special data type to store binary data called BLOB (Binary Large Object). 

mysql> CREATE TABLE Images(Id INT PRIMARY KEY AUTO_INCREMENT, Data MEDIUMBLOB);
Query OK, 0 rows affected (0.06 sec)

For this example, we create a new table called Images. 

<?php

mysql_connect('localhost', 'testuser', 'test623')
    or die("cannot connect to database\n");

mysql_select_db("testdb") or die("cannot select database");

$file = "woman.jpg";

$img = fopen($file, 'r') or die("cannot read image\n");
$data = fread($img, filesize($file));
$es_data = mysql_real_escape_string($data);
fclose($img);

$stm = "INSERT INTO Images(Data) Values('$es_data')";
    
mysql_query($stm)
    or die(mysql_error());

mysql_close();

?>

In the above script, we read a jpg image and insert it into the Images table. 

$file = "woman.jpg";

This is the image name, that we read from the filesystem and write into the database. It is located in the same directory as the script name. 

$img = fopen($file, 'r') or die("cannot read image\n");
$data = fread($img, filesize($file));

We open and read the image. The fread() function returns the data as string. 

$es_data = mysql_real_escape_string($data);

We escape unsafe characters. 

fclose($img);

We close the handle to the image file. 

$stm = "INSERT INTO Images(Data) Values('$es_data')";
    
mysql_query($stm)
    or die(mysql_error());

We insert the data to the newly created Images table.

Reading images

In the previous example, we have inserted an image into the database table. Now we are going to read the image back from the table. 

<?php

mysql_connect('localhost', 'testuser', 'test623') 
    or die("cannot connect to database\n");

mysql_select_db("testdb") or die("cannot select database");

$stm = "SELECT Data FROM Images LIMIT 1";
    
$result = mysql_query($stm)
    or die(mysql_error());

$row = mysql_fetch_row($result);

$file = "woman2.jpg";

$img = fopen($file, 'wb') or die("cannot open image\n");
$data = fwrite($img, $row[0]);
fclose($img);

mysql_close();

?>

We read one image from the Images table. 

$stm = "SELECT Data FROM Images LIMIT 1";

We select one record from the table. 

$row = mysql_fetch_row($result);

We fetch one row from the result set. There is only one row, containing the image data. 

$file = "woman2.jpg";

We will create a new image file name called "woman2.jpg". 

$img = fopen($file, 'wb') or die("cannot open image\n");

We open a writable binary file. 

$data = fwrite($img, $row[0]);

We write the data to the filesystem using the fwrite() function.

Now we should have an image called "woman2.jpg" in our current directory. We can check if it is the same image, that we have inserted into the table.

Transaction support

A transaction is an atomic unit of database operations against the data in one or more databases. The effects of all the SQL statements in a transaction can be either all committed to the database or all rolled back.

The MySQL database has different types of storage engines. The most common are the MyISAM and the InnoDB engines. The MyISAM is the default one. There is a trade-off between data security and database speed. The MyISAM tables are faster to process and they do not support transactions. On the other hand, the InnoDB tables are more safe against the data loss. They support transactions. They are slower to process. 

<?php

mysql_connect('localhost', 'testuser', 'test623') 
    or die("cannot connect to database\n");

mysql_select_db("testdb") or die(mysql_error());

$r1 = mysql_query("UPDATE Writers SET Name = 'Leo Tolstoy' WHERE Id = 1")
    or die(mysql_error());

$r2 = mysql_query("UPDATE Writers SET Name = 'Boris Pasternak' WHERE Id = 2")
    or die(mysql_error());

$r3 = mysql_query("UPDATE Writer SET Name = 'Leonid Leonov' WHERE Id = 3")
    or die(mysql_error());

mysql_close();

?>

In this script, we try to update three rows. The storage engine of the table is MyISAM. 

$r1 = mysql_query("UPDATE Writers SET Name = 'Leo Tolstoy' WHERE Id = 1")
    or die(mysql_error());

$r2 = mysql_query("UPDATE Writers SET Name = 'Boris Pasternak' WHERE Id = 2")
    or die(mysql_error());

Here we want to change names of authors for rows 1 and 2. 

$r3 = mysql_query("UPDATE Writer SET Name = 'Leonid Leonov' WHERE Id = 3")
    or die(mysql_error());

There is an error in the SQL statement. There is no Writer table. 

$ php update.php
Table 'testdb.Writer' doesn't exist

mysql> SELECT * FROM Writers;
+----+-------------------+
| Id | Name              |
+----+-------------------+
|  1 | Leo Tolstoy       |
|  2 | Boris Pasternak   |
|  3 | Lion Feuchtwanger |
|  4 | Emile Zola        |
|  5 | Truman Capote     |
|  6 | O'Neill           |
+----+-------------------+
6 rows in set (0.00 sec)

Running the script gives an error. But as we see, the first two rows already were changed.

In the last example of this tutorial, we are going to recreate the Writers table. This time, the table will be of InnoDB type. InnoDB MySQL database tables support transactions. 

DROP TABLE Writers;

CREATE TABLE IF NOT EXISTS Writers(Id INT PRIMARY KEY AUTO_INCREMENT, 
    Name VARCHAR(25)) ENGINE=INNODB;

INSERT INTO Writers(Name) VALUES('Jack London');
INSERT INTO Writers(Name) VALUES('Honore de Balzac');
INSERT INTO Writers(Name) VALUES('Lion Feuchtwanger');
INSERT INTO Writers(Name) VALUES('Emile Zola');
INSERT INTO Writers(Name) VALUES('Truman Capote');

This is writers.sql file. It is used to recreate the Writers table. 

mysql> source writers.sql
Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected (0.10 sec)

Query OK, 1 row affected (0.02 sec)

Query OK, 1 row affected (0.03 sec)

Query OK, 1 row affected (0.02 sec)

Query OK, 1 row affected (0.02 sec)

Query OK, 1 row affected (0.02 sec)

We can use the source commnad to load and execute the sql script. 

<?php

mysql_connect('localhost', 'testuser', 'test623') 
    or die("cannot connect to database\n");

mysql_select_db("testdb") or die(mysql_error());

mysql_query("SET AUTOCOMMIT=0");
mysql_query("START TRANSACTION");

$r1 = mysql_query("DELETE FROM Writers WHERE Id = 3")
    or die(mysql_error());

$r2 = mysql_query("DELETE FROM Writers WHERE Id = 4")
    or die(mysql_error());

$r3 = mysql_query("DELETE FROM Writer WHERE Id = 5")
    or die(mysql_error());

if ($r1 and $r2 and $r3) {
    mysql_query("COMMIT");
} else {
    mysql_query("ROLLBACK");
}

mysql_close();

?>

Now, we are going to execute the above script. We want to delete three rows from the table. The third SQL statement has an error. 

mysql_query("START TRANSACTION");

The START TRANSACTION statement starts a new transaction. All changes must be made permanent with the COMMIT statement or ignored with the ROLLBACK statement. 

if ($r1 and $r2 and $r3) {
    mysql_query("COMMIT");
} else {
    mysql_query("ROLLBACK");
}

We commit the statements only if all SQL statements three returned True. Otherwise, we roll them back. In our case the $r3 variable holds False, so the statements are not made permanent and the rows are not deleted from the table. 

$ php transaction.php
Table 'testdb.Writer' doesn't exist

mysql> SELECT * FROM Writers;
+----+-------------------+
| Id | Name              |
+----+-------------------+
|  1 | Jack London       |
|  2 | Honore de Balzac  |
|  3 | Lion Feuchtwanger |
|  4 | Emile Zola        |
|  5 | Truman Capote     |
+----+-------------------+
5 rows in set (0.00 sec)

The error occurred before we have committed the changes to the database. The ROLLBACK statement was called and no deletions took place.

This was the MySQL PHP tutorial. You might be also interested in MySQL C API tutorial, MySQL Python tutorial or MySQL Visual Basic tutorial.



Home ‡ Top of Page
ZetCode last modified September 29, 2010 © 2007 - 2011 Jan Bodnar