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