How to perform prepared queries with PDO?

To perform prepared queries with PDO (PHP Data Objects), you need to follow a series of steps that ensure security, efficiency, and reliability. Prepared statements use bound parameters, which enhances security by preventing SQL injection attacks. Here’s a detailed guide on how to perform prepared queries along with examples and sources.

Step-by-Step Guide

1. Establish a Connection: First, you need to create a PDO instance to connect to your database. Make sure to use the appropriate Data Source Name (DSN) for your database type.

```
$dsn = ‘mysql:host=localhost;dbname=testdb’;
$username = ‘root’;
$password = ‘password’;
$options = [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
];

try { $pdo = new PDO;
} catch (PDOException $e) { die(“Database connection failed: “ . $e->getMessage());
}
```

1. Prepare the Statement: Using the `prepare()` method of the PDO instance, you can prepare an SQL query. This method returns a PDO statement object.

```
$sql = “SELECT * FROM users WHERE email = :email”;
$stmt = $pdo->prepare($sql);
```

1. Bind Parameters: Binding parameters can be done using the `bindParam()` or `bindValue()` methods. `bindParam()` is used for input parameters, binding the variable name, while `bindValue()` binds the value directly. For simplicity, we generally use `bindValue()`.

```
$email = ‘example@gmail.com’;
$stmt->bindValue(‘:email’, $email, PDO::PARAM_STR);
```

1. Execute the Statement: After binding the parameters, execute the statement using the `execute()` method.

```
$stmt->execute();
```

1. Fetch the Results: Fetch the results using one of the fetch methods like `fetch()`, `fetchAll()`, etc.

```
$results = $stmt->fetchAll();
foreach ($results as $row) { print_r($row);
}
```

Example with INSERT Query

Here is a complete example showing how to use prepared statements to insert data into the database:

```
try { $dsn = ‘mysql:host=localhost;dbname=testdb’; $username = ‘root’; $password = ‘password’; $options = [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, ];

$pdo = new PDO; $sql = “INSERT INTO users (name, email, age) VALUES (:name, :email, :age)”; $stmt = $pdo->prepare($sql); $name = ‘John Doe’; $email = ‘johndoe@gmail.com’; $age = 30; $stmt->bindValue(‘:name’, $name, PDO::PARAM_STR); $stmt->bindValue(‘:email’, $email, PDO::PARAM_STR); $stmt->bindValue(‘:age’, $age, PDO::PARAM_INT); $stmt->execute(); echo “User successfully added!”; } catch (PDOException $e) { die(“Database error: “ . $e->getMessage()); } ```

Sources
- PHP Manual – PDO: The official PHP documentation provides in-depth resources and examples on using PDO for database operations, including prepared statements ([PHP PDO Manual](https://www.php.net/manual/en/book.pdo.php)).
- PHP Delusions – PDO Tutorial: A comprehensive guide with practical examples on how to use PDO effectively, which includes explanations and best practices ([PDO Tutorial for MySQL Developers](https://phpdelusions.net/pdo)).

Using these steps and best practices, prepared statements with PDO help ensure that your database operations are both secure and efficient.