How to access a MySQL database from PHP using PDO_MYSQL

PDO_MYSQL is a driver that allows us to establish a connection from PHP to a MySQL database. 

In this article, we will build an example step by step that will show how to connect a MySQL database from PHP using PDO_MYSQL.

In the example, we will cover the following points:

• Establish a connection to a MySQL database
• Run a query to retrieve the rows of a table
• Show the query results

First, let’s create a database in MySQL with an Orders table that has the following structure and data:

CREATE DATABASE IF NOT EXISTS `mydatabase` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; USE `mydatabase`; CREATE TABLE `orders` (   `id` int(11) NOT NULL,   `client_id` int(11) NOT NULL,   `received` datetime NOT NULL,   `processed` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,   `total` float NOT NULL,   `paid` float DEFAULT '0' ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `orders` (`id`, `client_id`, `received`, `processed`, `total`, `paid`) VALUES (1, 1, '2019-05-19 13:18:13', '2019-05-20 09:11:57', 100, 75), (2, 1, '2019-05-19 13:21:18', '2019-05-20 15:32:14', 50.2, 20.5), (3, 2, '2019-05-20 14:19:18', '2019-05-20 21:27:30', 75, 60), (4, 2, '2019-05-20 15:47:15', '2019-05-21 08:23:28', 90.7, 20), (5, 3, '2019-05-21 08:23:15', '2019-05-21 10:33:24', 120, 100), (6, 1, '2019-01-22 05:20:08', '2019-02-25 12:10:19', 500, NULL), (7, 1, '2019-05-23 13:18:13', '2019-05-24 09:11:57', 150, 150), (8, 3, '2019-05-24 11:33:28', '2019-05-25 12:16:12', 200, 50), (9, 2, '2019-05-25 14:14:21', '2019-05-26 06:33:19', 250, 250), (10, 3, '2019-05-26 11:12:12', '2019-05-26 17:13:13', 300, 0); ALTER TABLE `orders` ADD PRIMARY KEY (`id`);

Script 1. Creation of database to use in the example

Establish a connection to a MySQL database with PDO_MYSQL

Opening a connection with PDO_MYSQL is really simple. We only have to use the connection parameters that we normally use in the following structure:

<?php /*host and database name*/ $pdo_parameters = 'mysql:host=localhost;dbname=mydatabase'; /*name of database user*/ $database_user = 'root'; /*user database password*/ $user_password = 'password'; /*connection command*/ $pdo_connection = new PDO($pdo_parameters, $database_user, $user_password); ?>

Script 2. Opening connection to the database with PDO_MYSQL

Run a query to retrieve the rows of a table

To execute a query, we first build a text string that contains the command, then we can execute it with the “query” function.

$sql = 'SELECT id, processed, total, paid FROM orders ORDER BY id';  $pdo_connection->query($sql);

Script 3. Executing a query

Show the query results 

To show the results, we are going to use a table. We will fill the rows of the table through a loop.

<table> <tr> <th><strong>id</strong></th> <th><strong>total</strong></th> <th><strong>paid</strong></th> </tr> <?php foreach ($pdo_connection->query($sql) as $row) { ?> <tr> <td><?php print $row['id'] ?></td> <td><?php print $row['total'] ?></td> <td><?php print $row['paid'] ?></td> </tr> <?php } ?> </table>

Script 4. Showing query results in a table

Finally, the complete code of our example should look like this:

<?php $pdo_parameters = 'mysql:host=localhost;dbname=mydatabase'; $database_user = 'root'; $user_password = '311377'; $pdo_connection = new PDO($pdo_parameters, $database_user, $user_password); $sql = 'SELECT id, processed, total, paid FROM orders ORDER BY id'; ?> <table>     <tr>     <th><strong>id</strong></th>     <th><strong>total</strong></th>     <th><strong>paid</strong></th> </tr> <?php     foreach ($pdo_connection->query($sql) as $row) { ?>     <tr>     <td><?php print $row['id'] ?></td>     <td><?php print $row['total'] ?></td>     <td><?php print $row['paid'] ?></td>     </tr> <?php } ?> </table>

Script 5. Complete example code