Question

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 , MongoDB PHP tutorial , and PostgreSQL PHP 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 is a leading open source database management system. It is a multiuser, 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, and PHP. Currently MySQL is owned by Oracle. MySQL database is available on most important OS platforms. It runs on BSD Unix, Linux, Windows, or Mac OS. 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 , and 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.

$ sudo service mysql start

The above command is a common way to start MySQL if we have installed the MySQL database from packages.

$ sudo -b /usr/local/mysql/bin/mysqld_safe

The above command starts MySQL server using the MySQL server startup script. The way how we start a MySQL server might be different. It depends whether we have installed MySQL from sources or from packages and also on the Linux distro. For further information consult MySQL first steps or your Linux distro information.

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

$ 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 use the mysql monitor client application to connect to the server. We connect to the database using the root account. We show all available databases with the SHOW DATABASES statement.

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

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

mysql> CREATE USER user12@localhost IDENTIFIED BY '34klq*';
Query OK, 0 rows affected (0.00 sec)

mysql> USE mydb;
Database changed

mysql> GRANT ALL ON mydb.* to user12@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 mydb 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.

• mysql module
• mysqli module
• pdo_mysql

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

$host = "localhost"; 
$user = "user12"; 
$pass = "34klq*"; 

$r = mysql_connect($host, $user, $pass);

if (!$r) {
  echo "Could not connect to server\n";
  trigger_error(mysql_error(), E_USER_ERROR);
} else {
  echo "Connection established\n"; 
}

echo mysql_get_server_info() . "\n"; 

mysql_close();

?>

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

$host = "localhost"; 
$user = "user12"; 
$pass = "34klq*"; 

These are three variables holding the host name, user name and password. The variables are needed when connecting to the MySQL database.

$r = mysql_connect($host, $user, $pass);

We use the mysql_connect() function to connect to the database. The function returns a boolean value indicating whether the connection was successfully created or not. 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.

if (!$r) {
  echo "Could not connect to server\n";
  trigger_error(mysql_error(), E_USER_ERROR);
} else {
  echo "Connection established\n"; 
}

Now we check the $r variable. If it contains a boolean false, the connection to the database was not created. We call the trigger_error() function to generate an error message. The first generic message goes to the user. The more specific error message generated with the trigger_error() function is logged.

echo mysql_get_server_info() . "\n"; 

The mysql_get_server_info() returns the MySQL server version.

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 version.php
5.1.41-3ubuntu12.6
5.3.2-1ubuntu4.5

On my system, I got the following output.

We have a similar script.

<?php

$host = "localhost"; 
$user = "user12"; 
$pass = "34klq*"; 

$r = mysql_connect($host, $user, $pass);

if (!$r) {
  echo "Could not connect to server\n";
  trigger_error(mysql_error(), E_USER_ERROR);
} else {
  echo "Connection established\n"; 
}

$query = "SELECT VERSION()";

$rs = mysql_query($query);

if (!$rs) {
  echo "Could not execute query: $query\n";
  trigger_error(mysql_error(), E_USER_ERROR);
} else {
  echo "Query: $query executed\n"; 
}

$row = mysql_fetch_row($rs);

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

mysql_close();

?>

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

$query = "SELECT VERSION()";

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

$rs = mysql_query($query);

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.

if (!$rs) {
  echo "Could not execute query: $query\n";
  trigger_error(mysql_error(), E_USER_ERROR);
} else {
  echo "Query: $query executed\n"; 
}

In case of an error we generate an error message. Otherwise we print the SQL query executed.

$row = mysql_fetch_row($rs);

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

echo "Version: $row[0]\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.

$ php version2.php
Connection established
Query: SELECT VERSION() executed
Version: 5.1.62-0ubuntu0.11.10.1

Output of the script on our system.

Creating and populating a table

Next we are going to create a database table and fill it with data.

<?php

$host = "localhost"; 
$user = "user12"; 
$pass = "34klq*"; 
$db = "mydb"; 

function execute_query($query) {

  $r = mysql_query($query);

  if (!$r) {
    echo "Cannot execute query: $query\n";
    trigger_error(mysql_error()); 
  } else {
    echo "Query: $query executed\n"; 
  }
}

$r = mysql_connect($host, $user, $pass);

if (!$r) {
  echo "Could not connect to server\n";
  trigger_error(mysql_error(), E_USER_ERROR);
} else {
  echo "Connection established\n"; 
}

$r2 = mysql_select_db($db);

if (!$r2) {
  echo "Cannot select database\n";
  trigger_error(mysql_error(), E_USER_ERROR); 
} else {
  echo "Database selected\n";
}

$query = "DROP TABLE IF EXISTS Cars"; 
execute_query($query);

$query = "CREATE TABLE Cars(Id INT PRIMARY KEY, Name TEXT, 
  Price INT) ENGINE=InnoDB";
execute_query($query);

$query = "INSERT INTO Cars VALUES(1,'Audi',52642)";
execute_query($query);

$query = "INSERT INTO Cars VALUES(2,'Mercedes',57127)";
execute_query($query);

$query = "INSERT INTO Cars VALUES(3,'Skoda',9000)";
execute_query($query);

$query = "INSERT INTO Cars VALUES(4,'Volvo',29000)";
execute_query($query);

$query = "INSERT INTO Cars VALUES(5,'Bentley',350000)";
execute_query($query);

$query = "INSERT INTO Cars VALUES(6,'Citroen',21000)";
execute_query($query);

$query = "INSERT INTO Cars VALUES(7,'Hummer',41400)";
execute_query($query);

$query = "INSERT INTO Cars VALUES(8,'Volkswagen',21600)";
execute_query($query);

mysql_close();

?>

In the above code example, we create a Cars table with 8 rows.

function execute_query($query) {

  $r = mysql_query($query);

  if (!$r) {
    echo "Cannot execute query: $query\n";
    trigger_error(mysql_error()); 
  } else {
    echo "Query: $query executed\n"; 
  }
}

We have created a custom execute_query() function which will be called for each INSERT statement.

$r2 = mysql_select_db($db);

Before we can work with database tables, we must select a database. A database is selected with a mysql_select_db() function.

if (!$r2) {
  echo "Cannot select database\n";
  trigger_error(mysql_error(), E_USER_ERROR); 
} else {
  echo "Database selected\n";
}

Error handling for the database selection process.

$query = "DROP TABLE IF EXISTS Cars"; 
execute_query($query);

The first query drops a Cars table if it already exists.

$query = "CREATE TABLE Cars(Id INT PRIMARY KEY, Name TEXT, 
  Price INT) ENGINE=InnoDB";
execute_query($query);

This is the SQL statement to create the Cars table.

$query = "INSERT INTO Cars VALUES(1,'Audi',52642)";
execute_query($query);

A car is inserted into the table.

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

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

$ php create_fill.php
Connection established
Database selected
Query: DROP TABLE IF EXISTS Cars executed
Query: CREATE TABLE Cars(Id INT PRIMARY KEY, Name TEXT, 
  Price INT) ENGINE=InnoDB executed
Query: INSERT INTO Cars VALUES(1,'Audi',52642) executed
Query: INSERT INTO Cars VALUES(2,'Mercedes',57127) executed
Query: INSERT INTO Cars VALUES(3,'Skoda',9000) executed
Query: INSERT INTO Cars VALUES(4,'Volvo',29000) executed
Query: INSERT INTO Cars VALUES(5,'Bentley',350000) executed
Query: INSERT INTO Cars VALUES(6,'Citroen',21000) executed
Query: INSERT INTO Cars VALUES(7,'Hummer',41400) executed
Query: INSERT INTO Cars VALUES(8,'Volkswagen',21600) executed

Executing the create_fill.php script.

mysql> SELECT * FROM Cars;
+----+------------+--------+
| Id | Name     | Price  |
+----+------------+--------+
|  1 | Audi     |  52642 |
|  2 | Mercedes   |  57127 |
|  3 | Skoda    |   9000 |
|  4 | Volvo    |  29000 |
|  5 | Bentley  | 350000 |
|  6 | Citroen  |  21000 |
|  7 | Hummer   |  41400 |
|  8 | Volkswagen |  21600 |
+----+------------+--------+
8 rows in set (0.00 sec)

The data inserted into the Cars table.

Retrieving data

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

<?php

$host = "localhost"; 
$user = "user12"; 
$pass = "34klq*"; 
$db = "mydb";

$r = mysql_connect($host, $user, $pass);

if (!$r) {
  echo "Could not connect to server\n";
  trigger_error(mysql_error(), E_USER_ERROR);
} else {
  echo "Connection established\n"; 
}

$r2 = mysql_select_db($db);

if (!$r2) {
  echo "Cannot select database\n";
  trigger_error(mysql_error(), E_USER_ERROR); 
} else {
  echo "Database selected\n";
}

$query = "SELECT * FROM Cars LIMIT 5";

$rs = mysql_query($query);

if (!$rs) {
  echo "Could not execute query: $query";
  trigger_error(mysql_error(), E_USER_ERROR); 
} else {
  echo "Query: $query executed\n";
} 

while ($row = mysql_fetch_assoc($rs)) {
  echo $row['Id'] . " " . $row['Name'] . " " . $row['Price'] . "\n";
}

mysql_close();

?>

In this example, we retrieve five rows from the Cars table.

$query = "SELECT * FROM Cars LIMIT 5";

This SQL statement selects 5 rows from the Cars table.

$rs = mysql_query($query);

We execute the query with the mysql_query() function and retrieve the result set.

if (!$rs) {
  echo "Could not execute query: $query";
  trigger_error(mysql_error(), E_USER_ERROR); 
} else {
  echo "Query: $query executed\n";
} 

If the query did not succeed, we generate an error message.

while ($row = mysql_fetch_assoc($rs)) {
  echo $row['Id'] . " " . $row['Name'] . " " . $row['Price'] . "\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
Connection established
Database selected
Query: SELECT * FROM Cars LIMIT 5 executed
1 Audi 52642
2 Mercedes 57127
3 Skoda 9000
4 Volvo 29000
5 Bentley 350000

This is the output of the example.

In the second example, we will fetch data with the mysql_fetch_row() function.

<?php

$host = "localhost"; 
$user = "user12"; 
$pass = "34klq*"; 
$db = "mydb";

$r = mysql_connect($host, $user, $pass);

if (!$r) {
  echo "Could not connect to server\n";
  trigger_error(mysql_error(), E_USER_ERROR);
} else {
  echo "Connection established\n"; 
}

$r2 = mysql_select_db($db);

if (!$r2) {
  echo "Cannot select database\n";
  trigger_error(mysql_error(), E_USER_ERROR); 
} else {
  echo "Database selected\n";
}

$query = "SELECT Id, Name, Price From Cars LIMIT 5";

$rs = mysql_query($query);

if (!$rs) {
  echo "Could not execute query: $query";
  trigger_error(mysql_error(), E_USER_ERROR); 
} else {
  echo "Query: $query executed\n";
} 

$nrows = mysql_num_rows($rs);

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

mysql_close();

?>

We get the first 5 rows from the Cars table.

$nrows = mysql_num_rows($rs);

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

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

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

$ php query.php
Connection established
Query: SELECT * FROM Cars LIMIT 5 executed
1 Audi 52642
2 Mercedes 57127
3 Skoda 9000
4 Volvo 29000
5 Bentley 350000

Output.

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

<?php

$host = "localhost"; 
$user = "user12"; 
$pass = "34klq*"; 
$db = "mydb";

$r = mysql_connect($host, $user, $pass);

if (!$r) {
  echo "Could not connect to server\n";
  trigger_error(mysql_error(), E_USER_ERROR);
} else {
  echo "Connection established\n"; 
}

$r2 = mysql_select_db($db);

if (!$r2) {
  echo "Cannot select database\n";
  trigger_error(mysql_error(), E_USER_ERROR); 
} else {
  echo "Database selected\n";
}

$name = "Volkswagen";

$query = sprintf("SELECT Id, Name, Price From Cars Where Name = '%s'", 
  mysql_real_escape_string($name));

$rs = mysql_query($query);

if (!$rs) {
  echo "Could not execute query: $query\n";
  trigger_error(mysql_error(), E_USER_ERROR); 
} else {
  echo "Query: $query executed\n";
} 

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

mysql_close();

?>

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

$name = "Volkswagen";

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

$query = sprintf("SELECT Id, Name, Price From Cars 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($rs)) {
  echo $row->Id;
  echo " ";
  echo $row->Name;
  echo " ";
  echo $row->Price;
  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
Connection established
Database selected
Query: SELECT Id, Name, Price From Cars Where Name = 'Volkswagen' executed
8 Volkswagen 21600

The output of the example. We found the car 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.

mysql> CREATE TABLE IF NOT EXISTS Authors(Id INT PRIMARY KEY AUTO_INCREMENT, 
  ->   Name VARCHAR(25)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.09 sec)

For the example, we create an Authors table.

<?php

$host = "localhost"; 
$user = "user12"; 
$pass = "34klq*"; 
$db = "mydb"; 

$r = mysql_connect($host, $user, $pass);

if (!$r) {
  echo "Could not connect to server\n";
  trigger_error(mysql_error(), E_USER_ERROR);
} else {
  echo "Connection established\n"; 
}

$r2 = mysql_select_db($db);

if (!$r2) {
  echo "Cannot select database\n";
  trigger_error(mysql_error(), E_USER_ERROR); 
} else {
  echo "Database selected\n";
}

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

$query = "INSERT INTO Authors(Name) VALUES('$name_es')";
$rs = mysql_query($query);

if (!$rs) {
  echo "Could not execute query: $query\n";
  trigger_error(mysql_error(), E_USER_ERROR); 
} else {
  echo "Query: $query executed\n";
} 

mysql_close();

?>

We insert a new author to the Authors table. The name of the author 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.

$query = "INSERT INTO Authors(Name) VALUES('$name_es')";
$rs = mysql_query($query);

We create the statement and execute it.

mysql> SELECT * FROM Authors;
+----+---------+
| Id | Name  |
+----+---------+
|  1 | O'Neill |
+----+---------+
1 row 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

$host = "localhost"; 
$user = "user12"; 
$pass = "34klq*"; 
$db = "mydb"; 

$r = mysql_connect($host, $user, $pass);

if (!$r) {
  echo "Could not connect to server\n";
  trigger_error(mysql_error(), E_USER_ERROR);
} else {
  echo "Connection established\n"; 
}

$r2 = mysql_select_db($db);

if (!$r2) {
  echo "Cannot select database\n";
  trigger_error(mysql_error(), E_USER_ERROR); 
} else {
  echo "Database selected\n";
}

$query = "SELECT * From Cars LIMIT 8";
  
$rs = mysql_query($query);

if (!$rs) {
  echo "Could not execute query: $query";
  trigger_error(mysql_error(), E_USER_ERROR); 
} else {
  echo "Query: $query executed\n";
} 

$cname1 = mysql_fetch_field($rs, 0);
$cname2 = mysql_fetch_field($rs, 1);
$cname3 = mysql_fetch_field($rs, 2);

printf("%3s %-11s %8s\n", $cname1->name, $cname2->name, 
  $cname3->name);

while ($row = mysql_fetch_row($rs)) {
  printf("%3s %-11s %8s\n", $row[0], $row[1], $row[2]);
}

mysql_close();

?>

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

$cname1 = mysql_fetch_field($rs, 0);
$cname2 = mysql_fetch_field($rs, 1);
$cname3 = mysql_fetch_field($rs, 2);

To get a specific field name, we utilize the mysql_fetch_field() function. The function return an object containing column information.

printf("%3s %-11s %8s\n", $cname1->name, $cname2->name, 
  $cname3->name);

The column names are printed and formatted. The name property contains the column name.

$ php columns.php
Connection established
Database selected
Query: SELECT * From Cars LIMIT 8 executed
 Id Name       Price
  1 Audi       52642
  2 Mercedes     57127
  3 Skoda       9000
  4 Volvo      29000
  5 Bentley     350000
  6 Citroen    21000
  7 Hummer     41400
  8 Volkswagen   21600

Ouput of the script.

Fields, rows

The following script counts the number of fields/columns and rows returned by a query.

<?php

$host = "localhost"; 
$user = "user12"; 
$pass = "34klq*"; 
$db = "mydb";

$r = mysql_connect($host, $user, $pass);

if (!$r) {
  echo "Could not connect to server\n";
  trigger_error(mysql_error(), E_USER_ERROR);
} else {
  echo "Connection established\n"; 
}

$r2 = mysql_select_db($db);

if (!$r2) {
  echo "Cannot select database\n";
  trigger_error(mysql_error(), E_USER_ERROR); 
} else {
  echo "Database selected\n";
}

$query = "SELECT * FROM Cars WHERE Id IN (1, 2, 3)";

$rs = mysql_query($query);

if (!$rs) {
  echo "Could not execute query: $query\n";
  trigger_error(mysql_error(), E_USER_ERROR); 
} else {
  echo "Query: $query executed\n";
} 

echo "We have " . mysql_num_fields($rs) . " fields\n";
echo "We have " . mysql_num_rows($rs) . " rows\n";

print_r(mysql_fetch_row($rs));

mysql_close();

?>

We select three rows from the Cars table. We count the number of rows and columns returned by a query.

$query = "SELECT * FROM Cars WHERE Id IN (1, 2, 3)";

This is the query to be executed. It selects first three rows from the Cars table.

echo "We have " . mysql_num_fields($rs) . " fields\n";

The mysql_num_fields() returns the number of fields returned by a query.

echo "We have " . mysql_num_rows($rs) . " rows\n";

The mysql_num_rows() returns the number of rows returned by a query.

print_r(mysql_fetch_row($rs));

We print the contents of the array.

$ php fields_rows.php
Connection established
Database selected
Query: SELECT * FROM Cars WHERE Id IN (1, 2, 3) executed
We have 3 fields
We have 3 rows
Array
(
  [0] => 1
  [1] => Audi
  [2] => 52642
)

Running 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 lots 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

$host = "localhost"; 
$user = "user12"; 
$pass = "34klq*"; 
$db = "mydb";

$r = mysql_connect($host, $user, $pass);

if (!$r) {
  echo "Could not connect to server\n";
  trigger_error(mysql_error(), E_USER_ERROR);
} else {
  echo "Connection established\n"; 
}

$r2 = mysql_select_db($db);

if (!$r2) {
  echo "Cannot select database\n";
  trigger_error(mysql_error(), E_USER_ERROR); 
} else {
  echo "Database selected\n";
}

$file = "woman.jpg";

$img = fopen($file, 'r');

if (!$img) {
  echo "Cannot open file for writing\n";
  trigger_error("Cannot open file for writing\n", E_USER_ERROR);
} 

$data = fread($img, filesize($file));

if (!$data) {
  echo "Cannot read image data\n";
  trigger_error("Cannot read image data\n", E_USER_ERROR);
}

$es_data = mysql_real_escape_string($data);
fclose($img);

$query = "INSERT INTO Images(Id, Data) Values(1, '$es_data')";
  
$rs = mysql_query($query);

if (!$rs) {
  echo "Could not execute query: $query";
  trigger_error(mysql_error(), E_USER_ERROR); 
} else {
  echo "Query successfully executed\n";
} 

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');

if (!$img) {
  echo "Cannot open file for writing\n";
  trigger_error("Cannot open file for writing\n", E_USER_ERROR);
} 

$data = fread($img, filesize($file));

if (!$data) {
  echo "Cannot read image data\n";
  trigger_error("Cannot read image data\n", E_USER_ERROR);
}

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.

$query = "INSERT INTO Images(Id, Data) Values(1, '$es_data')";
  
$rs = mysql_query($query);

if (!$rs) {
  echo "Could not execute query: $query";
  trigger_error(mysql_error(), E_USER_ERROR); 
} else {
  echo "Query successfully executed\n";
} 

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

$host = "localhost"; 
$user = "user12"; 
$pass = "34klq*"; 
$db = "mydb";

$r = mysql_connect($host, $user, $pass);

if (!$r) {
  echo "Could not connect to server\n";
  trigger_error(mysql_error(), E_USER_ERROR);
} else {
  echo "Connection established\n"; 
}

$r2 = mysql_select_db($db);

if (!$r2) {
  echo "Cannot select database\n";
  trigger_error(mysql_error(), E_USER_ERROR); 
} else {
  echo "Database selected\n";
}

$query = "SELECT Data FROM Images WHERE Id=1";
  
$rs = mysql_query($query);

if (!$rs) {
  echo "Could not execute query: $query";
  trigger_error(mysql_error(), E_USER_ERROR); 
} else {
  echo "Query: $query executed\n";
} 

$row = mysql_fetch_row($rs);

$file = "woman2.jpg";

$img = fopen($file, 'wb');

if (!$img) {
  echo "Cannot open file for writing\n";
  trigger_error("Cannot open file for writing\n", E_USER_ERROR);
} 

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

if (!$r3) {
  echo "Cannot write image to file\n";
  trigger_error("Cannot write image to file\n", E_USER_ERROR);
} 

fclose($img);

mysql_close();

?>

We read one image from the Images table.

$query = "SELECT Data FROM Images WHERE Id=1";

We select one record from the table.

$row = mysql_fetch_row($rs);

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');

if (!$img) {
  echo "Cannot open file for writing\n";
  trigger_error("Cannot open file for writing\n", E_USER_ERROR);
} 

We open a writable binary file.

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

if (!$r3) {
  echo "Cannot write image to file\n";
  trigger_error("Cannot write image to file\n", E_USER_ERROR);
} 

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