Databases
## Learning Objectives
- Connect to databases using PDO
- Execute queries safely with prepared statements
- Fetch data properly
- Handle transactions
- Understand database best practices
## PDO Overview
PDO (PHP Data Objects) provides a consistent interface for database access.
### Supported Databases
- MySQL
- PostgreSQL
- SQLite
- Oracle
- Microsoft SQL Server
- and many more
## Connecting to MySQL
### DSN (Data Source Name)
```php
```
### Creating Connection
```php
setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connected successfully";
} catch (PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
?>
```
### Connection Options
```php
PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
];
try {
$pdo = new PDO($dsn, $user, $password, $options);
} catch (PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
?>
```
### Common Fetch Modes
```php
value)
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
// Fetch as object
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);
// Fetch into class
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_CLASS);
// Fetch both (array + object)
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_BOTH);
?>
```
## Prepared Statements
### Why Prepared Statements?
- Prevents SQL injection
- Improves performance for repeated queries
- Separates SQL logic from data
### Query Without Prepared Statement (Unsafe)
```php
query($sql);
// Someone could input: ' OR '1'='1
// Result: SELECT * FROM users WHERE name = '' OR '1'='1'
?>
```
### Prepared Statement with Placeholders
```php
prepare("SELECT * FROM users WHERE id = :id");
$stmt->execute(['id' => 1]);
// Question mark placeholders
$stmt = $pdo->prepare("SELECT * FROM users WHERE name = ?");
$stmt->execute(["Alice"]);
?>
```
### execute() with Parameters
```php
prepare("
INSERT INTO users (name, email, age)
VALUES (:name, :email, :age)
");
$stmt->execute([
'name' => 'Alice',
'email' => 'alice@example.com',
'age' => 25
]);
// Or with bindParam()
$stmt = $pdo->prepare("SELECT * FROM users WHERE age > :min_age");
$stmt->bindParam(':min_age', $minAge, PDO::PARAM_INT);
$minAge = 18;
$stmt->execute();
?>
```
## SELECT Queries
### fetch() - Single Row
```php
prepare("SELECT * FROM users WHERE id = :id");
$stmt->execute(['id' => 1]);
$user = $stmt->fetch();
print_r($user);
// [id] => 1, [name] => Alice, [email] => alice@example.com
?>
```
### fetchAll() - All Rows
```php
prepare("SELECT * FROM users WHERE active = 1");
$stmt->execute();
$users = $stmt->fetchAll();
foreach ($users as $user) {
echo $user['name'] . "\n";
}
// With class
$stmt = $pdo->prepare("SELECT * FROM users");
$stmt->execute();
$users = $stmt->fetchAll(PDO::FETCH_CLASS, 'User');
?>
```
### fetchColumn() - Single Value
```php
prepare("SELECT COUNT(*) FROM users WHERE active = 1");
$stmt->execute();
$count = $stmt->fetchColumn();
echo "Active users: " . $count;
?>
```
### Fetch with Class
```php
prepare("SELECT * FROM users WHERE id = :id");
$stmt->setFetchMode(PDO::FETCH_CLASS, 'User');
$stmt->execute(['id' => 1]);
$user = $stmt->fetch();
echo $user->name; // Alice
?>
```
## INSERT Queries
### Basic Insert
```php
prepare("
INSERT INTO users (name, email, age)
VALUES (:name, :email, :age)
");
$stmt->execute([
'name' => 'Alice',
'email' => 'alice@example.com',
'age' => 25
]);
$newId = $pdo->lastInsertId();
echo "Created user with ID: " . $newId;
?>
```
### Multiple Inserts
```php
prepare("
INSERT INTO users (name, email)
VALUES (:name, :email)
");
$users = [
['name' => 'Alice', 'email' => 'alice@example.com'],
['name' => 'Bob', 'email' => 'bob@example.com'],
['name' => 'Charlie', 'email' => 'charlie@example.com']
];
$pdo->beginTransaction();
try {
foreach ($users as $user) {
$stmt->execute($user);
}
$pdo->commit();
echo "All users inserted";
} catch (Exception $e) {
$pdo->rollBack();
echo "Error: " . $e->getMessage();
}
?>
```
## UPDATE Queries
### Basic Update
```php
prepare("
UPDATE users
SET email = :email, age = :age
WHERE id = :id
");
$stmt->execute([
'email' => 'newemail@example.com',
'age' => 26,
'id' => 1
]);
$affected = $stmt->rowCount();
echo "Updated $affected rows";
?>
```
## DELETE Queries
### Basic Delete
```php
prepare("DELETE FROM users WHERE id = :id");
$stmt->execute(['id' => 1]);
$affected = $stmt->rowCount();
echo "Deleted $affected rows";
?>
```
### Safe Delete with Check
```php
prepare("DELETE FROM users WHERE id = :id AND active = 0");
$stmt->execute(['id' => 1]);
if ($stmt->rowCount() === 0) {
echo "No inactive user found with that ID";
}
?>
```
## Transactions
### Basic Transaction
```php
beginTransaction();
try {
// Deduct from one account
$stmt = $pdo->prepare("UPDATE accounts SET balance = balance - ? WHERE id = ?");
$stmt->execute([100, 1]);
// Add to another account
$stmt->execute([100, 2]);
$pdo->commit();
echo "Transfer complete";
} catch (Exception $e) {
$pdo->rollBack();
echo "Transfer failed: " . $e->getMessage();
}
?>
```
### Auto-Commit Mode
```php
setAttribute(PDO::ATTR_AUTOCOMMIT, false);
// ... perform multiple queries ...
// Re-enable auto-commit (commits if active)
$pdo->setAttribute(PDO::ATTR_AUTOCOMMIT, true);
?>
```
## Error Handling
### try-catch Block
```php
prepare("SELECT * FROM nonexistent_table");
$stmt->execute();
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
// For debugging (not in production!)
echo "SQL State: " . $e->errorInfo[0];
echo "Error Code: " . $e->getCode();
}
?>
```
### Error Info Array
```php
prepare("INVALID SQL");
try {
$stmt->execute();
} catch (PDOException $e) {
$errorInfo = $stmt->errorInfo();
print_r($errorInfo);
// [0] => 42000 (SQLSTATE)
// [1] => 1064 (MySQL error code)
// [2] => Syntax error...
}
?>
```
## Working with SQLite
### Connect to SQLite
```php
setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Create table
$pdo->exec("
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT UNIQUE
)
");
?>
```
### SQLite Operations
```php
prepare("INSERT INTO users (name, email) VALUES (:name, :email)");
$stmt->execute(['name' => 'Alice', 'email' => 'alice@example.com']);
// Select
$stmt = $pdo->query("SELECT * FROM users");
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>
```
## Working with PostgreSQL
### Connect to PostgreSQL
```php
getMessage();
}
?>
```
## Common Patterns
### User Registration
```php
prepare("SELECT id FROM users WHERE email = :email");
$stmt->execute(['email' => $email]);
if ($stmt->fetch()) {
return false; // Email already exists
}
// Hash password
$hashedPassword = password_hash($password, PASSWORD_DEFAULT);
// Insert new user
$stmt = $pdo->prepare("
INSERT INTO users (name, email, password)
VALUES (:name, :email, :password)
");
$stmt->execute([
'name' => $name,
'email' => $email,
'password' => $hashedPassword
]);
return true;
}
?>
```
### User Login
```php
prepare("SELECT * FROM users WHERE email = :email");
$stmt->execute(['email' => $email]);
$user = $stmt->fetch();
if (!$user) {
return null; // User not found
}
if (!password_verify($password, $user['password'])) {
return null; // Wrong password
}
return $user;
}
?>
```
### Pagination
```php
prepare("
SELECT * FROM users
ORDER BY created_at DESC
LIMIT :limit OFFSET :offset
");
$stmt->bindValue(':limit', $perPage, PDO::PARAM_INT);
$stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
$stmt->execute();
$users = $stmt->fetchAll();
// Get total for pagination
$totalStmt = $pdo->query("SELECT COUNT(*) FROM users");
$total = $totalStmt->fetchColumn();
$totalPages = ceil($total / $perPage);
?>
```
### Search with LIKE
```php
prepare("
SELECT * FROM products
WHERE name LIKE :search
OR description LIKE :search
ORDER BY name
");
$stmt->execute(['search' => $search]);
$results = $stmt->fetchAll();
?>
```
## Summary
- PDO provides unified database access interface
- Use DSN to connect: `$pdo = new PDO($dsn, $user, $pass)`
- Always use prepared statements to prevent SQL injection
- Named placeholders `:name` preferred over `?`
- `fetch()` returns one row; `fetchAll()` returns all rows
- `execute()` returns boolean; use `rowCount()` for affected rows
- Transactions with `beginTransaction()`, `commit()`, `rollBack()`
- `lastInsertId()` gets auto-increment ID after insert
- `password_hash()` and `password_verify()` for secure passwords
- Set `PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION` for error handling
Comments
Comments powered by Giscus
To enable comments, add your Giscus embed code here.
Learn more about Giscus →