PHP PDO how do i include fetch assoc and numrows

Question

trying to convert all my old mysql_* operations into new and, from what i've heard, improved PDO, but this query wont seem to run successfully, I am trying to select all from the table PEOPLE where the username = $username (which has previously been declared $username = $_SESSION['username'];)

$query = "SELECT  * FROM people WHERE username=?";
$stmt = $conn->prepare($query);
$stmt->bindParam(1, $username);   
$stmt->execute();
$num_rows = $stmt->fetchColumn();

if ($num_rows == 1) {
    // ...
}

THE WORKING CODE IS:

$query = "SELECT  * FROM people 
             WHERE username=?";
$stmt = $conn->prepare($query);
$stmt->bindParam(1, $username);   
$stmt->execute();
$num_rows = $stmt->fetchColumn();
$user = $stmt->fetchObject();

if ($user) {

//do something

}

Answer 1

$stmt->fetchColumn does not fetch the number of rows; in this case it will fetch the first column from the first row of the result set. Since that will not be equal to 1 generally your test will fail.

In this case there is also no real need to count the number of returned rows because you are expecting either one or zero (if the username does not exist). So you can simply do:

$stmt->execute();
$user = $stmt->fetchObject();
if (!$user) {
    // not found
}
else {
    echo "User $user->username found!";
}

The if(!$user) test works because if there is no row to fetch $user will be false (see the documentation for fetchObject).

Answer 2

$query = "SELECT * FROM people WHERE username = :username";
$stmt = $conn->prepare($query);
$stmt->bindParam(':username', $username);   
$stmt->execute();

while ($row = $stmt->fetchObject()) {
    // do stuff
}