Query containing array

Question

I want to get the query result that contains all entries from array and some data from the DB.
It's like the array is a DB table and by left join I connect the data that I need.

I've solved this task in a clumsy way and am asking for tips on how to rewrite this solution.
Here is my query:

 $subquery = 'Select \'' . 
 implode('\' as id union Select \'', $var_array) . '\'';
 $query = "Select tmp.variable, 
 fields.content is NULL as content FROM ({$subquery}) tmp
 LEFT JOIN fields
 ON tmp.id= fields.id";

• Mast: How big is your array?
• sambit: about 10 elements

Answer 1

Option 1 - Use a temporary table

CREATE TEMPORARY TABLE foo (`id` INT, `variable` VARCHAR(255), PRIMARY KEY (`id`));

Insert the data into the primary table and do a normal JOIN.

Note that temporary tables are created in memory and are dropped when the connection is closed (when the php script is done).

Option 2 - Query and add to result

Assuming that $vars are key/value pairs of id/variable

$ids = join(',', array_keys($vars));
$result = $db->query("SELECT * FROM `fields` WHERE `id` IN ($ids)");
$data = [];
while ($row = $result->fetch_assoc()) {
 $variable = $vars[$row['id']];
 $data[] = $row + ['variable' => $variable];
}