PHP PDO MySQL

Created with Sketch.

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:

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

Leave a Reply

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