php mysqli oo multi_query over an array

Question

I have a $color_combo value which explodes into an array of $colors for a given item (3 values in the array). The $colors values are 3-character string codes i.e. FGR and there is a lookup table in the db that matches that code to the color 'forest green' ... but with 3 colors per item I need to run the query 3 times over the array:

 $color_combo = 'FGR-BLU-YLW'
 $colors = explode('-', $color_combo);
 $q = $mysqli -> prepare("SELECT color_name from color_table as c WHERE color_code=?");
 $q->bind_param("s", $colors[]);

So I'm trying:

 while (mysqli_multi_query($link, $q)) {
 $result = mysqli_store_result($link))
 return $result;
 }

I'm haven't been able to find documentation on this case scenario, it might be using other functions ...:

 $value = $result->fetch_array(); ... or maybe next_result() or more_result()

My question at its core, is more about best practices with the mysqli extension and how to run queries on arrays of values

Answer 1

You should change your code with the query into the following:

$color_combo = 'FGR-BLU-YLW'
$colors = explode('-', $color_combo);
$q = $mysqli ->prepare("
    SELECT 
        color_name 
    FROM
        color_table as c 
    WHERE 
        color_code IN (?, ?, ?)
");

$q->bind_param("sss", $colors[0], $colors[1], $colors[2]);

After executing the statement, the result will now contain 3 rows from the database containing the color names. You can loop over this result to retrieve the color names. The code assumes that there will always be 3 color codes in the $colors array.