How to use PHP with MySQL: the complete tutorial (with examples)

This is the definitive, step-by-step guide to learn how to use PHP with MySQL.

If you want to:

• learn how PHP and MySQL work together
• learn how to connect to a MySQL server with PHP and execute SQL queries properly
• look at concrete examples using both MySQLi and PDO

Then this is the tutorial you are looking for.

(Looking for an advanced guide about SQL security? Take a look at my SQL injection guide).

Ready to learn how to use PHP with MySQL?

Let’s dive in. 

TABLE OF CONTENTS

PHP AND MYSQL: AN INTRODUCTION

So, what is MySQL and why do you need it?

It all comes down to a simple fact:

An e-commerce needs to store the list of products and the orders from its clients, a blog needs to store its posts’ content, and so on.

Back-end languages like PHP and Python cannot “store” any information, except for some small temporary information saved in Sessions.

Therefore, web applications need a storage space where to keep all this data and where to read it from when needed.

This is what databases like MySQL are used for.

The vast majority of web applications rely on relational databases like MySQL, PostreSQL and Oracle.

You can think of a relational database as a collection of tables linked to each other. Data is stored inside these tables as rows.

You will see how they look like in a minute.

MySQL is by far the most used database for dynamic web sites (databases are also called DBMS, database management systems).

Despite lacking some advanced features, it gained a lot of popularity for its open source license and its high performance for simple operations.

Many popular web sites like WordPress blogs and Wikipedia rely on MySQL (or some of its forks like MariaDB).

Now, you are probably wondering:

“How can I use a MySQL database in my PHP application?”

Don’t worry, we’ll get there in a bit.

But first, let’s make sure you have everything you need to get started. 

HOW TO GET STARTED

The best way to get started is to install a local development environment like XAMPP on your computer.

That way, you can do all your tests and exercises with PHP and MySQL locally.

Follow the steps from the Getting started chapter of my my How to learn PHP guide:

>> PHP in practice: getting started

When you’re done, start the web server and the MySQL server and open a web browser.

If you are using XAMPP, you can start Apache (the web server) and MySQL from the XAMPP Control Panel clicking on their Start buttons:

“All right! Now, how can I connect to the MySQL server?”

You need an SQL client.

You can use a stand alone application like the MySQL Workbench, a command line tool (useful when working on a remote server through an SSH connection) or a web application like phpMyAdmin.

phpMyAdmin is one of the most popular choices. It’s easy to use and you can run it from your browser like any other web application.

All PHP development environments include phpMyAdmin by default. To access it, you usually need to open the following URL:

http://localhost/phpmyadmin/

If you are using XAMPP, you can also get there by clicking the MySQL Admin button in the Control Panel:

Depending on its configuration, phpMyAdmin will let you access the database directly or will ask you for username and password. In that case, check the development environment documentation (you can try using root / root, a common default account).

When you’re in, you will see an interface like this one:

OK, BUT… HOW DOES A DATABASE WORK?

You can think of a MySQL installation as a pool of directories. These directories are called databases or schemas.

I usually prefer to use the term schema, because database can easily be confused with the whole installation.

Each of these schemas contains one or more tables. A table has one or more columns (or fields).

Finally, tables contain data as rows.

On the left side of the phpMyAdmin interface you can see the list of the schemas currently available on the MySQL installation:

Usually, each web application uses its own schema to store its data.

For example, phpMyAdmin itself stores its own data inside the schema called phpmyadmin (you can see it in the above screenshot).

Now it’s your turn:

For this tutorial, I want you to create create your own schema.

You will also create your own MySQL account. You will use this account to connect to MySQL from your PHP scripts.

I made a video that shows you how to:

1. Create your new schema called “mySchema”
2. Create your new account with username “myUser” and password “myPasswd”
3. Give your account the privileges to work on your new schema

Here it is:

Can’t see the video? Here are the steps:

Now you have your own schema (called “mySchema”) and your own account (“myUser” with password “myPasswd”).

Congratulations, you’re ready to go!

You’re ready for the next step: how to connect to MySQL with PHP. 

THE PHP MySQL EXTENSIONS: MySQLi AND PDO

 HOW CAN YOU CONNECT TO MYSQL WITH PHP?

PHP comes with two extensions for connecting to MySQL databases: MySQLi and PDO.

MySQLi is a specific driver for MySQL databases, while PDO is a more general purpose driver supporting different database types.

Both extensions provide prepared statements to protect your code from SQL injection attacks. MySQLi provides escaping too.

“Ok Alex, but which one do I need to use?”

Both are fine, so you can pick the one you like the most.

MySQLi is probably the easiest of the two, and it provides an OOP-style syntax as well as a simpler procedural-style syntax.

Anyway, this tutorial will help you understand the differences between MySQLi and PDO.

In fact, you will find examples with:

• MySQLi with procedural-style syntax,
• MySQLi with OOP-style syntax,
• and PDO.

All right:

Now it’s time to start writing some PHP code. 

HOW TO CONNECT TO MYSQL WITH PHP

How can you connect to your MySQL server using the PHP database extensions? 

Here’s a concrete example.

First, create a new PHP file and name it “db_inc.php” (or any name you prefer).

This script will take care of the database connection.

The advantage of having a separate database connection script is clear: every time you need to access the database, you can just include that file instead of writing all the connection code again and again.

Here is how to connect to your MySQL server using the MySQLi extension, procedural-style:

<?php
/* Host name of the MySQL server */
$host = 'localhost';
/* MySQL account username */
$user = 'myUser';
/* MySQL account password */
$passwd = 'myPasswd';
/* The schema you want to use */
$schema = 'mySchema';
/* Connection with MySQLi, procedural-style */
$mysqli = mysqli_connect($host, $user, $passwd, $schema);
/* Check if the connection succeeded */
if (!$mysqli)
{
   echo 'Connection failed<br>';
   echo 'Error number: ' . mysqli_connect_errno() . '<br>';
   echo 'Error message: ' . mysqli_connect_error() . '<br>';
   die();
}
echo 'Successfully connected!<br>';

The mysqli_connect() function connects to a MySQL server (argument #1 given as hostname or IP address; in this case, localhost  means your local computer) using the provided username and password (arguments #2 and #3).

You can also provide a default schema (or default database) as argument #4.

While not mandatory, it’s usually a good idea to do so. The above example uses the schema you made before (“mySchema”).

mysqli_connect() returns a connection resource variable.

It’s a special variable linked to the connected MySQL server. You will need to provide this resource to the other mysqli_* functions.

If a problem occurs during the connection (for example, if the password is wrong or the server is offline), FALSE is returned instead.

In that case, you can check the connection error code and message using the mysqli_connect_errno() and mysqli_connect_error() functions like in the example.

Now, let’s see the same example using MySQLi OOP-style syntax.

I suggest you create a new PHP file and name it “db_inc_oop.php”,  so you can keep using the one you made before when using the procedural-style MySQLi syntax.

Here’s the code:

<?php
/* Host name of the MySQL server */
$host = 'localhost';
/* MySQL account username */
$user = 'myUser';
/* MySQL account password */
$passwd = 'myPasswd';
/* The schema you want to use */
$schema = 'mySchema';
/* Connection with MySQLi, OOP-style */
$mysqli = new mysqli($host, $user, $passwd, $schema);
/* Check if the connection succeeded */
if (!is_null($mysqli->connect_error))
{
   echo 'Connection failed<br>';
   echo 'Error number: ' . $mysqli->connect_errno . '<br>';
   echo 'Error message: ' . $mysqli->connect_error . '<br>';
   die();
}
echo 'Successfully connected!<br>';

Here, the MySQLi class constructor connects to the database just like mysqli_connect() does.

However, the constructor returns a MySQLi object instead of a resource variable.

To check whether a connection error occurred, you need to check if the connect_error class attribute is not NULL.

If it is NULL, it means no errors occurred. Otherwise, connect_error will contain the error message and the connect_errno attribute will contain the error code.

(Again, remember not to output these errors when in production mode).

MySQLi does not throw exceptions on errors by default, but it can be configured to do so.

You will see how you can enable exceptions in the “Error handling” chapter.

Now let’s move on to PDO.

Again, create a new script and name it “db_inc_pdo.php”.

Here is the connection example (remember that PDO supports OOP only): 

<?php
/* Host name of the MySQL server */
$host = 'localhost';
/* MySQL account username */
$user = 'myUser';
/* MySQL account password */
$passwd = 'myPasswd';
/* The schema you want to use */
$schema = 'mySchema';
/* The PDO object */
$pdo = NULL;
/* Connection string, or "data source name" */
$dsn = 'mysql:host=' . $host . ';dbname=' . $schema;
/* Connection inside a try/catch block */
try
{  
   /* PDO object creation */
   $pdo = new PDO($dsn, $user,  $passwd);
   
   /* Enable exceptions on errors */
   $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch (PDOException $e)
{
   /* If there is an error an exception is thrown */
   echo 'Connection failed<br>';
   echo 'Error number: ' . $e->getCode() . '<br>';
   echo 'Error message: ' . $e->getMessage() . '<br>';
   die();
}
echo 'Successfully connected!<br>';

The PDO class constructor, on line 25, takes three arguments:

1. a connection string called “data source name” (or DSN) that sets the database type, the hostname and the default schema;
2. the username;
3. the password.

You need to specify mysql: inside the connection string because PDO can work with different database types.

For example, if you were connecting to a PostgreSQL database, the connection string would have begun with pgsql:

PDO throws exceptions on connection errors, which makes error handling a bit different from the MySQLi example. You need to enclose the connection code inside a try block and use a catch block to check if an exception has been thrown.

For example:

If set a wrong password and then you execute the PHP script from your browser, you will get this:

In order to enable exceptions on query errors too, you need to set the Error reporting mode to Exception using the PDO::setAttribute(). This is done on line 28 in the code above.

Ok, you have now mastered the MySQL connection steps.

Now the real fun begins: let’s see how to execute SQL queries.

SQL QUERIES EXPLAINED

SQL is the language understood by SQL servers like MySQL.

You can execute SQL queries, or SQL statements, to insert, edit and delete data from a database.

As you will see in the next chapter, you can use queries to create, edit and destroy tables too.

Actually, every database operation is done with an SQL query, including schema creation, permissions handling and configuration settings (like the time zone or the locale).

If you want a quick and clear introduction to SQL before moving on, here’s a nice video from Caleb Curry:

In most cases, operations such as table and schema editing are done “manually” from SQL clients like phpMyAdmin, just like you did before.

On the other hand, operations like data insertion and retrieval are often done programmatically by web applications.

Let’s see a couple of query examples.

This is a basic authentication procedure that, given an username and password couple sent from an HTML form, looks for a matching row inside a “users” table: 

$username = 'Morpheus';
$password = 'red_pill'
$query = "SELECT * FROM users WHERE user_name = '" . $username . "' AND user_passwd = '" . $password . "'";

(Note: this is just an example to illustrate the idea of a query search. Follow this guide to learn more about authentication).

Another example is a “product page” that reads a product id from the request string and looks for a corresponding product item inside a products table: 

$product_id = $_REQUEST['product_id'];
$query = "SELECT * FROM products WHERE product_id = " . $product_id;

Before looking at more practical examples, there is one important thing you must know:

Here’s why:

When you use your computer and you want to delete some files, the operating system will ask you if you are really sure and then move the files to the trash bin.

Only after the bin has been emptied the files are really gone.

SQL is not so kind.

In fact, if you execute the following query: 

$query = 'DELETE FROM users';

all data from the users table will be deleted instantly, without any chances of recover.

And this query: 

$query = 'DROP DATABASE mySchema';

will instantly destroy your entire schema and all the tables in it, without asking you anything!

Many queries are built using values from the request string. You *always* need to check and validate that values before executing the query.

After you finish this tutorial, be sure to check my SQL injection prevention guide to learn more about this topic.

Now, let’s move on and see how you can create a new table inside your schema. 

HOW TO CREATE TABLES

I want you to really learn how tables work.

So, instead of just looking at some SQL code, in this tutorial you will pretend to run an online store.

You will create three tables inside your schema (mySchema):

1. a products table with a list of all the products in your store;
2. an orders table with a list of orders taken from your customers;
3. an order_products table linking each order with a list of products.

Each table has 3 columns, or fields. The first column of each table is its primary key.

The primary key is an unique ID (different for each row) which can be used to refer to a specific row of the table. Here are the tables columns:

Inside order_products, each row links a specific order (identified by its ID) with a specific product (again, identified by its ID).

This kind of relation is used extensively in databases. In fact, that’s why they are called relational databases in the first place.

For example:

If a client places an order with 3 products, there will be a single order row inside the orders table and 3 rows inside the order_products table, each one linking the order with one of the 3 products:

Almost 100% of the time, you will create tables manually using tools like phpMyAdmin.

But now, as an exercise, you will see how to execute the queries to create these tables using MySQLi and PDO.

At the end of this chapter you will find a video that will show you how to create these tables using phpMyAdmin, but I suggest you try executing these queries manually to get an idea of how MySQL tables work.

Note that the SQL syntax for creating a table can be quite complex, so we’re not going to look at the details here. But if you want to learn more, you’re welcome to ask me questions in the comments or in my Facebook group.

Let’s go:

here is the SQL code to create the three tables.

Each table needs three different queries:

the first one creates the table itself, the second one sets the ID column as the primary key, and the last one sets the ID column as auto-increment:

/* PRODUCTS table */
/* Query #1: create the table structure */
$products_query = 
'CREATE TABLE `products` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(255) NOT NULL,
  `price` smallint(5) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8';
/* Query #2: set the primary key */
$products_pk = 'ALTER TABLE `products` ADD PRIMARY KEY (`id`)';
/* Query #3: set the auto-increment */
$products_ai = 'ALTER TABLE `products` MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT';

/* ORDERS table */
/* Query #1: create the table structure */
$orders_query = 
'CREATE TABLE `orders` (
  `id` int(10) UNSIGNED NOT NULL,
  `date` date NOT NULL,
  `client` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8';
/* Query #2: set the primary key */
$orders_pk = 'ALTER TABLE `orders` ADD PRIMARY KEY (`id`)';
/* Query #3: set the auto-increment */
$orders_ai = 'ALTER TABLE `orders` MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT';

/* ORDER_PRODUCTS table */
/* Query #1: create the table structure */
$order_products_query = 
'CREATE TABLE `order_products` (
  `id` int(10) UNSIGNED NOT NULL,
  `order_id` int(10) UNSIGNED NOT NULL,
  `product_id` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8';
/* Query #2: set the primary key */
$order_products_pk = 'ALTER TABLE `order_products` ADD PRIMARY KEY (`id`)';
/* Query #3: set the auto-increment */
$order_products_ai = 'ALTER TABLE `order_products` MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT';

A primary key column acts as unique identifier for a row. When you need to refer to a specific row inside a table, you should always use the primary key value as reference.

Of course, primary keys must be different for each row, and this is why you set them as auto-increment:

in fact, auto-increment columns automatically assume an incremental numeric value when a new row is added to the table.

Don’t worry: you don’t need to remember the syntax 😉

Now let’s create the products table using the MySQLi procedural-style syntax: 

<?php
/* Include the MySQLi procedural-style connection script */
include 'db_inc.php';

/* Query #1: create the table structure */
$products_query = 
'CREATE TABLE `products` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(255) NOT NULL,
  `price` smallint(5) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8';
/* Execute the SQL query */
if (!mysqli_query($mysqli, $products_query))
{
   /* if mysqli_query() returns FALSE it means an error occurred */
   echo 'Query error: ' . mysqli_error($mysqli);
   die();
}
echo 'Products table created successfully<br>';

/* Query #2: set the primary key */
$products_pk = 'ALTER TABLE `products` ADD PRIMARY KEY (`id`)';
/* Execute the SQL query */
if (!mysqli_query($mysqli, $products_pk))
{
   /* if mysqli_query() returns FALSE it means an error occurred */
   echo 'Query error: ' . mysqli_error($mysqli);
   die();
}
echo 'Primary key added successfully<br>';

/* Query #3: set the auto-increment */
$products_ai = 'ALTER TABLE `products` MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT';
/* Execute the SQL query */
if (!mysqli_query($mysqli, $products_ai))
{
   /* if mysqli_query() returns FALSE it means an error occurred */
   echo 'Query error: ' . mysqli_error($mysqli);
   die();
}
echo 'Auto-increment set successfully<br>';

Now create the orders table using the MySQLi OOP-style syntax: 

<?php
/* Include the MySQLi oop-style syntax connection script */
include 'db_inc_oop.php';

/* Query #1: create the table structure */
$orders_query = 
'CREATE TABLE `orders` (
  `id` int(10) UNSIGNED NOT NULL,
  `date` date NOT NULL,
  `client` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8';
/* Execute the SQL query */
if (!$mysqli->query($orders_query))
{
   /* if mysqli::query() returns FALSE it means an error occurred */
   echo 'Query error: ' . $mysqli->error;
   die();
}
echo 'Orders table created successfully<br>';

/* Query #2: set the primary key */
$orders_pk = 'ALTER TABLE `orders` ADD PRIMARY KEY (`id`)';
/* Execute the SQL query */
if (!$mysqli->query($orders_pk))
{
   /* if mysqli::query() returns FALSE it means an error occurred */
   echo 'Query error: ' . $mysqli->error;
   die();
}
echo 'Primary key added successfully<br>';

/* Query #3: set the auto-increment */
$orders_ai = 'ALTER TABLE `orders` MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT';
/* Execute the SQL query */
if (!$mysqli->query($orders_ai))
{
   /* if mysqli::query() returns FALSE it means an error occurred */
   echo 'Query error: ' . $mysqli->error;
   die();
}
echo 'Auto-increment set successfully<br>';

The mysqli_query() function and the MySQLi::query() class method execute an SQL query (or SQL statement).

Note that mysqli_query() requires the mysqli connection resource as first argument. Remember? It’s the variable returned by the mysqli_connect() function inside your db_inc.php connection script.

When you work with databases, it’s important that you always check for errors. You did that when connecting, and you should do every time you execute a query too.

The return value from mysql_query() or from MySQLi::query() can be:

• false if the query fails
• true if the query succeeded without returning a result (like in this case)
• A resource if the query succeeded and returned a result set (like when reading rows from a table)

In the above example, you check if the result is false and, in that case, you output the error message and terminate the script.

If the return value is either true or a result resource (which also evaluates to true), it means that the query was executed successfully. 

Now let’s create the last table, order_products. This time you will use PDO:

<?php
/* Include the PDO connection script */
include 'db_inc_pdo.php';

/* Query #1: create the table structure */
$order_products_query = 
'CREATE TABLE `order_products` (
  `id` int(10) UNSIGNED NOT NULL,
  `order_id` int(10) UNSIGNED NOT NULL,
  `product_id` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8';
/* Execute the SQL query */
try
{
   $res = $pdo->prepare($order_products_query);
   $res->execute();
}
catch (PDOException $e)
{
   /* If there is an error an exception is thrown */
   echo 'Query error: ' . $e->getMessage();
   die();
}

echo 'Orders table created successfully<br>';

/* Query #2: set the primary key */
$orders_pk = 'ALTER TABLE `order_products` ADD PRIMARY KEY (`id`)';
/* Execute the SQL query */
try
{
   $res = $pdo->prepare($orders_pk);
   $res->execute();
}
catch (PDOException $e)
{
   /* If there is an error an exception is thrown */
   echo 'Query error: ' . $e->getMessage();
   die();
}
echo 'Primary key added successfully<br>';

/* Query #3: set the auto-increment */
$orders_ai = 'ALTER TABLE `order_products` MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT';
/* Execute the SQL query */
try
{
   $res = $pdo->prepare($orders_ai);
   $res->execute();
}
catch (PDOException $e)
{
   /* If there is an error an exception is thrown */
   echo 'Query error: ' . $e->getMessage();
   die();
}
echo 'Auto-increment set successfully<br>';

PDO throws a PDOException when an SQL query error occurs, so you must catch it with a try/catch block instead of checking the return value like you did with MySQLi.

Another difference is that PDO uses two methods for executing a single query: PDO::prepare() and PDOStatement::execute().

Couldn’t it just use a “query()” method like MySQLi?

Well… yes, it could. In fact, a PDO::query() method exists and it does just that.

“So, Alex, why do you want me to learn this weird syntax instead?”

Here’s why:

As you will see in the next chapter, in most cases you will need to use the prepare()/execute() syntax to make use of prepared statements.

So, instead of using query() here and prepare()/execute() later, it’s better to just stick with only one syntax that works in all cases.

All right.

After you execute the queries, you will see from phpMyAdmin that the new tables have been created inside MySchema.

Just click on “MySchema” on the left to see them.

As promised, here’s the video that shows you how to create the tables using phpMyAdmin:

HOW TO READ, INSERT AND EDIT ROWS

Data is added to databases as rows.

For example, each product is stored inside your database as a row of the products table:

If you want to:

• add a new row
• read one or more rows
• edit or delete one or more rows

you need to execute an SQL query, just like you did in the previous chapter.

Of course, the query itself will be different depending on what to want to do.

In this chapter you will learn how to add and read rows, how to edit them and how to delete them.

As usual, you will find examples using both MySQLi and PDO.

Let’s dive in.

HOW TO INSERT A NEW ROW

Let’s start by adding a new row inside the products table.

Suppose you have the product information available in your PHP script as variables, like this: 

$product_name = 'toothpaste';
$product_price = 5;

The SQL command for adding a new row is the INSERT command. The query for adding the above information as a new row is this: 

INSERT INTO products (name, price) VALUES ('toothpaste', 5);

However, you need to use your PHP variables to build the query.

Now, remember how SQL queries can be dangerous?

When you use variables to create your query, you must ALWAYS make sure that these variables are safe to use.

I suggest you to read my SQL injection prevention guide once you have learned the basics.

For now, you should know that all PHP variables used inside an SQL query must either be escaped or included inside the query using prepared statements.

Escaping takes care of parsing the variable by escaping all the dangerous characters (like ‘ and %). Prepared statements achieve a similar result but are even more safe.

MySQLi supports both operations, while PDO supports prepared statements only.

(PDO has a PDO::quote() function that can be used for escaping, but it’s not 100% safe and the PHP documentation itself discourages its use).

So, how do you execute the above query properly?

Let’s begin with MySQLi with procedural-style syntax. This is how you use escaping: 

<?php
/* Include the connection script */
include 'db_inc.php';
/* Values */
$product_name = 'toothpaste';
$product_price = 5;
/* Build the query escaping the values */
$query = "INSERT INTO products (name, price) VALUES ('" . mysqli_real_escape_string($mysqli, $product_name) . "', " . mysqli_real_escape_string($mysqli, $product_price)  . ")";
/* Execute the SQL query */
if (!mysqli_query($mysqli, $query))
{
   /* if mysqli_query() returns FALSE it means an error occurred */
   echo 'Query error: ' . mysqli_error($mysqli);
   die();
}
echo "Product added successfully<br>";

As you can see, escaping is quite straightforward.

You just need to use the mysqli_real_escape_string() function on the values you want to use inside the query.

This function makes a string safe to use by searching and escaping all dangerous characters that could lead to SQL errors or injection attacks.

Like many mysqli_* functions, it takes the connection resource as first argument (remember? It’s the $mysqli variable returned by mysqli_connect()).

This is how you can use escaping with MySQLi’s OOP-style syntax: 

<?php
/* Include the connection script */
include 'db_inc_oop.php';
/* Values */
$product_name = 'toothpaste';
$product_price = 5;
/* Build the query escaping the values */
$query = "INSERT INTO products (name, price) VALUES ('" . $mysqli->real_escape_string($product_name) . "', " . $mysqli->real_escape_string($product_price)  . ")";
/* Execute the SQL query */
if (!$mysqli->query($query))
{
   /* if mysqli::query() returns FALSE it means an error occurred */
   echo 'Query error: ' . $mysqli->error;
   die();
}
echo "Product added successfully<br>";

Here, the MySQLi::escape() method works just like the procedural-style mysqli_real_escape() function.

Isn’t that difficult, is it?

Let’s move on to prepared statements.

Here an example using MySQLi, OOP-style syntax: 

<?php
/* Include the connection script */
include 'db_inc_oop.php';
/* Values */
$product_name = 'toothpaste';
$product_price = 5;
/* Query template */
$query = "INSERT INTO products (name, price) VALUES (?, ?)";
/* Prepare step: send the query template to the MySQL server */
$stmt = $mysqli->prepare($query);
if (!$stmt)
{
   echo 'Query error: ' . $mysqli->error();
   die();
}
/* Bind step: bind the variables to the query ('s' for strings, 'i' for integers) */
if (!$stmt->bind_param('si', $product_name, $product_price))
{
   echo 'Binding failed: ' . $stmt->error;
   die();
}
/* Finally execute the complete query */
if (!$stmt->execute())
{
   echo 'Execute failed: ' . $stmt->error;
   die();
}
echo "Product added successfully<br>";

Prepared statements are a bit more complex than escaping.

Instead of building a complete query string with the escaped values, using prepared statements is a three-steps process:

• first, you send a query template to the MySQL server, using placeholders (as ?) instead of values (lines 10-14 of the above example) ;
• then, you bind each placeholder to a PHP variable, specifying the variable type (lines 22-27);
• finally, you execute the query (lines 29-34).

The MySQLi::prepare() method sends the query template (the query with placeholders instead of real values) to the MySQL server and returns a mysqli_stmt (mysqli statement) object.

This object is the link to the query template sent to the database.

Then, the binding step links a PHP variable to each of the placeholders you put in the query template. This is done with the mysqli_stmt::bind_param() method.

This method takes a string of variable types as first argument, and the actual PHP variables as next arguments. In the above example, the ‘si’ argument means that the first variable is a string (s) and the second is an integer number (i).

The last step is to run the mysqli_stmt::execute() method.

Every time you call it, the MySQL server will execute the query using the current values of the bound variables.

You need to perform the prepare and binding steps only once. Then, you can change the PHP variables and call mysqli_stmt::execute() multiple times.

For example: 

<?php
/* Include the connection script */
include 'db_inc_oop.php';
/* Variable values */
$product_name = '';
$product_price = 0;
/* Array of products */
$products = array();
$products[] = array('name' => 'toothpaste', 'price' => 5);
$products[] = array('name' => 'shoes', 'price' => 60);
$products[] = array('name' => 'hat', 'price' => 20);
/* Query template */
$query = "INSERT INTO products (name, price) VALUES (?, ?)";
/* Prepare step: send the query template to the MySQL server */
$stmt = $mysqli->prepare($query);
if (!$stmt)
{
   echo 'Query error: ' . $mysqli->error();
   die();
}
/* Bind step: bind the variables to the query ('s' for strings, 'i' for integers) */
if (!$stmt->bind_param('si', $product_name, $product_price))
{
   echo 'Binding failed: ' . $stmt->error;
   die();
}
/* Insert all the products from the array */
foreach ($products as $product)
{
   $product_name = $product['name'];
   $product_price = $product['price'];
   if (!$stmt->execute())
   {
      echo 'Execute failed: ' . $stmt->error;
      die();
   }
}
echo 'All products added!';

This variable-binding prepared statements method is the only one supported by MySQLi.

Now let’s move on to PDO.

PDO supports different prepared statements procedures, including a variable-binding syntax similar to MySQLi’s.

However, the method I suggest you to learn first is the param-binding syntax.

In fact, this syntax is easier to learn and it’s less error-prone, because you don’t need to keep track of the bound variables.

Let’s see an example: 

<?php
/* Include the PDO connection script */
include 'db_inc_pdo.php';
/* Variable values */
$product_name = 'toothpaste';
$product_price = 5;
/* Query template with named placeholders */
$query = 'INSERT INTO products (name, price) VALUES (:name, :price)';
/* Array with query values */
$values = array(
   ':name' => $product_name,
   ':price' => $product_price
);
/* Start a try/catch block to catch PDO exceptions */
try
{
   /* Prepare step */
   $res = $pdo->prepare($query);
   
   /* Execute step, with the array of values */
   $res->execute($values);
}
catch (PDOException $e)
{
   /* If there is an error an exception is thrown */
   echo 'Query error: ' . $e->getMessage();
   die();
}
echo "Product added successfully<br>";

The main difference compared to the MySQLi procedure is how the values are sent to the MySQL server in the execute step.

In the above example, the PDO query template uses named placeholders (as “:name” and “:price”) instead of generic placeholders (as question marks “?”). 

The query template is sent to the database with the PDO::prepare() method which returns a PDOStatement object.

Note: named placeholders must start with a colon (“:”).

Then, instead of binding each placeholder to a PHP variable, an associative array is created ($values) to bind each named placeholder to a value. The array keys are the named placeholders and their values are the values to be sent to the database.

This array is passed to the PDOStatement::execute() method.

If you want to insert multiple rows with different values, you need to change the $values array  each time, like this: 

/* Array of products */
$products = array();
$products[] = array('name' => 'toothpaste', 'price' => 5);
$products[] = array('name' => 'shoes', 'price' => 60);
$products[] = array('name' => 'hat', 'price' => 20);
try
{
   /* Prepare step (can be done only once) */
   $res = $pdo->prepare($query);
   
   foreach ($products as $product)
   {
      $values = array(
         ':name' => $product['name'],
         ':price' => $product['price']
      );
      
      /* Execute step, with the array of values */
      $res->execute($values);
   }
}
catch (PDOException $e)
{
   /* If there is an error an exception is thrown */
   echo 'Query error: ' . $e->getMessage();
   die();
}

PDO also supports an alternative syntax with generic placeholders (“?“), just like MySQLi’s.

In that case, the values array must be an ordered, numeric-indexed array: 

...
/* Query template with generic placeholders */
$query = 'INSERT INTO products (name, price) VALUES (?, ?)';
/* Array with query values */
$values = array($product_name, $product_price);
...

HOW TO READ ROWS FROM A TABLE

If you want to read some rows back from a database, you need to execute an SQL query (Remember? Every database operation is done with a query).

The process is just like adding a new row, but there is one big difference…

Can you guess what it is?

Yes, that’s right: this time you have a result set.

A result set is an object containing the rows returned by the SQL query.

How does it work?

Let’s see an example.

Let’s say that your products table contains 5 rows, like this:

Now suppose you want to read all these rows and print them inside your web page.

The SQL command to read rows from a table is the SELECT command.

This is the query to read all the rows from the products table:

SELECT * FROM products;

When you execute this query with MySQLi or PDO, you get a list of rows in return.

Once you have that list, all you need to do is to iterate through it using specific functions.

Let’s see how to do it.

As usual, let’s start with the MySQLi procedural-style syntax.

<?php
/* Include the MySQLi (procedural-style) connection script */
include 'db_inc.php';
/* Query */
$query = 'SELECT * FROM products';
/* Execute the query */
$result = mysqli_query($mysqli, $query);
/* Check for errors */
if (!$result)
{
   echo 'Query error: ' . mysqli_error($mysqli);
   die();
}
/* Iterate through the result set */
while ($row = mysqli_fetch_assoc($result))
{
   echo 'Product name: ' . $row['name'] . ', price: ' . $row['price'] . '<br>';
}

The $result variable contains the result set. It’s a mysqli_result object.

To iterate through all the rows in this result set you need to use a mysqli_fetch_* function, like mysqli_fetch_assoc() used in the example.

These functions get the current row from the result set and return it as a standard PHP array or object.

In the example, mysqli_fetch_assoc() returns an associative array where the array key is the name of the column (name and price) and the values are the column values.

After each iteration, the result set internal pointer moves on by 1 row so that the next time mysqli_fetch_assoc() will read the next row, until no more rows are left.

This is what happens inside the while loop in the above example:

• mysqli_fetch_assoc() reads the first row from $result and returns an associative array inside $row
• The echo command prints: “Product name: Toothpaste, price: 5”
• At the next while iteration, the next row is read and the echo command prints: “Product name: Shoes, price: 50”
• When there are no more rows, mysqli_fetch_assoc() returns false and the while loop stops

 If the query doesn’t return any row, the while loop does not run even once.

Now, let’s see another example.

Suppose you want to look up a specific product price from the table.

Instead of reading all the rows and looking for your product using PHP code, you can filter the SQL result directly from the database using a WHERE clause.

For example, if you want to limit the result to the rows where the product name is “Laptop”, you can use this query:

SELECT * FROM products WHERE name = 'Laptop';

This query will return only one row (the one with “Laptop” as product name).

If you know that the result set contains only one row, like in this case, you can avoid the while loop and run the fetch command only once.

This is how you can do it using MySQLi OOP-style syntax: 

<?php
/* Include the MySQLi (OOP-style) connection script */
include 'db_inc_oop.php';
$search_prd = 'Laptop';
/* Query */
$query = "SELECT * FROM products WHERE name = '" . $mysqli->real_escape_string($search_prd) . "'";
/* Execute the query */
$result = $mysqli->query($query);
/* Check for errors */
if (!$result)
{
   echo 'Query error: ' . $mysqli->error();
   die();
}
/* Fetch the only row from the result set */
$row = $result->fetch_assoc();
/* Check that $row is ok */
if (!$row)
{
   echo 'Warning: no rows found.';
   die();
}
/* Echo the Laptop price */
echo 'Laptop price: ' . $row['price'] . '<br>';

If you have any doubt, just leave a comment below, ok?

All right.

So, how about PDO?

PDO syntax is similar to MySQLi’s.

Remember that the PDO prepare() method returns a PDOStatement object?

From there, you can use the PDOStatement::fetch() method to iterate through its result rows.

In this last read example, you will search for all the products having a price higher than 10.

This is the query you will execute:

SELECT * FROM products WHERE price > 10;

And here is the PDO example:

<?php
/* Include the PDO connection script */
include 'db_inc_pdo.php';
$min_price = 10;
/* Query template with a named placeholder (:min_price) */
$query = 'SELECT * FROM products WHERE price > :min_price';
/* Query values (the minimum price value) */
$values = array(
   ':min_price' => $min_price
);
/* Start a try/catch block to catch PDO exceptions */
try
{
   /* Prepare step */
   $res = $pdo->prepare($query);
   
   /* Execute step, with the values array */
   $res->execute($values);
}
catch (PDOException $e)
{
   /* If there is an error an exception is thrown */
   echo 'Query error: ' . $e->getMessage();
   die();
}
/* Iterate through the result rows */
while ($row = $res->fetch(PDO::FETCH_ASSOC))
{
   echo 'Product name: ' . $row['name'] . ', price: ' . $row['price'] . '<br>';
}

HOW TO EDIT AND DELETE ROWS

Just like any SQL operation, editing and deleting rows is done with an SQL query too.

Both operations do not return any result set.

Of course, you still need to check whether the operation succeeded by looking at the return value or by catching exceptions, just like you did in the previous examples.

Usually, when editing or deleting rows from a table you don’t want to affect all the rows in the table but only a subset.

Selecting the proper subset can be done in many different ways, but most of the time you will rely on a WHERE clause.

The WHERE clause limits the affected rows when executing an SQL query.

You already used it in two of the previous examples:

• when searching for the product with name “Laptop”,
• and when searching for all the products with a price tag higher than 10

Here’s a delete command example.

If you want to delete all the orders from a specific client, you must perform a DELETE operation on the orders table selecting the rows with a specific value in the client column.

This selection is done using a WHERE clause.

Remember: every time you use a value in your query (in this case, the client name) you must use escaping or prepared statements.

So, let’s say you want to delete all the orders placed by the client named “Spock”. This is the query you will execute:

DELETE FROM orders WHERE client = 'Spock';

If you remember the previous examples, you should have no problems finding out how to execute this query 😉

Anyway, here is how to do it using the MySQLi extension, using escaping and the procedural-style syntax:

<?php
/* Include the MySQLi (procedural-style) connection script */
include 'db_inc.php';
/* Client name */
$client_name = 'Spock';
/* Query */
$query = "DELETE FROM orders WHERE client = '" . mysqli_real_escape_string($mysqli, $client_name) . "'";
/* Execute the query */
$result = mysqli_query($mysqli, $query);
/* Check for errors */
if (!$result)
{
   echo 'Query error: ' . mysqli_error($mysqli);
   die();
}
echo mysqli_affected_rows($mysqli) . ' orders have been deleted.';

In this example I introduced a new MySQLi function: mysqli_affected_rows()

This function (and the MySQLi::affected_rows attribute, when using OOP) returns the number of rows that have been affected by the last SQL query.

In this case, that is the number of deleted rows. This information can be very useful.

Now, what if you want to EDIT some rows?

The SQL command you need to use is UPDATE.

The UPDATE command modifies the column values for all the rows selected by the SQL query.

Just like for the DELETE operations, UPDATE operations too are usually done only on some specific rows instead of the whole table. So, you usually want to use a WHERE clause here too.

For example, let’s say you want to lower your product prices by 5, but only for the products with a price tag higher than 20.

Products that cost less or equal 20 must keep their current price, while the more expensive ones will have their price reduced by 5.

Let’s go straight to the example. This time, you will use PDO:

<?php
/* Include the PDO connection script */
include 'db_inc_pdo.php';
/* Lower price threshold */
$price_thr = 20;
/* Query template with a named placeholder (:price_thr) */
$query = 'UPDATE products SET price = (price - 5) WHERE price > :price_thr';
/* Query values (the lower price threshold) */
$values = array(
   ':price_thr' => $price_thr
);
/* Start a try/catch block to catch PDO exceptions */
try
{
   /* Prepare step */
   $res = $pdo->prepare($query);
   
   /* Execute step, with the values array */
   $res->execute($values);
}
catch (PDOException $e)
{
   /* If there is an error an exception is thrown */
   echo 'Query error: ' . $e->getMessage();
   die();
}
/* Echo how many products were affected by the query */
echo $res->rowCount() . ' products had their price lowered.';

In this example, the UPDATE SQL command is used together with the SET command to specify the new values for the price column.

You can SET an explicit value, but you can also use the current column to calculate the new value.

That’s exactly what the example does:

• SET price = (price – 5)

That means: get the current price value, decrease it by 5 (price -5), then save the result as the new price value.

The PDOStatement::rowCount() at the end of the example returns the number of rows affected by the last SQL query, just like the mysqli_affected_rows() function does.

ERROR HANDLING AND EXCEPTIONS

SQL connection attempts and query executions can fail for many different reasons.

Connection errors are usually caused by network issues or wrong permissions, while query failures are often caused by syntax errors. 

Unfortunately, you cannot presume that your application will be completely SQL-errors free, so you must assume that every SQL operation may fail for some reason and be ready to handle such events.

As you learned in the previous examples, you can catch errors in two ways:

1. by checking if the return value from connection and query functions is false;
2. by catching exceptions with try/catch blocks.

You also learned how to get the specific error messages, using procedural-style functions (like mysqli_connect_error()) and OOP-style class methods and attributes (like MySQLi::$error) with MySQLi, and using the PDOException class methods with PDO.

In all the previous examples you checked the functions return values when using MySQLi, while you used Exceptions when using PDO.

But… what if you want to use exceptions with MySQLi or you do not want to use them with PDO?

Let’s see how you can do that.

Enabling exceptions with MySQLi is done with the mysqli_report() function.

The report mode must be set to STRICT for MySQLi functions to throw exceptions on errors. You can also set it to ALL to have both exceptions and function return values.

The thrown exception is a mysqli_sql_exception, a MySQLi specific exception class inherited from the more generic RuntimeException.

Here’s a practical example: 

<?php
/* Host name of the MySQL server */
$host = 'localhost';
/* Account username */
$user = 'myUser';
/* Account password */
$passwd = 'myPasswd';
/* The schema you want to use */
$schema = 'mySchema';
/* Set the MySQLi error mode to STRICT */
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
/* This will also work, using both exceptions and return values */
/* mysqli_report(MYSQLI_REPORT_ALL); */
/* Do everything inside a try/catch block */
try
{
   /* Connection */
   $mysqli = mysqli_connect($host, $user, $passwd, $schema);
   
   /* A wrong query */
   $query = 'SELECT * FROM myschema.wrong_table';
   mysqli_query($mysqli, $query);
}
catch (mysqli_sql_exception $e)
{
   /* Error: exception thrown */
   echo 'SQL error: ' . $e->getMessage();
}

The above code will output:

SQL error: Table ‘myschema.wrong_table’ doesn’t exist

Now let’s look at PDO.

You can choose how PDO handles errors by changing the PDO::ATTR_ERRMODE attribute with the PDO::setAttribute() method.

That is what you did in the db_inc_pdo.php connection script: 

/* Enable exceptions on errors */
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Note that the connection operation (done by the PDO constructor) always throws exceptions on errors, regardless of the error mode.

If you want to disable exceptions for query errors, you have to set the error mode to SILENT. If you do that, you must check for the functions return values to see if an error occurred.

Here’s an example:

<?php
/* Include the PDO connection script */
include 'db_inc_pdo.php';
/* Disable exceptions on query errors */
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
/* A wrong query */
$query = 'SELECT * FROM myschema.wrong_table';
/* Prepare step */
$res = $pdo->prepare($query);
if (!$res)
{
   echo 'Prepare failed<br>';
   
   /* Print the error */
   $error = $pdo->errorInfo();
   echo 'Error code: ' . $error[1] . '<br>';
   echo 'Error message: ' . $error[2];
   die();
}
/* Execute step */
if (!$res->execute())
{
   echo 'Execute failed<br>';
   
   /* Print the error */
   $error = $res->errorInfo();
   echo 'Error code: ' . $error[1] . '<br>';
   echo 'Error message: ' . $error[2];
   die();
}

The above code will output:

Execute failed
Error code: 1146
Error message: Table ‘myschema.wrong_table’ doesn’t exist

If there is an error in the prepare operation, you can use the PDO::errorInfo() method to fetch an array with the error information.

If there is an error in the execute operation, you need to call PDOStatement::errorInfo() instead.

SQL SECURITY

When you work with MySQL or with any other database, it’s very important that you care about security from the beginning.

According to OWASP, SQL-related attacks are the number #1 web security risk.

This is why I introduced escaping and prepared statements right from the start.

The most common security-related error I see is about using unsafe variables inside SQL queries.

That is: using PHP variables inside an SQL query without escaping them and without using prepared statements.

For example, suppose you have an HTML login form with username and password fields.

Your back-end code searches a users table for a row with the username and password values from the form.

Now, suppose you forget to escape the fields values (or to use prepared statements) inside your back-end code:

<?php
/* Include the PDO connection script */
include 'db_inc_pdo.php';
/* Request values */
$username = $_REQUEST['username'];
$password = $_REQUEST['password'];
/* Query with UNSAFE variables */
$query = "SELECT * FROM test.users2 WHERE username = '" . $username . "' AND password = '" . $password . "'";
try
{
	/* Prepare step */
	$res = $pdo->prepare($query);
	
	/* Execute step */
	$res->execute();
}
catch (PDOException $e)
{
   /* If there is an error an exception is thrown */
   echo 'Query error<br>';
   echo 'Error number: ' . $e->getCode() . '<br>';
   echo 'Error message: ' . $e->getMessage() . '<br>';
   die();
}
$row = $res->fetch(PDO::FETCH_ASSOC);
if ($row)
{
	echo 'Welcome back, ' . $username . '!';
}
else
{
	echo 'Sorry, wrong username or password.';
}

If a malicious user inputs “admin” in the username field and the string “wrong_password’ OR 1;–“ in the password field, do you know what happens?

The user will be authenticated as admin, without using the correct password!

This is just an example of the many possible SQL-related vulnerabilities.

As soon as you are familiar with the basics, I suggest you to read my SQL security guide here:

>> SQL Injection Complete Guide

CONCLUSION

Pheww, you made it!

Now you know all you need to start working with MySQL like a pro.

You understood how MySQLi and PDO work and what their differences are, and you have all the information you need to choose which one to go with.

You also saw a lot of examples that you can use to get started with your own web application.

If you have any question or if you just want to let me know what you think about this guide, please leave a comment below! 

P.s. If this guide has been helpful to you, please spend a second of your time to share it… thanks!

Alex