Checking Row Existence with PDO

When working with databases, it's often necessary to perform different actions depending on whether a row exists or not. This article explores how to efficiently check for the existence of a row using the popular PDO library in PHP.

PDO and Row Existence

PDO provides a flexible and efficient interface for interacting with various databases. One of its powerful features is the prepare() method, which allows you to create and execute prepared statements.

Checking with rowCount() and fetch()

Your initial approach using count($row) == 0 and $stmt->rowCount()

Solution: Direct Return Value Check

To check for row existence, you can simply examine the return value of execute(). If the statement executed successfully and returned no rows, execute() will return false. You can then perform the necessary actions:

$stmt = $conn->prepare('SELECT * FROM table WHERE ID=?');
$stmt->bindParam(1, $_GET['id'], PDO::PARAM_INT);
$stmt->execute();

if( ! $stmt->execute() ) {
    echo 'Row not found';
}

Checking Without Fetching

If you want to check row existence without actually fetching the row data, you can use MySQL's ability to return a value in the form of a count. This allows you to bind a variable inside execute() and check its value:

$sql = 'SELECT 1 FROM table WHERE ID = ? LIMIT 1';
$stmt = $conn->prepare($sql);
$stmt->execute([$_GET['id']]);

if($stmt->fetchColumn()) {
    echo 'Row found';
}

This approach is more efficient as it avoids unnecessary fetching of row data.