Read MySQL database with PHP – php-pdo

Install LAMP web development stack, and write a simple PHP program to read MySQL database.
This article is very brief and without detailed explanations. Prequisites include Linux command line interface, Apache, SQL and MySQL.

Test Environment – Ubuntu 16.04 LTS

If you are already running Linux, you don’t need to install virtual guest.

$ vagrant init bento/ubuntu-16.04
$ vagrant up
$ vagrant ssh

Test Environment – Install LAMP

If you have already installed LAMP, enabled user homepages and allowed PHP in homepages, you don’t need to do this again.
Installations should be tested in the smallest steps possible. For LAMP, this is explained in other articles. Here, everything is installed in one go to keep this article short.

$ sudo apt-get update && sudo apt-get -y install apache2 mysql-client mysql-server libapache2-mod-php php-mysql curl lynx

‘New password for the MySQL “root” user’. Give good password when asked. Use different password from any Linux shell account.

Allow User Homepages and PHP

$ sudo a2enmod userdir
$ sudoedit /etc/apache2/mods-available/php7.0.conf # comment out ifmodule stanza to allow PHP in userdir
$ sudo service apache2 restart

Test PHP on homepages

$ cd; mkdir public_html; cd public_html
$ echo '<?php print(2+2+"\n\n"); ?>'> index.php
$ curl localhost/~vagrant/; echo   # use your own username
4

Create MySQL Database and User with the Same Name

$ mysql --user=root --password

Enter your MySQL root password when asked.

mysql> CREATE DATABASE terohat CHARACTER SET utf8;

MySQL will acknowledge: “Query OK, 1 row affected (0.00 sec)”

mysql> GRANT ALL ON terohat.* TO terohat@localhost IDENTIFIED BY '9nl3jhIInex9)83.23uu';

“Query OK, 0 rows affected, 1 warning (0.00 sec)”
Use a very complicated password. Copy it from the SQL command, so that you can paste it to your PHP script. You don’t need to remember the password.
Always give password “IDENTIFIED BY” when creating a new user “GRANT ALL”, so that you don’t mistakenly create a passwordless user.
Exit MySQL root to use your new, less priviledged user.

mysql> exit

Create Table and Insert Records

$ mysql --user=terohat --password

Paste your complicated password to the prompt.

mysql> USE terohat;
mysql> CREATE TABLE hats(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(1024));
mysql> INSERT INTO hats(name) VALUES ('Cap');
mysql> INSERT INTO hats(name) VALUES ('Ninja Hood');
mysql> INSERT INTO hats(name) VALUES ('White Helmet');
mysql> SELECT * FROM hats;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | Cap          |
|  2 | Ninja Hood   |
|  3 | White Helmet |
+----+--------------+

mysql> exit

Write PHP Page to Access Your Database

$ cd
$ nano public_html/index.php

Write a PHP program to show some records.

<?php
// public_html/index.php - Read MySQL from PHP
// (c) 2016 Tero Karvinen http://TeroKarvinen.com
// MySQL Login
$user='terohat';
$password='9nl3jhIInex9)83.23uu';
// Data Source Name i.e. connection details
$database=$user;
$dsn="mysql:host=localhost;charset=UTF8;dbname=$database";
// Open Connection, create new object of PDO class
$pdo=new PDO($dsn, $user, $password);
// Perform SQL Query
$pdoStatement=$pdo->prepare('SELECT * FROM hats;');
$pdoStatement->execute();
$hits=$pdoStatement->fetchAll();
// Print the $hits Array
foreach($hits as $row) {
 echo "<p>".$row['id']." ".$row['name']."</p>\n";
}
?>

Test Your PHP Program

$ curl localhost/~vagrant/      
<p>1 Cap</p>
<p>2 Ninja Hood</p>
<p>3 White Helmet</p>

Or if you want to get fancy, use a graphical web browser such as Firefox.

Blank Page? Errors Are Only in Logs

PHP errors are in logs. They are not shown on web pages.

$ tail /var/log/apache2/error.log

Well done. You now have a nice, simple program to read MySQL databases from PHP.