MYSQLI new query within a while fetch [closed]

Question

I'm just making some website for fun at the moment but I'm stuck at a point.

I want to perform a group quest and any member of the group can start it. All people of the group will get the same ammount of gold,experience and share the same cooldown. I have 3 tables in my database(I will only show 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 member his/her level, experience, playergold

So after getting each username of the group members, the quests data, calculating the experience 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:

Update user stats error: Commands out of sync; you can't run this command now (4 times, which is the size my groups are when they are full.)

I just can't find the solution to work around the out of sync error, because I can not close the $select_members_info_stmt because then it would stop fetching.

Please help me out, because I really have no clue what to do.

Answer 1

You can't nest your execute() like that.

The best solution is to toss that list of members into an array() once, close your connection, and THEN iterate that array and update each record.

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);
    }

}