Got an issue When Replacing Mysqli With PDO

PHP
1

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

This is the mysqli version of the code:


 
         if ($e && $p) { // If email and password are validated.
                               
                        // First query the users table:
                        $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\
<br />MySQL Error: " . mysqli_error($dbc));
        
                        if (@mysqli_num_rows($r) == 1) { // A match was made in the user table
                                
                                // Register the values:
                                $_SESSION = mysqli_fetch_array($r, MYSQLI_ASSOC);
           
                                
                                // Update last_login_time column when a User logs in                                  
                                $q = "UPDATE users SET last_login_time=NOW()";
                                 
                                $r = mysqli_query($dbc, $q) or trigger_error("Query: $q\
<br>MySQL Error: " . mysqli_error($dbc));
        
                                $test_affected_rows = mysqli_affected_rows($dbc); // get around debug issue
                                
                                if ($test_affected_rows != 1) {  
                       
                                        echo '<p class="error">There is some system error, please contact administrator!</p>';
                                                             
                                }
        
                                mysqli_free_result($r);   
                                mysqli_close($dbc);  
                                
                                // Redirect the user to loggedin_user.php page:
                                $url = BASE_URL . 'loggedin_user.php'; // Define the URL.
                                ob_end_clean(); // Delete the buffer.
                                header("Location: $url");
                                exit(); // Quit the script.

This is my updated PDO version:

    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\
<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 PDO:

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

I changed from mysqli to PDO mainly because I want to use prepared statements, though mysqli also support prepared statements, I found many people prefer PDO.

2

Please edit you question and replace the code tags with [PHP ][/PHP ] tags. The formatting will be considerably easier to read.

3

The basic problem is that $rows = $stmt->fetchAll() returns an array of array.


    $rows = $stmt->fetchAll();
    if (count($rows))
    {
        $row = $rows[0];
        $_SESSION = $row;

You need to initialize PDO so it returns assoc arrays by default:


$pdo = new PDO("mysql:host=localhost; dbname=DBNAME", 'USER', 'PASSWORD');
$pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC);

Because pdo throws exceptions you can remove most of your error checking. Truely improbably things like update failures can be caught using an application try/catch block. Makes your code much cleaner.

Finally, the herdoc notation is your friend and abbreviated aliases are evil:


    $q = <<<EOT
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=:email AND pass=SHA1(:pass) AND active IS NULL";
EOT;