Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,057
    Thanks
    25
    Thanked 0 Times in 0 Posts

    Multiple Prepared Statements Failing

    How do I get two Prepared Statements that are back-to-back in the same script to work?

    When I run the code below I get this error:
    Warning: mysqli_prepare() [function.mysqli-prepare]: All data must be fetched before a new statement prepare takes place in /Users/user1/Documents/DEV/++htdocs/01_MyProject/activate.php on line 101

    Here is my code...

    PHP Code:
                            // Handle Activation Codes.
                            
    if ($x){
                                
    // Activation succeeded.

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

                                
    // Get Member's Name.
                                
    $n "SELECT first_name
                                            FROM member
                                            WHERE activation_code=?"
    ;

                                
    // Prepare statement.
                                
    $n_stmt mysqli_prepare($dbc$n);

                                
    // Bind variable.
                                
    mysqli_stmt_bind_param($n_stmt's'$x);

                                
    // Execute query.
                                
    mysqli_stmt_execute($n_stmt);

                                
    // Check for Member's Name.
                                
    if (mysqli_stmt_num_rows($n_stmt)==1){
                                    
    // Member's Name in Database.

                                    // Bind result-set to variables.
                                    
    mysqli_stmt_bind_result($n_stmt$firstName);

                                    
    // Fetch record.
                                    
    mysqli_stmt_fetch($n_stmt);

                                    echo 
    '<p>$firstName</p>';

                                    exit();
                                    
                                }

                                
    // Update Member Account.
                                // Build query.
                                
    $q "UPDATE member
                                            SET activation_code=NULL,
                                                    activated_on=NOW(),
                                                    updated_on=NOW()
                                            WHERE (activation_code=?)
                                            LIMIT 1"
    ;

                                
    // Prepare statement.
                                
    $stmt mysqli_prepare($dbc$q);

                                
    // Bind variable.
                                
    mysqli_stmt_bind_param($stmt's'$x);

                                
    // Execute query.
                                
    mysqli_stmt_execute($stmt);

                                
    // Verify update.
                                
    if (mysqli_stmt_affected_rows($stmt)==1){
                                    
    // Update succeeded.
                                    
    echo '<h1>Activation Succeeded</h1>';
                                    echo 
    '<p>Your Member Account has been activated!';
                                    echo 
    '<p>To log in to your new account, <a href="' WEB_ROOT 'log_in.php">click here</a>.</p>';
                                }else{
                                    
    // Update failed.
                                    
    echo '<h1>Activation Failed 1</h1>';
                                    echo 
    '<p>Your Member Account could not be activated.</p>';
                                    echo 
    '<p>Please re-check the link or contact the System Administrator.</p>';
                                }

                                
    // Close prepared statement.
                                
    mysqli_stmt_close($stmt);

                                
    // Close the connection.
                                
    mysqli_close($dbc); 

    I am admittedly still shaky on Prepared Statements...

    What I am trying to do is customize the message above so the user gets something like this:

    Activation Succeeded
    Debbie, your Member Account has been activated!
    To log in to your new account, click here.

    The code above is obviously not finished...


    Sincerely,



    Debbie

  • #2
    Supreme Overlord Spookster's Avatar
    Join Date
    May 2002
    Location
    Marion, IA USA
    Posts
    6,278
    Thanks
    4
    Thanked 83 Times in 82 Posts
    Well just like the error says. You need to fetch all the records first. You would need to put your fetch() into a loop until you have grabbed all the records.

    PHP Code:
    // Fetch the result of the query
        
    while($n_stmt->fetch()) {
            
    // Do something with each record
        

    Spookster
    CodingForums Supreme Overlord
    All Hail Spookster

  • #3
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,057
    Thanks
    25
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Spookster View Post
    Well just like the error says. You need to fetch all the records first. You would need to put your fetch() into a loop until you have grabbed all the records.

    PHP Code:
    // Fetch the result of the query
        
    while($n_stmt->fetch()) {
            
    // Do something with each record
        

    Notice this line in my code above...

    PHP Code:
    // Fetch record.
    mysqli_stmt_fetch($n_stmt); 

    Debbie

  • #4
    Supreme Overlord Spookster's Avatar
    Join Date
    May 2002
    Location
    Marion, IA USA
    Posts
    6,278
    Thanks
    4
    Thanked 83 Times in 82 Posts
    Quote Originally Posted by doubledee View Post
    Notice this line in my code above...

    PHP Code:
    // Fetch record.
    mysqli_stmt_fetch($n_stmt); 

    Debbie
    Yes that is why I pointed it out. You are fetching 1 record not all of them. The loop keeps looping and calling fetch until all records are retrieved.
    Spookster
    CodingForums Supreme Overlord
    All Hail Spookster

  • #5
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,057
    Thanks
    25
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Spookster View Post
    Yes that is why I pointed it out. You are fetching 1 record not all of them. The loop keeps looping and calling fetch until all records are retrieved.
    The problem is that I left this out and not Fetch...

    PHP Code:
        // Store result-set.
        
    mysqli_stmt_store_result($n_stmt); 

    Debbie


  •  

    Posting Permissions

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