每次使用不同的参数运行相同的mysqli查询

Question

Basically I have an array "$badges" of which contains a list of badge ids a user has aquired (This has been made in a previous query), I need to run another query for every id to select the name, description etc of the badge.

$level = array("#FFFFFF", "#76EE00", "#00FFFF", "#00FFFF", "#FF77FF");
        for ($i = 0; $i < count($badge); $i++) {
        if($stmt = $mysqli->prepare("SELECT badges.name, badges.description, badges.value FROM badges INNER JOIN user_badges ON user_badges.id_badge = ? LIMIT 1")){
            $stmt->bind_param('s', $badge[$i]);
            $stmt->execute();
            echo $badge[$i] . "test";
            $stmt->store_result();
            $stmt->bind_result($name,$description,$value);
            //echo $name;
            $stmt->fetch();
            echo '<div class="badge"><span class="bold" style="color:' . $level[$value] . '">' . $name . '</span></div>';
            $stmt->close();
        }
    }

In this case the badge array contains values 1 and 2 as:

array(2) { [0]=> int(1) [1]=> int(2) }

And it's going through the for loop fine for each value. However, it seems the query is executed twice for the value 1 and not for the values 1 and 2. *If this makes any sense So I receive two outputs of the badge with id 1 rather than 1 and 2.

I know I can use foreach etc but that isn't where the problem is. I was wondering if anyone can educate me on why this is happening? I feel it's something to do with binding the results but I don't know why.

Answer 1

Beside of malformed SQL, your method of running prepared statements is quite wrong. You have to move preparation outside and then run only binding and execution in a loop.
That's the point of prepared statements actually

$stmt = $mysqli->prepare("SELECT badges.name, badges.description, badges.value FROM badges INNER JOIN user_badges ON user_badges.id_badge = badges.id WHERE (badges.id = ?) LIMIT 1");
for ($i = 0; $i < count($badge); $i++) {
    $stmt->bind_param('s', $badge[$i]);
    $stmt->execute();
    echo $badge[$i] . "test";
    $stmt->store_result();
    $stmt->bind_result($name,$description,$value);
    //echo $name;
    $stmt->fetch();
    echo '<div class="badge"><span class="bold" style="color:' . $level[$value] . '">' . $name . '</span></div>';
}

Also, it looks like that JOIN is useless here and you can query only badges table