PHP PDO MySQL

Summary: in this tutorial, you will learn how to create a new sample database in MySQL for practicing with PDO.

Creating a database #

First, log in to the MySQL database using the root user from the Command Prompt on Windows or Terminal on macOS and Linux:

mysql -u rootCode language: Shell Session (shell)

And you’ll prompt for a password; enter the password for the root user and you’re connected to the MySQL Server.

Next, create the bookdb database by executing the following CREATE DATABASE statement:

CREATE DATABASE IF NOT EXISTS bookdb;Code language: SQL (Structured Query Language) (sql)

Then, select to the bookdb database:

USE bookdb;Code language: PHP (php)

After that, execute the following CREATE TABLE statement to create the publishers table:

CREATE TABLE IF NOT EXISTS publishers (
    publisher_id INT AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    PRIMARY KEY (publisher_id)
);Code language: SQL (Structured Query Language) (sql)

Finally, execute the following CREATE TABLE statement to create the books table:

CREATE TABLE IF NOT EXISTS books (
    book_id INT AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    isbn VARCHAR(13) NULL,
    published_date DATE NULL,
    publisher_id INT NULL,
    PRIMARY KEY (book_id),
    CONSTRAINT fk_publisher FOREIGN KEY (publisher_id)
        REFERENCES publishers (publisher_id)
);Code language: SQL (Structured Query Language) (sql)

Connecting to the bookdb database from PHP #

First, create a config.php file to store the database parameters:

<?php

$host     = 'localhost';
$db       = 'bookdb';
$user     = 'root';
$password = '';Code language: HTML, XML (xml)

Second, create a new connect.php file that connects to the bookdb database:

<?php

require 'config.php';

$dsn = "mysql:host=$host;dbname=$db;charset=UTF8";

try {
	$options = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION];

	$conn = new PDO($dsn, $user, $password, $options);

	if ($conn) {
		echo "Connected to the $db database successfully!";
	}
} catch (PDOException $e) {
	echo $e->getMessage();
}Code language: HTML, XML (xml)

To make it more reusable, you can define a function called connect() that returns a new database connection and return it from the connect.php file:

<?php

require_once 'config.php';

function connect($host, $db, $user, $password)
{
	$dsn = "mysql:host=$host;dbname=$db;charset=UTF8";

	try {
		$options = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION];

		return new PDO($dsn, $user, $password, $options);
	} catch (PDOException $e) {
		die($e->getMessage());
	}
}

return connect($host, $db, $user, $password);Code language: HTML, XML (xml)

To get the database connection in other script files, you use the require construct like this:

<?php

$pdo = require 'connect.php';

var_dump($pdo);Code language: HTML, XML (xml)

The require construct loads the connect.php file and returns the new instance of PDO returned by the connect() function.

Using the class-based approach #

To use a class instead of a function to create a new database connection, you can follow these steps:

First, create a new file called Connection.php and define the Connection class:

<?php

require_once 'config.php';

class Connection
{
	public static function make($host, $db, $user, $password)
	{
		$dsn = "mysql:host=$host;dbname=$db;charset=UTF8";

		try {
			$options = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION];

			return new PDO($dsn, $username, $password, $options);
		} catch (PDOException $e) {
			die($e->getMessage());
		}
	}
}

return Connection::make($host, $db, $user, $password);Code language: HTML, XML (xml)

The Connection class has the make() method that returns a new instance of PDO.

Second, use the Connection.php file in other script files as follows:

<?php

$pdo = require 'Connection.php';
var_dump($pdo);Code language: HTML, XML (xml)

In this tutorial, you have learned how to create the bookdb database in the MySQL server and developed a reusable script for connecting the database.