Why PDO is the standard in PHP - See It in Action

Why PDO is the standard

📖 Scenario: Imagine you are building a simple PHP application that needs to talk to a database. You want to do it in a way that is safe, easy to change later, and works with many types of databases.

🎯 Goal: You will create a PHP script that connects to a database using PDO, runs a simple query safely, and shows the results. This will help you understand why PDO is the standard way to work with databases in PHP.

📋 What You'll Learn

Create a PDO connection to a SQLite database

Write a SQL query using a prepared statement

Fetch and display results safely

Understand how PDO helps prevent SQL injection and supports multiple databases

💡 Why This Matters

🌍 Real World

Web applications often need to store and retrieve data safely and efficiently. PDO is the standard way in PHP to do this because it works with many databases and helps prevent security problems.

💼 Career

Knowing PDO is essential for PHP developers working on real-world projects, as it is widely used in professional environments for database access and security.

1

Create a PDO connection

Create a variable called $pdo and set it to a new PDO connection to an in-memory SQLite database using new PDO('sqlite::memory:').

PHP

# Create a PDO connection to an in-memory SQLite database
# Your code here

Hint

Use new PDO('sqlite::memory:') to create a temporary database in memory.

2

Create a table and insert data

Use the $pdo variable to execute SQL commands that create a table called users with columns id and name, and insert two rows: (1, 'Alice') and (2, 'Bob').

PHP

<?php
$pdo = new PDO('sqlite::memory:');
# Create a table called users and insert two rows
# Your code here

Hint

Use $pdo->exec() to run SQL commands that do not return data.

3

Prepare and execute a safe query

Use $pdo to prepare a SQL statement that selects the name from users where id equals a placeholder :id. Then execute the statement with [':id' => 1] to get the user with id 1.

PHP

exec("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)");
$pdo->exec("INSERT INTO users (id, name) VALUES (1, 'Alice')");
$pdo->exec("INSERT INTO users (id, name) VALUES (2, 'Bob')");
# Prepare a safe SQL query to select name where id = :id
# Your code here

Hint

Use prepare() and execute() with an array to safely insert values.

4

Fetch and display the result

Fetch the result from $stmt using fetch() and print the user's name using print().

PHP

exec("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)");
$pdo->exec("INSERT INTO users (id, name) VALUES (1, 'Alice')");
$pdo->exec("INSERT INTO users (id, name) VALUES (2, 'Bob')");
$stmt = $pdo->prepare("SELECT name FROM users WHERE id = :id");
$stmt->execute([':id' => 1]);
# Fetch the result and print the name
# Your code here

Hint

Use fetch(PDO::FETCH_ASSOC) to get an associative array and then print the 'name' key.

Practice

1. Why is PDO considered the standard way to access databases in PHP?

easy

A. Because it supports prepared statements to prevent SQL injection

B. Because it only works with MySQL databases

C. Because it requires no configuration to connect to any database

D. Because it automatically creates database tables

Solution

1. Step 1: Understand PDO's security feature

   PDO uses prepared statements which separate SQL code from data, preventing attackers from injecting harmful SQL.

2. Step 2: Compare with other options

   PDO works with many databases, requires configuration, and does not create tables automatically.

3. Final Answer:

   Because it supports prepared statements to prevent SQL injection -> Option A

4. Quick Check:

   PDO security = prepared statements [OK]

Hint: Remember PDO protects with prepared statements [OK]

Common Mistakes:

• Thinking PDO only works with MySQL
• Believing PDO auto-creates tables
• Assuming PDO needs no setup

2. Which of the following is the correct way to create a PDO connection in PHP?

easy

A. $pdo = new PDO('mysql://user:pass@localhost/testdb');

B. $pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'pass');

C. $pdo = new PDO('connect:mysql://localhost/testdb', 'user', 'pass');

D. $pdo = new PDO('localhost', 'user', 'pass', 'testdb');

Solution

1. Step 1: Recall PDO constructor syntax

   The PDO constructor requires a DSN string like 'mysql:host=localhost;dbname=testdb' plus username and password.

2. Step 2: Check each option

   Only $pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'pass'); uses the correct DSN format and parameters.

3. Final Answer:

   $pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'pass'); -> Option B

4. Quick Check:

   PDO connection = DSN string + user/pass [OK]

Hint: DSN string format is 'driver:host=...;dbname=...' [OK]

Common Mistakes:

• Using wrong DSN format
• Passing parameters in wrong order
• Confusing DSN with URL

3. What will be the output of this PHP code using PDO?

$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE users (id INTEGER, name TEXT)');
$pdo->exec("INSERT INTO users VALUES (1, 'Alice')");
$stmt = $pdo->prepare('SELECT name FROM users WHERE id = :id');
$stmt->execute([':id' => 1]);
echo $stmt->fetchColumn();

medium

A. name

B. 1

C. Alice

D. Error: no such table

Solution

1. Step 1: Understand the code flow

   The code creates an in-memory SQLite database, a table 'users', inserts one row with id=1 and name='Alice'.

2. Step 2: Analyze the prepared statement

   The statement selects the 'name' where id=1, then fetchColumn() returns the first column of the first row, which is 'Alice'.

3. Final Answer:

   Alice -> Option C

4. Quick Check:

   PDO fetchColumn() returns 'Alice' [OK]

Hint: fetchColumn() returns first column of first row [OK]

Common Mistakes:

• Expecting the id instead of name
• Confusing fetchColumn() with fetchAll()
• Assuming table does not exist

4. Identify the error in this PDO code snippet:

$pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'pass');
$stmt = $pdo->prepare('SELECT * FROM users WHERE id = ?');
$stmt->execute(['id' => 5]);
$result = $stmt->fetchAll();
print_r($result);

medium

A. No error, code runs fine

B. Missing database connection

C. Wrong method name 'fetchAll' instead of 'fetch_all'

D. Using named parameter array with positional placeholder

Solution

1. Step 1: Check placeholder and execute parameter usage

   The SQL uses a positional placeholder '?', but execute() is called with an associative array ['id' => 5].

2. Step 2: Understand PDO parameter binding rules

   Positional placeholders require a numeric-indexed array, not associative keys.

3. Final Answer:

   Using named parameter array with positional placeholder -> Option D

4. Quick Check:

   Positional placeholder needs numeric array [OK]

Hint: Match placeholder type with execute() array keys [OK]

Common Mistakes:

• Mixing named and positional placeholders
• Assuming fetchAll() is misspelled
• Ignoring connection setup

5. You want to write PHP code that can switch easily between MySQL and SQLite databases without changing query code. Why is PDO the best choice for this?

hard

A. PDO provides a consistent interface and supports multiple databases with the same methods

B. PDO automatically converts MySQL queries to SQLite syntax

C. PDO requires no changes because it uses raw SQL strings only

D. PDO stores all data in memory for faster access

Solution

1. Step 1: Understand PDO's database abstraction

   PDO offers a uniform API to connect and query different databases like MySQL and SQLite.

2. Step 2: Evaluate other options

   PDO does not convert SQL syntax automatically, nor store data in memory, and raw SQL strings alone don't ensure portability.

3. Final Answer:

   PDO provides a consistent interface and supports multiple databases with the same methods -> Option A

4. Quick Check:

   PDO abstraction = easy DB switching [OK]

Hint: PDO abstracts DB differences with one API [OK]

Common Mistakes:

• Thinking PDO converts SQL syntax automatically
• Believing PDO stores data in memory
• Assuming raw SQL means no changes needed