PDO Introduction

PDO Introduction What Is PDO? In simple terms: PDO (PHP Data Objects) is PHP's standard way to talk to databases. With one consistent set of classes and methods, you can work with MySQL, MariaDB, PostgreSQL, SQLite, SQL Server, and more. Learning PDO once means you can use the same code style with any of them.

Technically: PDO is a data-access abstraction layer. It provides a uniform API — connecting, preparing statements, binding parameters, fetching results, handling transactions and errors — while database-specific drivers (pdo_mysql, pdo_pgsql, pdo_sqlite, …) handle communication with each database. PDO does not translate SQL between databases: SQL dialect differences remain your responsibility.

Why PDO Is the Common Choice Advantage Explanation One API for many databases Switching between MySQL in production and SQLite in tests requires mostly configuration changes Named placeholders :email instead of ? makes queries with many parameters readable Flexible fetch modes Rows as arrays, objects, key-value pairs, grouped arrays, or instances of your classes Exceptions by default Since PHP 8.0, errors throw PDOException Industry standard Laravel, Symfony/Doctrine, and most libraries are built on PDO PDO vs MySQLi PDO MySQLi Databases Many (via drivers) MySQL / MariaDB only Placeholders ? and named :name ? only Fetching into objects of your classes PDO::FETCH_CLASS fetch_object($class) MySQL-specific features Most All (async queries, multi-statement helpers) API style Object-oriented Object-oriented and procedural Both are secure when used with prepared statements. The previous lessons used MySQLi; this part of the section shows the same tasks with PDO. For new projects, PDO is generally recommended.

The Three Core Classes Class Represents Examples of Methods PDO A database connection prepare(), query(), exec(), beginTransaction(), lastInsertId() PDOStatement A prepared statement and its result execute(), bindValue(), fetch(), fetchAll(), rowCount() PDOException A database error getMessage(), getCode(), errorInfo PHP 8.4 also added driver-specific subclasses such as Pdo\Mysql and Pdo\Sqlite, which expose features unique to each database.

Checking the Available Drivers <?php print_r(PDO::getAvailableDrivers()); Output (depends on the installed extensions) Array ( [0] => mysql [1] => sqlite ) The driver name must be installed and enabled for the database you want to use (on Ubuntu, php8.4-mysql provides pdo_mysql, and php8.4-sqlite3 provides pdo_sqlite).

A First Complete Example The following script works without a database server, because SQLite can run in memory. The same code works with MySQL by changing only the connection line.

<?php $pdo = new PDO('sqlite::memory:'); // MySQL: new PDO('mysql:host=127.0.0.1;dbname=gocourse_shop;charset=utf8mb4', $user, $password);

$pdo->exec('CREATE TABLE products ( id INTEGER PRIMARY KEY, sku TEXT NOT NULL UNIQUE, name TEXT NOT NULL, price_paise INTEGER NOT NULL )');

$insert = $pdo->prepare('INSERT INTO products (sku, name, price_paise) VALUES (:sku, :name, :price)');

foreach ([['PEN-01', 'Gel Pen', 2000], ['LAMP-01', 'Desk Lamp', 129900]] as [$sku, $name, $price]) { $insert->execute(['sku' => $sku, 'name' => $name, 'price' => $price]); }

echo "Last inserted ID: " . $pdo->lastInsertId() . "\n";

$select = $pdo->prepare('SELECT name, price_paise FROM products WHERE price_paise > :min ORDER BY price_paise'); $select->execute(['min' => 1000]);

foreach ($select->fetchAll(PDO::FETCH_ASSOC) as $row) { echo $row['name'] . ': ₹' . number_format($row['price_paise'] / 100, 2) . "\n"; } Output Last inserted ID: 2 Gel Pen: ₹20.00 Desk Lamp: ₹1,299.00 How It Works new PDO($dsn) opens a connection. The DSN (data source name) sqlite::memory: selects the SQLite driver and an in-memory database that disappears when the script ends. exec() runs a statement that returns no rows (here, CREATE TABLE). prepare() creates a statement with named placeholders (:sku, :name, :price); execute() receives the values as an associative array. The statement is prepared once and executed twice. lastInsertId() returns the ID of the last inserted row — as a string. The SELECT uses a placeholder as well; fetchAll(PDO::FETCH_ASSOC) returns all rows as associative arrays. Every step — connecting, preparing, executing, fetching — follows the same pattern for every database.

query(), exec(), and prepare() Method Use For Returns $pdo->exec($sql) Fixed statements without results (CREATE, fixed UPDATE) Number of affected rows $pdo->query($sql) Fixed SELECT statements without external values PDOStatement $pdo->prepare($sql) + execute($params) Any statement with values from outside the code PDOStatement Use exec() and query() only for SQL written entirely in your code. As soon as a value comes from a user, a file, an API, or even the database, use prepare() (see PDO Prepared Statements).

The PDO Lessons in This Section Lesson Topic PDO Connect DSNs, options, configuration, and connection errors PDO Prepared Statements Positional and named placeholders, binding, and security PDO Fetch Modes Getting rows as arrays, objects, key-value pairs, and groups PDO Transactions Making several changes succeed or fail together PDO Error Handling Exceptions, SQLSTATE codes, and translating database errors Common Misconceptions "PDO Makes My SQL Portable" PDO unifies the PHP API, not the SQL language. MySQL's LIMIT, PostgreSQL's RETURNING, and SQLite's type system differ. Query builders and ORMs (Doctrine DBAL, Laravel's query builder) generate database-specific SQL for you.

"PDO Automatically Prevents SQL Injection" Only when values are passed through placeholders. Concatenating input into SQL is just as dangerous with PDO as with any other API.

"PDO::query() Is Safe Because It Is PDO" query() executes the SQL string as given. Use it only for fixed SQL.

Best Practices Use PDO for new projects, especially if more than one database type may be involved. Use prepared statements for every value that does not come from your code. Configure the connection consistently (error mode, fetch mode, charset) in one place. Test with SQLite where appropriate, but keep in mind the SQL differences from MySQL. Move on to a query builder or ORM when an application's data access grows complex. Official reference: https://www.php.net/manual/en/intro.pdo.php