SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Jan 2014
    Location
    LA, USA
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Got an issue When Replacing Mysqli With PDO

    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:
    PHP 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\n<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($rMYSQLI_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\n<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:

    PHP Code:
        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 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.
    Last edited by SpacePhoenix; Feb 6, 2014 at 07:48. Reason: placed php tags around php code

  2. #2
    SitePoint Guru
    Join Date
    Nov 2003
    Location
    Huntsville AL
    Posts
    664
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Please edit you question and replace the code tags with [PHP ][/PHP ] tags. The formatting will be considerably easier to read.

  3. #3
    SitePoint Guru
    Join Date
    Nov 2003
    Location
    Huntsville AL
    Posts
    664
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    The basic problem is that $rows = $stmt->fetchAll() returns an array of array.

    PHP Code:
        $rows $stmt->fetchAll();
        if (
    count($rows))
        {
            
    $row $rows[0];
            
    $_SESSION $row
    You need to initialize PDO so it returns assoc arrays by default:
    PHP Code:
    $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:
    PHP Code:
        $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; 


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •