New MYSQLI query during fetch

Question

I'm just making a website for entertainment at the moment, but I'm stuck at a certain point.

I want to complete a group quest, and any member of the group can start it. All people in the group will receive the same amount of gold, experience and share the same recovery time. I have 3 tables in my database (I will show only important information)

Members: username, level, experience, playergold Levels: level, required_experience Groups: leader, member_1, member_2, member_3, last_quest_started, quest_cooldown Quests: success_message, failed_message, chance, minimum_experience, maximum_experience, minimum_gold, maximum_gold, cooldown 

I want to update last_quest_started and quest_cooldown in groups, and I want to update each participant with my level, experience, playergold

So, after receiving each group username, quest data, experience calculation and gold. I use this to update:

 if($select_members_info_stmt = $mysqli->prepare("SELECT members.username, members.level, members.experience, members.playergold, levels.required_experience FROM members INNER JOIN levels ON members.level = levels.level WHERE ((members.username = ?) OR (members.username = ?) OR (members.username = ?) OR(members.username = ?))")) { $select_members_info_stmt->bind_param('ssss', $leader, $member_1, $member_2, $member_3); $select_members_info_stmt->execute(); $select_members_info_stmt->bind_result($selected_username, $level, $experience, $playergold, $required_experience); while($select_members_info_stmt->fetch()) { $now = time(); if($update_user_stats_stmt = $mysqli->prepare("UPDATE members SET level = ?, experience = ?, playergold = ? WHERE username = ?")) { $update_user_stats_stmt->bind_param('iiiiis', $new_level, $new_experience, $new_gold, $now, $cooldown, $selected_username); $update_user_stats_stmt->execute(); if($update_user_stats_stmt->affected_rows == 0) { echo '<div>Because of a system error it is impossible to perform a task, we apologize for this inconvience. Try again later.</div>'; } $update_user_stats_stmt->close(); } else { printf("Update user stats error: %s<br />", $mysqli->error); } } $select_members_info_stmt->close(); echo '<div>'.$success_message.'</div><br />'; } else { printf("Select members info error: %s<br />", $mysqli_error); } 

But I keep getting:

Error updating user statistics: commands are not synchronized; you cannot run this command now (4 times, the size of my groups when they are full.)

I just can’t find a solution to work with a synchronization error, because I can’t close $ select_members_info_stmt, because then it will stop being extracted.

Please help me because I really don’t know what to do.

Answer 1

You cannot nest your execute() this way.

The best solution is to drop this list of members into array() once, close your connection, and THEN repeat this array and update each entry.

It should look like this:

 $select_members_info_stmt->bind_param('ssss', $leader, $member_1, $member_2, $member_3); $select_members_info_stmt->execute(); $select_members_info_stmt->bind_result($selected_username, $level, $experience, $playergold, $required_experience); $members = array(); while($select_members_info_stmt->fetch()) { // tossing into the array $members[] = array( 'selected_username' =>$selected_username, 'level' => $level, 'experience' => $experience, 'playergold' => $playergold, 'required_experience' => $required_experience ); } $select_members_info_stmt->close(); // Now iterate through the array and update the user stats foreach ($members as $m) { if($update_user_stats_stmt = $mysqli->prepare("UPDATE members SET level = ?, experience = ?, playergold = ? WHERE username = ?")) { // Note that you need to use $m['selected_username'] here. $update_user_stats_stmt->bind_param('iiiiis', $new_level, $new_experience, $new_gold, $now, $cooldown, $m['selected_username']); $update_user_stats_stmt->execute(); if($update_user_stats_stmt->affected_rows == 0) { echo '<div>Because of a system error it is impossible to perform a task, we apologize for this inconvience. Try again later.</div>'; } $update_user_stats_stmt->close(); } else { printf("Update user stats error: %s<br />", $mysqli->error); } } 

Answer 2

You cannot actively insert prepared statements into the same connection to mysql. Once you call execute() on any of the statements, you cannot run another on the same connection until this prepared statement is closed. Any selections in the first prepared statement will fail after starting the second.

Only one live statement can be prepared and run on the mysql server for each connection.

If you really need to enclose your prepared statements, you can establish 2 separate mysqli connections.