Select multiple records by one query

Question

Hello there. Please, give me advice, how to construct select query. I have table table with fields type and obj_id. I want to select all records in concordance with next array:

$arr = array(
0 => array('type' => 1, 'obj_id' => 5),
1 => array('type' => 3, 'obj_id' => 15),
2 => array('type' => 4, 'obj_id' => 14),
3 => array('type' => 12, 'obj_id' => 17),
);

I want to select needed rows by one query, is it real? Smth like

select * from `table` where type in (1,3,4,12) and obj_id in (5,15,14,17)

But this query returns also records with type = 3 and obj_id = 14, and for example type = 1 and obj_id = 17. p.s. moderators, please fix my title, I dont know how to describe my question.

update: array $arr could contain more than 500 elems.

Answer 1

As far as I know you can't use in for this but you have to fall back to something like this

select * from `table` where type=1 and obj_id=5 or type=3 and obj_id=15

Answer 2

If I understand correctly, you can use either UNIONs or ORs. Using UNIONs:

select * from `table` where type = 1 and obj_id = 5
UNION ALL
select * from `table` where type = 3 and obj_id = 15
UNION ALL
select * from `table` where type = 4 and obj_id = 14
UNION ALL
select * from `table` where type = 12 and obj_id = 17

Remove the ALL from the UNION ALL if you need to remove duplicates.

Using ORs:

select * from `table` 
 where (type = 1 and obj_id = 5)
    OR (type = 3 and obj_id = 15)
    OR (type = 4 and obj_id = 14)
    OR (type = 12 and obj_id = 17)

The brackets are important - they indicate that all the things inside need to be satisfied.

I recommend using UNIONs - ORs are notorious for bad performance.

Answer 3

$sql = 'select * from `table` where ';

foreach ($arr as $index => $conditions){
    $sql .= $or.' (';
    foreach($condicions as $key => $value){
       $sql .= $concat. "$key = $value";
       $concat = ' and ';
    }
    $sql .= ')';
    $or = ' or ';
}

should form $sql as:

select * from table where 
  (type = 1 and obj_id = 5) or 
  (type = 3 and obj_id = 15) or 
  (type = 4 and obj_id = 14) or 
  (type = 12 and obj_id = 17); 

Answer 4

If the condition array becomes large, you may want to create an indexed temporary table, have a compound index on table (type, obj_id) and perform an INNER JOIN.