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 root
Code 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:
$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:
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:
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:
$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:
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:
$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.