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

Create MySQL Database and User with the Same Name

$ mysql --user=root --password

Enter your MySQL root password when asked.


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

// public_html/index.php - Read MySQL from PHP
// (c) 2016 Tero Karvinen
// MySQL Login
// Data Source Name i.e. connection details
// Open Connection, create new object of PDO class
$pdo=new PDO($dsn, $user, $password);
// Perform SQL Query
$pdoStatement=$pdo->prepare('SELECT * FROM hats;');
// 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.

See also

PDO – PHP Data Objects (manual): PDO::__construct
An extremely simple commented PDO example
MySQL Install and One Table Database – SQL CRUD tutorial for Ubuntu

Posted in Uncategorized | 2 Comments

2 Responses to Read MySQL database with PHP – php-pdo

  1. pricereduc says:

    As I like PDO and in order to prevent sql queries from sql injection, I would like to suggest this code: (I intentionally removed dollar sign because I don’t know if it can display on my comment)
    Let’s say you want to select all hats that starts with letter A but you will use a get or post method to pass a value, if you want to do this safely, you must use the method bindParam (or even bindValue) on your pdoStatement object you have created.
    statement = pdo->prepare(‘SELECT * FROM hats WHERE name = ?’);
    mygetparameter = _GET[“firstletter”].”%”;
    pdoStatement= pdo->prepare(“select * from hats WHERE name LIKE :mygetparameter”);
    pdoStatement->bindParam(“:mygetparameter”, mygetparameter );
    hits= $pdoStatement->fetchAll();
    foreach(hits as row) {
    echo “”.row[‘id’].” “.row[‘name’]
    I hope it can help

  2. Remember to use database library commands to combine user data to SQL. This protects you against sql injection.

    $pdoStatement=$pdo->prepare("INSERT INTO hats (name) VALUES (:name)");
    $pdoStatement->bindParam(':name', $name);