PHP PDO Extension

Data Access Abstraction Layer

The PDO extension supports many databases via database-specific PDO drivers. PDO will work on 12 different database systems.

Each database driver implementing the PDO interface exposed database-specific features through regular extension functions.

No matter the database used, the same functions to query and fetch data are used with PDO.

PDO Extension

Glossary:

PHP Data Objects

PDO is an abstraction layer used for accessing databases.

Data Access Abstraction

Layer than uses same functions to issue queries and fetch data.

Object-oriented Programming

(OOP) focuses on objects, which can contain data and code to manipulate that data.

Dual Interface

Programming Paradigm

Name	Description	Example
Object-oriented Connection	Connect to database.	new PDO(“mysql:host=dbhost;dbname=dbname”, “dbuser”, “dbpass”)
Object-oriented Query	Select database table data.	$pdo->query(“SELECT * FROM tbname”)
Object-oriented Fetch Row	Fetch table row data.	$stmt->fetch(PDO::FETCH_ASSOC)
Name	Description	Example

MySQL SQL Code

CREATE DATABASE IF NOT EXISTS tutorial;
USE tutorial;
CREATE TABLE IF NOT EXISTS Students (
	sid INT NOT NULL AUTO_INCREMENT,
	name TEXT NOT NULL,
	age INT(3) NOT NULL,
	PRIMARY KEY (sid)
);
INSERT INTO Students(name, age)
SELECT * FROM (SELECT 'John', 23) AS tmp
WHERE NOT EXISTS (SELECT name FROM Students WHERE name = 'John' AND age = 23) LIMIT 1;
INSERT INTO Students(name, age)
SELECT * FROM (SELECT 'Jane', 24) AS tmp
WHERE NOT EXISTS (SELECT name FROM Students WHERE name = 'Jane' AND age = 24) LIMIT 1;
INSERT INTO Students(name, age)
SELECT * FROM (SELECT 'D\'Arcy', 35) AS tmp
WHERE NOT EXISTS (SELECT name FROM Students WHERE name = 'D\'Arcy' AND age = 35) LIMIT 1;

MySQL Run Code

 mysql -u dbuser -p < tutorial.sql
echo 'USE tutorial; SELECT * FROM Students;' | mysql -u dbuser -p 

SQLite SQL Code

CREATE TABLE IF NOT EXISTS Students (
	sid INTEGER PRIMARY KEY AUTOINCREMENT,
	name TEXT NOT NULL,
	age INT(3) NOT NULL
);
INSERT INTO Students(name, age)
SELECT * FROM (SELECT 'John', 23) AS tmp
WHERE NOT EXISTS (SELECT name FROM Students WHERE name = 'John' AND age = 23) LIMIT 1;
INSERT INTO Students(name, age)
SELECT * FROM (SELECT 'Jane', 24) AS tmp
WHERE NOT EXISTS (SELECT name FROM Students WHERE name = 'Jane' AND age = 24) LIMIT 1;
INSERT INTO Students(name, age)
SELECT * FROM (SELECT 'D''Arcy', 35) AS tmp
WHERE NOT EXISTS (SELECT name FROM Students WHERE name = 'D''Arcy' AND age = 35) LIMIT 1;

SQLite Run Code

sqlite3 tutorial.db < Students.sql
sqlite3 tutorial.db 'SELECT * FROM Students;'

PHP PDO Code

<?php
// Object-oriented Interface Connect And Fetch Single Row For MySQL Database
$pdo1 = new PDO("mysql:host=localhost;dbname=dbname", "dbuser", "dbpass");
$pdo1->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt1 = $pdo1->query("SELECT id FROM tbname LIMIT 1");
$stmt1->execute();
$result1 = $stmt1->fetch(PDO::FETCH_ASSOC);
echo $result1['id'];

// Object-oriented Interface Connect And Fetch Single Row For SQLite Database
$pdo2 = new PDO("sqlite:dbname.db");
$pdo2->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt2 = $pdo2->query("SELECT id FROM tbname LIMIT 1");
$stmt2->execute();
$result2 = $stmt2->fetch(PDO::FETCH_ASSOC);
echo $result2['id'];
?>

Explanation:

1. A variable is created for the database connection.
2. A new variable is created for the database query.
3. A final variable is created to store the fetch result.
4. The row result can then be output as an array value.

Usage

You can use any IDE or text editor and the command-line or a web browser to compile and execute PHP code. For this tutorial, the OjamboShop.com Learning PHP Course Web IDE can be used to input and compile PHP code for connection to a remote MySQL server. For security reasons, I do not recommend using a remote database connection.

Open Source

The PHP License is an open-source under which the PHP scripting language is released. Redistribution is permitted in source or binary form with or without modifications, consult the license for more specific details.

Live Stream

Every day, you can join a live stream and ask questions. Check Ojambo.com for details and instructions. Scroll down to the footer for the channels.

Learn Programming Courses:

Get the Learning PHP Course for your web browser on any device.

Learn Programming Books:

Learning PHP Book is available as Learning PHP Ebook.

Conclusion:

PHP PDO features an object-oriented interface to connect to multiple database via a database-specific driver such as the MySQL database. The object-oriented paradigm shows functions grouped by their purpose. In summary, PDO provides a unified interface for database interactions.

If you enjoy this article, consider supporting me by purchasing one of my WordPress Ojambo.com Plugins or programming OjamboShop.com Online Courses or publications at Edward Ojambo Programming Books or become a donor here Ojambo.com Donate

References:

• Customer Sets Price Plugin for WooCommerce on Ojambo.com
• Learning JavaScript Course on OjamboShop.com
• Learning Python Course on OjamboShop.com
• Learning PHP Course on OjamboShop.com
• Learning JavaScript Paperback on Amazon
• Learning JavaScript Ebook on Amazon
• Learning Python Ebook on Amazon
• Learning PHP Ebook on Amazon
• OjamboServices.com For Custom Websites, Applications & Tutorials