PHP MySQLi Vs PDO

Which PHP Database Extension Is Right For You?

The MySQLi extension features a dual interface. The PDO extension will work on 12 different database systems, not just MySQL.

The MySQLi interfaces can be switched but it is not recommended for recommended coding practices. Each database driver implementing the PDO interface exposed database-specific features through regular extension functions.

Some MySQLi procedural functions take a connection handle as their first argument, where others take it as an optional last argument. The object-oriented interface shows functions grouped by their purpose. No matter the database used, the same functions to query and fetch data are used with PDO.

MySQLi Extension

Glossary:

Procedural Programming

Imperatively breaking down the code into subroutines.

Object-oriented Programming

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

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.

Interfaces

Programming Paradigm

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

<?php
// Procedural Interface Connect And Fetch Single Row
$mysqli_proc = mysqli_connect("localhost", "dbuser", "dbpass", "dbname");
$result_proc = mysqli_query($mysqli_proc, "SELECT id FROM tbname LIMIT 1");
$row_proc = mysqli_fetch_assoc($result_proc);
echo $row_proc['id'];

// Object-oriented Interface Connect And Fetch Single Row
$mysqli_oop = new mysqli("localhost", "dbuser", "dbpass", "dbname");
$result_oop = $mysqli_oop->query("SELECT id FROM tbname LIMIT 1");
$row_oop = $result_oop->fetch_assoc();
echo $row_oop['id'];

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

MySQLi supports prepared statements to prevent SQL injection, better performance for MySQL-specific features, but is limited to MySQL databases. PDO is a database abstraction layer that supports 12 database systems, making it easier to switch between different database systems, and supports prepared statements.

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 MySQLi features a dual procedural and object-oriented interface to connect to a MySQL database. The procedural interface tables the connection handle as either a first or last argument depending on the function. The object-oriented paradigm shows functions grouped by their purpose.

PHP PDO features an object-oriented interface to connect to multiple database via a database-specific driver such as the MySQL database. 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