SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,531
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Prepared Statement Failing

    Any idea why the Update query below is failing?

    It keeps coming up "TRUE" and ending up where my echo statement is...


    PHP Code:
        // Initialize variables.
        
    $loggedIn 1;
        
    $memberID 19;


        
    // Connect to the database.
        
    require_once(WEB_ROOT 'private/mysqli_connect.php');

        
    // Build query.
        
    $q1 "UPDATE member
                SET logged_in=?,
                    last_activity=now()
                WHERE id=?
                LIMIT 1"
    ;

        
    // Prepare statement.
        
    $stmt1 mysqli_prepare($dbc$q1);

        
    // Bind variables to query.
        
    mysqli_stmt_bind_param($stmt1'ii'$loggedIn$memberID);

        
    // Execute query.
        
    mysqli_stmt_execute($stmt1);

        
    // Verify Update.
        
    if (mysqli_stmt_affected_rows($stmt1)!==1){
            
    // Update Failed.
            
    $_SESSION['resultsCode'] = 'MEMBER_UPDATE_FAILED_2126';
    echo 
    'HERE';
        }
    //End of UPDATE MEMBER RECORD 
    Thanks,


    Debbie

  2. #2
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    2 Thread(s)
    Debbie, I would like to help but I've never used mysqli... I use PDO
    ictus==""

  3. #3
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,531
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ServerStorm View Post
    Debbie, I would like to help but I've never used mysqli... I use PDO
    If you want to help, check out my other PHP thread on Redirects. (That seems to be the problem...)

    As far as PDO, maybe sometime soon?!


    Debbie

  4. #4
    @php.net Salathe's Avatar
    Join Date
    Dec 2004
    Location
    Edinburgh
    Posts
    1,396
    Mentioned
    54 Post(s)
    Tagged
    0 Thread(s)
    Debbie, are you getting any other errors? (make sure your script is set to show them all) Since mysqli_stmt_affected_rows() isn't giving 1, check to see what it is giving. Also, a common cause of what you're seeing is due to the preparing failing for some reason.

    Try your script but with a little more feedback when things go wrong.

    PHP Code:
        error_reporting(-1);
        
    ini_set('display_errors''On');

        
    // Initialize variables. 
        
    $loggedIn 1
        
    $memberID 19


        
    // Connect to the database. 
        
    require_once(WEB_ROOT 'private/mysqli_connect.php'); 

        
    // Build query. 
        
    $q1 "UPDATE member 
                SET logged_in=?, 
                    last_activity=now() 
                WHERE id=? 
                LIMIT 1"


        
    // Prepare statement. 
        
    $stmt1 mysqli_prepare($dbc$q1); 
        if ( ! 
    $stmt1) {
            die(
    "Prepare failed: " mysqli_error($dbc));
        }

        
    // Bind variables to query. 
        
    mysqli_stmt_bind_param($stmt1'ii'$loggedIn$memberID); 

        
    // Execute query. 
        
    mysqli_stmt_execute($stmt1); 

        
    // Verify Update. 
        
    $affected_rows mysqli_stmt_affected_rows($stmt1);
        if (
    $affected_rows !== 1) { 
            echo 
    "Affected rows: ";
            
    var_dump($affected_rows);

            
    // Update Failed. 
            
    $_SESSION['resultsCode'] = 'MEMBER_UPDATE_FAILED_2126'

            echo 
    'HERE'
        }
    //End of UPDATE MEMBER RECORD 
    Salathe
    Software Developer and PHP Manual Author.

  5. #5
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,531
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Salathe View Post
    Debbie, are you getting any other errors?
    No, and the one error I do get isn't really an error - it is just my own custom error to show what happens when things go wrong.


    (make sure your script is set to show them all)
    I have a debugger in my IDE...


    Since mysqli_stmt_affected_rows() isn't giving 1, check to see what it is giving.
    I already had similar code to what you suggested above, and I get back a zero "0" when I run things, which is strange because after logging in I see the following in phpMyAdmin...
    logged_in = 1
    last_activity = 2012-03-10 11:52:01
    Which means the UPDATE query DID run successfully and log me in.

    But then why doesn't my code reflect that as well?!

    This is soooo frustrating!!!


    Debbie

  6. #6
    @php.net Salathe's Avatar
    Join Date
    Dec 2004
    Location
    Edinburgh
    Posts
    1,396
    Mentioned
    54 Post(s)
    Tagged
    0 Thread(s)
    This might be a silly question, are you sure logged_in is ever being set to anything other than 1 for the member 19? The 0 returned from mysqli_stmt_affected_rows() indicates that no rows are being updated.
    Salathe
    Software Developer and PHP Manual Author.

  7. #7
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,531
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Salathe,

    My website is set up to capture the $returnToPage on most pages, so that when you log in, the log in script will redirect back to the page you were on before needing to log in.

    Let's say I am on "index.php" and I click on the "Log In" link in the page header. When I click "Log In" on the form, I see...
    System Error

    A Fatal Error has occurred. Please contact the System Administrator. (2126)
    ...and that comes from "http://local.debbie/members/results.php"

    If I click on another tab (e.g. How-To Guides) and then I click back on the original tab I was on (e.g. "index.php") then that page appears properly.

    So while my Prepared Statement is running successfully and updating the Member record as it should, mysqli_stmt_affected_rows is not reflecting that record was updated?!

    This is soooo frustrating?!


    Debbie

  8. #8
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,531
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Salathe View Post
    This might be a silly question, are you sure logged_in is ever being set to anything other than 1 for the member 19? The 0 returned from mysqli_stmt_affected_rows() indicates that no rows are being updated.
    Here is a sequence of what is going on right now...

    - In phpMyAdmin...
    Code:
    id=19
    username=DoubleDee
    logged_in = 0
    last_activity = 2012-03-10 12:13:35

    - On index.php I see...
    Hello. Log In to access premium content. Not a Member? Start Here
    - I click on "Log In"

    - I am taken to "http://local.debbie/members/log_in.php"

    - After refreshing phpMyAdmin, I see...
    Code:
    id=19
    username=DoubleDee
    logged_in = 0
    last_activity = 2012-03-10 12:13:35

    - Fill out form and click "Log In" button

    - I am taken to "http://local.debbie/members/results.php"

    - In the page header I see...
    Hello, Debbie!! My Account Help Log Out
    ...which means I am logged in!!

    - Below this I see the error message...
    System Error

    A Fatal Error has occurred. Please contact the System Administrator. (2126)
    ...which is caused by this code not working properly...
    PHP Code:
        // Verify Update.
        
    if (mysqli_stmt_affected_rows($stmt)!==1){

            
    // Update Failed.
            
    $_SESSION['resultsCode'] = 'MEMBER_UPDATE_FAILED_2126';

            
    // Redirect to Display Outcome.
            
    header("Location: " BASE_URL "/members/results.php");

            
    // End script.
            
    exit();
        }
    //End of UPDATE MEMBER RECORD 
    - After refreshing phpMyAdmin, I see...
    Code:
    id=19
    username=DoubleDee
    logged_in = 1
    last_activity = 2012-03-10 12:18:37

    ...which means I am logged in!!

    - If I click on the "Home" tab, I am taken to "http://local.debbie/index.php"

    - In the page header I see...
    Hello, Debbie!! My Account Help Log Out
    ...which means I am logged in!!

    - Below this I see the normal Home Page.

    - I can click on any links from there and everything *seems* okay.


    Strange, huh?!


    Debbie

  9. #9
    @php.net Salathe's Avatar
    Join Date
    Dec 2004
    Location
    Edinburgh
    Posts
    1,396
    Mentioned
    54 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DoubleDee View Post

    - Below this I see the error message...

    System Error

    A Fatal Error has occurred. Please contact the System Administrator. (2126)
    ...which is caused by this code not working properly...
    PHP Code:
        // Verify Update.
        
    if (mysqli_stmt_affected_rows($stmt)!==1){

            
    // Update Failed.
            
    $_SESSION['resultsCode'] = 'MEMBER_UPDATE_FAILED_2126';

            
    // Redirect to Display Outcome.
            
    header("Location: " BASE_URL "/members/results.php");

            
    // End script.
            
    exit();
        }
    //End of UPDATE MEMBER RECORD 
    From your description there is nothing wrong with the updating of your row in the database, which means the query isn't failing. Since the query isn't failing, I don't see how (if, as you say, the row is getting updated) the affected rows wouldn't equal 1. Unless the query is being executed more than once (on the same page, or different page loads).

    I don't see anything particularly strange about what's happening, something else is happening that we can't see yet. A little more to go on would be nice, could you show the full page?
    Salathe
    Software Developer and PHP Manual Author.


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
  •