PHP PDO WHERE - Filtering Data

Filtering Data (WHERE)

When we want to retrieve only specific records from the database (e.g., a user with a specific last name), we use the WHERE clause. In PDO, we always do this with prepared statements to prevent the injection of malicious code.

EXAMPLE

<?php
$server = "localhost";
$username = "root";
$password = "";
$database = "pdo_database";

try {
    $connection = new PDO("mysql:host=$server;dbname=$database;charset=utf8mb4", $username, $password);
    $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // STEP 1: The value we are looking for
    // Never insert variables directly into the SQL statement!
    $search_last_name = "Smith";

    // STEP 2: Preparing the query with WHERE
    // We use a named parameter (:last_name) for maximum security.
    $statement = $connection->prepare("SELECT id, first_name, last_name FROM users WHERE last_name = :last_name");
    
    // STEP 3: Secure execution (Binding & Execute)
    // Instead of bindParam, we can pass parameters directly into execute() as an array.
    $statement->execute(['last_name' => $search_last_name]);

    // STEP 4: Fetching a single result
    // We use fetch() because we are looking for only one user (or the first one that matches the condition).
    $result = $statement->fetch(PDO::FETCH_ASSOC);

    // STEP 5: Checking the result
    if ($result) {
        echo "User found: " . $result['first_name'] . " " . $result['last_name'] . " (ID: " . $result['id'] . ")";
    } else {
        echo "A user with this last name does not exist.";
    }

} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

$connection = null;
?>

RESULT

The result will be displayed here...

In the example above, we searched our table of 11 records for a user with the last name "Novak". We will use a named parameter :last_name, which ensures security and clarity.

Always use prepare() when including variables in a WHERE clause.

The execute() method accepts parameters and executes the query securely.

If you expect only one result, you can use fetch() instead of fetchAll().