Ch18, Login.php - Got A Problem When Replacing Mysqli With Pdo

[Christopher]

I am in the midst of replacing Ch18 scripts from using mysqli to PDO, but I got a problem here:

 

login.php

 

    if ($e && $p) { // If email and password are validated.

        // Prepare the first query on users table: (using Prepared Statements)
        $q = "SELECT user_id, first_name, DATE_FORMAT(last_login_time, '%a, %b %e at %l:%i%p') as f_last_login_time, last_login_time FROM users WHERE (email=:e AND pass=SHA1(: p)) AND active IS NULL";

        //$r = mysqli_query($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));
               

        $stmt = $pdo->prepare($q);

        $stmt->execute(array(':e' => $e, ':p' => $p));

        // Since $stmt->rowCount() is not suitable for counting number of rows in SELECT query...
         $rows = $stmt->fetchAll();
         $num_rows = count($rows);
                
        if ($num_rows == 1) { // A match was made in the user table
       //if (@mysqli_num_rows($r) == 1) { // A match was made in the user table

                        // Set the fetch mode:
                        //$stmt->setFetchMode(PDO::FETCH_ASSOC);

                        foreach( $rows as $row) {
                        // Register the values:
                        $_SESSION = $row;
                        }

 

The problem is for PDO, I have to use PDOStatement::fetchAll() to get all the rows in the result set and then use count() to get the number of the rows. But after that, when I need to fetch the matched 1 row to SESSION, $_SESSION = $row; is not correct!

 

When $rows = $stmt->fetchAll(); is executed, the value of $rows is kinda strange(I debugged in Netbeans), so later in the foreach() loop, $row is also not the correct data.

 

How to solve it?

 

Or put it this way, what is the code pattern for:

1. use Prepared Statement --> 2. get the affected SELECT row number --> 3. fetch the result

 

[Christopher]

Now I basically got the idea of PDO, will try to make a OO version of CH18 example later.

[Larry]

Awesome. Glad to hear it. Let us know if you have any other questions.