current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.
up vote 1 down vote favorite

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";
share|improve this question
    
How big is your array? –  Mast Jun 17 at 11:51
    
about 10 elements –  sambit Jun 17 at 12:21

1 Answer 1

up vote 2 down vote accepted

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

Assume that $vars are key/value pares or 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];
}
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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