1
\$\begingroup\$

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";
\$\endgroup\$
2
  • \$\begingroup\$ How big is your array? \$\endgroup\$
    – Mast
    Jun 17, 2015 at 11:51
  • \$\begingroup\$ about 10 elements \$\endgroup\$
    – sambit
    Jun 17, 2015 at 12:21

1 Answer 1

2
\$\begingroup\$

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];
}
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.