How to fetch a row with PDO
Transitioning from the mysql_* extension to PDO is a standard move for any PHP developer, but it often introduces friction when trying to replicate familiar behaviors. The legacy functions were straightforward: $row = mysql_fetch_assoc($result) returned an array where keys matched column names. When you switch to PDO and attempt to execute fetchAll(PDO::FETCH_ASSOC), you might encounter a confusing mismatch between what you expect and what the object returns.
The specific issue in the provided code snippet lies in the choice of fetch method combined with the data structure returned. The line $row = $stmt->fetchAll(PDO::FETCH_ASSOC); executes a query that selects from a users table where a username matches a parameter. However, fetchAll() is designed to retrieve all remaining rows from the result set and wrap them in an array of arrays.
Consequently, $row is not a single associative array representing one user; it is an indexed array containing zero or more such rows. If no user exists, this variable becomes an empty array []. Accessing $row["username"] on an empty array triggers a fatal error because you cannot use string keys to index into a list of rows. Even if a user is found, attempting to access $row["username"] will fail because the first element would be accessed via $row[0]["username"], not $row["username"].
To fetch a single row exactly like mysql_fetch_assoc did, you must use the singular fetch() method instead of fetchAll(). This distinction is critical. fetch() retrieves the next row from the result set and returns it as an object or array depending on your configuration. It stops immediately after retrieving one record, which matches the behavior of the deprecated function you are emulating.
Here is how the corrected logic should look:
$stmt = $Database->prepare("SELECT * FROM users WHERE username=?");
$stmt->execute([$sUserCook]);
// Use fetch() to get a single row as an associative array
$row = $stmt->fetch(PDO::FETCH_ASSOC);
if ($row) {
echo $row["username"];
} else {
// Handle the case where no user was found
echo "No user found with that username.";
}
This approach ensures that $row is either an associative array containing the column names as keys, or false if the query returned zero rows. You can then safely check for the existence of data before accessing properties like $row["username"].
One common pitfall when migrating to PDO is assuming that execute() guarantees a result set exists. In your original code, if the user does not exist in the database, fetchAll() returns an empty array. Without checking if the row exists first, your script will crash when trying to access string keys on that empty array. Always wrap single-row fetches in a conditional check to handle the "not found" scenario gracefully.
Another consideration is the trade-off between fetch() and fetchAll(). If you are building an application that expects multiple records, such as a list of users, you should switch back to fetchAll(PDO::FETCH_ASSOC). The method you choose must align with your data consumption pattern: singular for one record, plural for lists.
For developers working within the Laravel ecosystem, this raw PDO usage is less common than using Eloquent ORM models or Query Builders. However, understanding these low-level interactions remains vital for debugging complex queries or optimizing performance bottlenecks where ORM abstractions add overhead. If you need to integrate custom database logic into a larger application, ensuring your fetch strategy matches the expected data volume prevents subtle bugs that are hard to trace.
Finally, remember that mysql_fetch_array returned arrays indexed by both column names and numeric keys by default in older versions. The modern equivalent in PDO requires explicit flags like PDO::FETCH_ASSOC to ensure you only get name-indexed arrays, avoiding ambiguity when your column names conflict with PHP's internal variable naming conventions.