PHP Database – Beginner’s Guide With Examples

INTRODUCTION
THE MANY WAYS

Welcome to a beginner’s tutorial on simple PHP Database, where we will walk through some practical code examples, with little boring words and grandmother stories. When it comes to databases in PHP, you might have already found several different tutorials online – Each of them with a different way of doing it.

Just why are there so many ways to deal with databases in PHP? How do we connect to the database in PHP? Which is the “correct” way to do it? We shall explore all of that in this guide, read on to find out!

ⓘ I have included a zip file with all the example source code at the end of this tutorial, so you don’t have to copy-paste everything… Or if you just want to dive straight in.

 

 

 

PREAMBLE
EXAMPLE CODE DOWNLOAD

First, here is the download link as promised.

 

EXAMPLE CODE DOWNLOAD

Click here to download the source code, I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

QUICK NOTES

There is nothing to install, so just download and unzip into a folder. If you spot a bug, please feel free to comment below. I try to answer questions too, but it is one person versus the entire world… If you need answers urgently, please check out my list of websites to get help with programming.
 

SECTION A
PHP DATABASE BASICS

Before we jump into the examples, here is a section of the “basic stuff” for those of you who are new to PHP and databases. Please feel free to skip this section if you are already an advanced code ninja.

 

A STORY OF PHP & MYSQL

So why are there different ways to deal with the database in PHP? Well, let us start with an overview and a really short history lesson. As you might have already known, a web server generally consists of these components:

  • HTTP server
  • Server-side language
  • Database

Over the many years, PHP has been commonly bundled in a WAMP, LAMP, or MAMP web server stack –

  • Windows, Linux, or Mac
  • Apache HTTP server
  • MySQL database
  • PHP

So yes, MySQL has always been kind of the “default” database for PHP; PHP has also exclusively supported MySQL historically. But as the number of different databases starts to grow, the developers of PHP also realized that they cannot stick to just MySQL. Which is why they developed different extensions to work with various databases – Thus the many different ways, and different tutorials all over the Internet.

 

GETTING STARTED – DATABASE EXTENSION

In this tutorial, we will be working with MySQL, which is the easiest to set up. If you have installed the XAMPP package, then you are already good to go. For those of you who have installed the components individually, then you will need to manually install MySQL and make sure that the extensions are enabled in the php.ini file –

php.ini
; JUST REMOVE THE ";" IN FRONT TO ENABLE THE EXTENSIONS
extension=mysqli
extension=pdo_mysql

 

THE PHP DATABASE EXTENSIONS

If you look through the extensions list in the php.ini file, you will notice that there are 3 different extensions for MySQL. Why are there 3 of them? So which one of them is correct?

  • MySQL – The old PHP-MySQL extension, this one has been deprecated. Don’t use this unless you have to support the really old legacy systems.
  • MySQLi – The newer improved version of the MySQL extension.
  • PDO MySQL (PHP Data Objects) – Remember that we mentioned there are various databases in the industry? PDO is the answer to supporting multiple databases. If you look through the extensions in php.ini, you will also find many other PDO extensions – Firebird, SQLite, ODBC, OCI, etc… With PDO, the same set of PHP code will work with many different types of databases.

So yes – I will highly recommend using PDO for your projects, and it is the way to future proof your project as well.

 

 

CRUD

For this final part of the basics, I will introduce a concept called CRUD. Just what is it and what has it got to do with databases? CRUD stands for:

  • Create
  • Read
  • Update
  • Delete

Which are also the very basic functions of permanent storage. In terms of a database, that will mean:

  • Creating database entries.
  • Reading the entries.
  • Updating entries.
  • Deleting entries.

Which are what we will walk through in the examples next.

 

SECTION B
PDO EXAMPLES

Now that you are armed with the basic knowledge, let us dive into the example code on how to work with the PHP PDO database extension – By creating a database library file itself.

 

0) THE CONFIG FILE

First, let us start by creating a config file first. It will be wise to keep all your database and settings in this file, and not having copies of the database credentials all over your scripts… This will also save you a lot of time.

config.php
<?php
// MUTE NOTICES
error_reporting(E_ALL & ~E_NOTICE);

// DATABASE SETTINGS - CHANGE THESE TO YOUR OWN
define('DB_HOST', 'localhost');
// SET & ENABLE THIS TO DIRECTLY USE A SPECIFIED DATABASE
// define('DB_NAME', 'test');
define('DB_CHARSET', 'utf8');
define('DB_USER', 'root');
define('DB_PASSWORD', '');
?>

 

1) CONNECTING & DISCONNECTING FROM THE DATABASE

The first step of working with a database is to connect with it. The smart way is to create a database class and use the constructor to automatically connect to the database when the object is created.

lib-db.php
<?php
class DB {
  protected $pdo = null;
  protected $stmt = null;
  public $error = "";
  public $lastID = null;

  function __construct() {
  // __construct() : connect to the database
  // PARAM : DB_HOST, DB_CHARSET, DB_NAME, DB_USER, DB_PASSWORD

    // ATTEMPT CONNECT
    try {
      $str = "mysql:host=" . DB_HOST . ";charset=" . DB_CHARSET;
      if (defined('DB_NAME')) { $str .= ";dbname=" . DB_NAME; }
      $this->pdo = new PDO(
        $str, DB_USER, DB_PASSWORD, [
          PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
          PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
          PDO::ATTR_EMULATE_PREPARES => false
        ]
      );
      return true;
    }

    // ERROR - DO SOMETHING HERE
    // THROW ERROR MESSAGE OR SOMETHING
    catch (Exception $ex) {
      print_r($ex);
      die();
    }
  }

  function __destruct(){
  // __destruct() : close connection when done

    if ($this->stmt!==null) { $this->stmt = null; }
    if ($this->pdo!==null) { $this->pdo = null; }
  }
}
?>

That’s it. When we create a new object with the above database class, it will automatically connect to the database, and disconnect when the script is done.

1-connect.php
<?php
require "config.php";
require "lib-db.php";
$pdoDB = new DB();
?>

 

 

2) CREATING A NEW DATABASE

Let us now add a new “create database” function to the library:

lib-db.php
function createDB($db="", $user=DB_USER, $pass=DB_PASSWORD) {
// createDB() : create a new database
// PARAM $db : name of new database
//       $user : user/owner of new database
//       $password : password

  $sql = "CREATE DATABASE `" . $db . "`;";
  $sql .= "CREATE USER '" . $user . "'@'localhost' IDENTIFIED BY '" . $pass . "';";
  $sql .= "GRANT ALL ON `" . $db . "`.* TO '" . $user . "'@'localhost';";
  $sql .= "FLUSH PRIVILEGES;";
  try {
    $this->pdo->exec($sql);
  } catch (Exception $ex) {
    $this->error = $ex;
    return false;
  }
  return true;
}

Yep, all we need to do now is to call this function to create a new dummy database.

2-create-db.php
<?php
// INIT
require "config.php";
require "lib-db.php";
$pdoDB = new DB();

// CREATE NEW DATABASE
if ($pdoDB->createDB("test")) {
  echo "OK";
} else {
  print_r($pdoDB->error);
}
?>

 

3) CREATING NEW TABLES

Now that we have successfully created a new database, we can uncomment that DB_NAME line in the config to directly use the database on connect.

config.php
// SET & ENABLE THIS TO DIRECTLY USE A SPECIFIED DATABASE
define('DB_NAME', 'test');

Next, we add another new function to the library that will create tables.

lib-db.php
function createTable($name="", $fields) {
// createTable() : create a new table
// PARAM $name : name of table
//       $fields : array of fields

  $sql = "CREATE TABLE " . $name . " (";
  foreach ($fields as $f) {
    $sql .= $f . ",";
  }
  $sql = substr($sql, 0, -1) . ");";
  try {
    $this->pdo->exec($sql);
  } catch (Exception $ex) {
    $this->error = $ex;
    return false;
  }
  return true;
}

Finally, we simply use the function to create a new dummy table:

3-create-table.php
<?php
// INIT
require "config.php";
require "lib-db.php";
$pdoDB = new DB();

// CREATE NEW TABLE
$fields = [
  "`user_id` int(11) AUTO_INCREMENT PRIMARY KEY",
  "`user_email` varchar(255) NOT NULL UNIQUE KEY",
  "`user_name` varchar(255) NOT NULL"
];
if ($pdoDB->createTable("users", $fields)) {
  echo "OK";
} else {
  print_r($pdoDB->error);
}
?>

 

4) INSERT, REPLACE, UPDATE, DELETE

Remember CRUD? Let us now deal with creating, updating, and deleting entries all in one fell swoop. Let us add yet another function to the database library:

lib-db.php
function exec($sql, $data=null) {
// exec() : run insert, replace, update, delete query
// PARAM $sql : SQL query
//       $data : array of data

  try {
    $this->stmt = $this->pdo->prepare($sql);
    $this->stmt->execute($data);
    $this->lastID = $this->pdo->lastInsertId();
  } catch (Exception $ex) {
    $this->error = $ex;
    return false;
  }
  $this->stmt = null;
  return true;
}

We can now pump the SQL in with this function:

4-CUD.php
<?php
// INIT
require "config.php";
require "lib-db.php";
$pdoDB = new DB();

// INSERT
$sql = "INSERT INTO `users` (`user_name`, `user_email`) VALUES (?, ?)";
$users = [
  ["John Doe", "john@doe.com"],
  ["Jane Doe", "jane@doe.com"],
  ["Robert Doe", "robert@doe.com"],
  ["Jordan Doe", "jordan@doe.com"],
  ["Mary Doe", "mary@doe.com"]
];
foreach ($users as $usr) {
  if ($pdoDB->exec($sql, $usr)) {
    echo "OK";
  } else {
    print_r($pdoDB->error);
  }
  echo "<br>";
}

// REPLACE
$sql = "REPLACE INTO `users` (`user_name`, `user_email`) VALUES (?, ?)";
$users = [
  ["Robert Doezzzzzzzzzzzzzzzzz", "robert@doe.com"],
  ["Rupert Doe", "rupert@doe.com"]
];
foreach ($users as $usr) {
  if ($pdoDB->exec($sql, $usr)) {
    echo "OK";
  } else {
    print_r($pdoDB->error);
  }
  echo "<br>";
}

// UPDATE
$sql = "UPDATE `users` SET `user_email`=? WHERE `user_id`=?";
$users = [
  ["johnnnnnnnnnnnnnnnnnnnn@doe.com", 1],
  ["janeeeeeeeeeeeeeeeeeeee@doe.com", 2]
];
foreach ($users as $usr) {
  if ($pdoDB->exec($sql, $usr)) {
    echo "OK";
  } else {
    print_r($pdoDB->error);
  }
  echo "<br>";
}

// DELETE
$sql = "DELETE FROM `users` WHERE `user_id`=5";
if ($pdoDB->exec($sql)) {
  echo "OK";
} else {
  print_r($pdoDB->error);
}
?>

 

 

5) SELECT & SEARCH

The “read” part of PDO is a little more complicated, and there are 2 ways to do it –

  • fetchAll will simply grab the results and throw them into an array.
  • fetch is mostly used in conjunction with while to loop through the results, so you can better rearrange the results.

Let us start by adding yet another function that will “fetch all”:

lib-db.php
function fetchAll ($sql, $cond=null, $key=null, $value=null) {
// fetchAll() : perform select query (multiple rows expected)
// PARAM $sql : SQL query
//       $cond : array of conditions
//       $key : sort in this $key=>data order, optional
//       $value : $key must be provided. If string provided, sort in $key=>$value order. If function provided, will be a custom sort.

  $result = [];
  try {
    $this->stmt = $this->pdo->prepare($sql);
    $this->stmt->execute($cond);
    // Sort in given order
    if (isset($key)) {
      if (isset($value)) {
        if (is_callable($value)) {
          while ($row = $this->stmt->fetch(PDO::FETCH_NAMED)) {
            $result[$row[$key]] = $value($row);
          }
        } else {
          while ($row = $this->stmt->fetch(PDO::FETCH_NAMED)) {
            $result[$row[$key]] = $row[$value];
          }
        }
      } else {
        while ($row = $this->stmt->fetch(PDO::FETCH_NAMED)) {
          $result[$row[$key]] = $row;
        }
      }
    }
    // No key-value sort order
    else {
      $result = $this->stmt->fetchAll();
    }
  } catch (Exception $ex) {
    $this->error = $ex;
    return false;
  }
  // Return result
  $this->stmt = null;
  return count($result)==0 ? false : $result ;
}

This may seem a little complicated, but that will actually give it some sorting capabilities:

5-read.php
<?php
// INIT
require "config.php";
require "lib-db.php";
$pdoDB = new DB();

// SIMPLE SELECT
// [N => DATA]
$sql = "SELECT * FROM `users` WHERE `user_id`=?";
$search = [1];
$results = $pdoDB->fetchAll($sql, $search);
print_r($results);
echo "<br>";

// SIMPLE SEARCH
// [N => DATA]
$sql = "SELECT * FROM `users` WHERE `user_name` LIKE (?)";
$search = ["%jo%"];
$results = $pdoDB->fetchAll($sql, $search);
print_r($results);
echo "<br>";

// RETURN THE SEARCH RESULTS WITH THE USER ID AS THE KEY
// [USER-ID => DATA]
$sql = "SELECT * FROM `users` WHERE `user_name` LIKE (?)";
$search = ["%jo%"];
$results = $pdoDB->fetchAll($sql, $search, "user_id");
print_r($results);
echo "<br>";

// RETURN THE SEARCH RESULTS IN THE FORMAT OF KEY => ARRAY
// [USER-ID => USER EMAIL]
$sql = "SELECT * FROM `users` WHERE `user_name` LIKE (?)";
$search = ["%jo%"];
$results = $pdoDB->fetchAll($sql, $search, "user_id", "user_email");
print_r($results);
echo "<br>";
?>

 

6) MORE SELECT

Need more selection yoga? Let us add more fetch functions to the library:

lib-db.php
function fetch ($sql, $cond=null, $sort=null) {
// fetch() : perform select query (single row expected)
//           returns an array of column => value
// PARAM $sql : SQL query
//       $cond : array of conditions
//       $sort : custom sort function

  $result = [];
  try {
    $this->stmt = $this->pdo->prepare($sql);
    $this->stmt->execute($cond);
    if (is_callable($sort)) {
      while ($row = $this->stmt->fetch(PDO::FETCH_NAMED)) {
        $result = $sort($row);
      }
    } else {
      while ($row = $this->stmt->fetch(PDO::FETCH_NAMED)) {
        $result = $row;
      }
    }
  } catch (Exception $ex) {
    $this->error = $ex;
    return false;
  }
  // Return result
  $this->stmt = null;
  return count($result)==0 ? false : $result ;
}

function fetchCol ($sql, $cond=null) {
// fetchCol() : yet another version of fetch that returns a flat array
// I.E. Good for one column SELECT `col` FROM `table`

  $this->stmt = $this->pdo->prepare($sql);
  $this->stmt->execute($cond);
  $result = $this->stmt->fetchAll(PDO::FETCH_COLUMN, 0);
  return count($result)==0 ? false : $result;
}
6-more-read.php
<?php
// INIT
require "config.php";
require "lib-db.php";
$pdoDB = new DB();

// FETCH SINGLE ROW - SINGLE USER
$sql = "SELECT * FROM `users` WHERE `user_id`=?";
$search = [1];
$user = $pdoDB->fetch($sql, $search);
print_r($user);
echo "<br>";

// FETCH SINGLE COLUMN - ALL THE EMAIL ADDRESSES
$sql = "SELECT `user_email` FROM `users`";
$email = $pdoDB->fetchCol($sql);
print_r($email);
?>

 

7) COMMIT AND ROLLBACK

When dealing with multiple records that span across different tables, it is the best to use auto-commit off – And only commit when the entire transaction is confirmed.

lib-db.php
function start () {
// start() : auto-commit off
  $this->pdo->beginTransaction();
}

function end ($commit=1) {
// end() : commit or roll back?

  if ($commit) { $this->pdo->commit(); }
  else { $this->pdo->rollBack(); }
} 
7-commit-off.php
<?php
// INIT
require "config.php";
require "lib-db.php";
$pdoDB = new DB();
$pdoDB->start(); // Start multiple transactions

// FIRST ENTRY
$sql = "INSERT INTO `users` (`user_name`, `user_email`) VALUES (?, ?)";
$data = ["jay@doe.com", "Jay Doe"];
$pass = $pdoDB->exec($sql, $data);

// SECOND ENTRY - ONLY IF THE FIRST IS OK
if ($pass) {
  $sql = "UPDATE `users` SET `user_name`=? WHERE `user_id`=?";
  $data = ["FOO BAR!", 4];
  $pass = $pdoDB->exec($sql, $data);
}

// YOU CAN RUN MORE QUERIES AS NEEDED
// END - COMMIT OR ROLLBACK
$pdoDB->end($pass);
echo $pass ? "OK" : "ERROR" ;
?>

 

 

EXTRA
USEFUL BITS

We have come to the end of this guide, and here are some small extras that you may be useful to use.

 

WHY CREATE A CLASS?

We could have just used PDO without creating a database class. So why did we go through all the trouble and “roundabout” way? Because of something called “object-oriented programming” and “code reuse“. With the database class above, we can now rapidly build new libraries. For example, we could have built a users class:

lib-users.php
<?php
class Users extends DB {
  function getAll() {
    $sql = "SELECT * FROM `users`";
    return $this->fetchAll($sql);
  }

  function getByEmail($email) {
    $sql = "SELECT * FROM `users` WHERE `user_email`=?";
    $search = [$email];
    return $this->fetch($sql, $search);
  }

  function add($name, $email) {
    $sql = "INSERT INTO `users` (`user_name`, `user_email`) VALUES (?, ?)";
    $cond = [$name, $email];
    return $this->exec($sql, $cond);
  }

  function edit($name, $email, $id) {
    $sql = "UPDATE `users` SET `user_name`=?, `user_email`=? WHERE `user_id`=?";
    $cond = [$name, $email, $id];
    return $this->exec($sql, $cond);
  }

  function delete($id) {
    $sql = "DELETE FROM `users` WHERE `user_id`=?";
    $cond = [$id];
    return $this->exec($sql, $cond);
  }
}
?>
extra-users.php
<?php
// INIT
require "config.php";
require "lib-db.php";
require "lib-users.php";
$pdoUSR = new Users();

// READY!
echo $pdoUSR->add("FOO DOE", "foo@doe.com") ? "OK" : "ERR" ;
echo "<br>";

echo $pdoUSR->edit("FOO BAR DOE", "foo@barz.com", 2) ? "OK" : "ERR" ;
echo "<br>";

$users = $pdoUSR->getAll();
print_r($users);
?>

Yep, the database class basically serve as our “quick start”, and all future development is pretty much writing more component libraries on top of it.

 

 

CHEAT SHEET

PHP PDO MySQL Basics (click to enlarge)

 

CLOSING
WHAT’S NEXT

Thank you for reading, and we have come to the end of this tutorial. I hope that it has helped you to better understand the database operations in PHP. If you have anything to share with this guide, please feel free to comment below. Good luck and happy coding!

Leave a Comment

Your email address will not be published. Required fields are marked *