Tuesday 3 December 2019

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(11NOT NULL,
  `client_id` int(11NOT 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
(11'2019-05-19 13:18:13''2019-05-20 09:11:57'10075),
(21'2019-05-19 13:21:18''2019-05-20 15:32:14'50.220.5),
(32'2019-05-20 14:19:18''2019-05-20 21:27:30'7560),
(42'2019-05-20 15:47:15''2019-05-21 08:23:28'90.720),
(53'2019-05-21 08:23:15''2019-05-21 10:33:24'120100),
(61'2019-01-22 05:20:08''2019-02-25 12:10:19'500NULL),
(71'2019-05-23 13:18:13''2019-05-24 09:11:57'150150),
(83'2019-05-24 11:33:28''2019-05-25 12:16:12'20050),
(92'2019-05-25 14:14:21''2019-05-26 06:33:19'250250),
(103'2019-05-26 11:12:12''2019-05-26 17:13:13'3000);

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

0 comments:

Post a Comment