...

View Full Version : Multiple Prepared Statements Failing



doubledee
09-04-2011, 11:51 PM
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...



// 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... :o

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

Spookster
09-05-2011, 01:12 AM
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.



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

doubledee
09-05-2011, 02:09 AM
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.



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


Notice this line in my code above...



// Fetch record.
mysqli_stmt_fetch($n_stmt);



Debbie

Spookster
09-06-2011, 01:39 AM
Notice this line in my code above...



// 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.

doubledee
09-08-2011, 04:48 AM
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...



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



Debbie



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum