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

Need a 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

Need a 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

Need a 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

Need a hint?

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