🐘 PDO Crash Course

PDO (PHP Data Objects) is a database access layer providing a uniform method of access to multiple databases. It is a secure and efficient way to interact with databases in PHP.

1. Connecting to a Database

<?php
$host = 'localhost';
$dbname = 'your_database_name';
$username = 'your_username';
$password = 'your_password';

try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully";
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}
?>

The line $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); sets the error handling mode for PDO. Specifically, it sets PDO to throw exceptions when errors occur.

• PDO::ATTR_ERRMODE: This is an attribute that controls the error reporting mode.
• PDO::ERRMODE_EXCEPTION: This mode causes PDO to throw exceptions when errors occur, making it easier to handle errors in your code.

So, if there's an error in a PDO operation, it will throw an exception, and you can catch that exception to handle errors more gracefully.

2. Executing Queries

• SELECT Statement

  $stmt = $pdo->prepare('SELECT * FROM your_table_name');
  $stmt->execute();
  
  $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
  
  foreach ($result as $row) {
      // Process each row as needed
      echo $row['column1'] . "\t" . $row['column2'] . "\n";
  }
  

  • fetchAll: This method is used to fetch all rows from the result set returned by a prepared statement. It retrieves all the rows into an array.
  • PDO::FETCH_ASSOC: This is a fetch style constant provided by PDO that specifies the format in which the rows should be fetched. In this case, FETCH_ASSOC indicates that the rows should be fetched as an associative array where the column names are used as keys.

• INSERT Statement:

  $data = ['value1', 'value2'];
  $stmt = $pdo->prepare('INSERT INTO your_table_name (column1, column2) VALUES (?, ?)');
  $stmt->execute($data);
  echo "Data inserted successfully";
  

• UPDATE Statement:

  $newValue = 'new_value';
  $id = 1;
  $stmt = $pdo->prepare('UPDATE your_table_name SET column1 = ? WHERE id = ?');
  $stmt->execute([$newValue, $id]);
  echo "Data updated successfully";
  
  

• DELETE Statement:

  $id = 1;
  $stmt = $pdo->prepare('DELETE FROM your_table_name WHERE id = ?');
  $stmt->execute([$id]);
  echo "Data deleted successfully";
  

3. Prepared Statements and Parameter Binding:

Prepared statements help prevent SQL injection by separating SQL code from user input.

$username = 'user';
$password = 'pass';

$stmt = $pdo->prepare('SELECT * FROM users WHERE username = :username AND password = :password');
$stmt->bindParam(':username', $username);
$stmt->bindParam(':password', $password);
$stmt->execute();

$result = $stmt->fetch(PDO::FETCH_ASSOC);

In this example, placeholders (:username and :password) are used in the query, and then values are bound to these placeholders using bindParam. This helps prevent SQL injection and allows for better reuse of the prepared statement.

4. Error Handling

Always handle errors to ensure a robust application.