如何在选择基于两个数组数据的查询时避免循环

Question

I have a table like below.

id  supplier_id item_id minimum_order_qty   
1      6           1         500 
2      4           1         300 
3      2           2         200 
4      3           2         100 
5      4           3         250 
6      5           5         100 
7      7           6         1000    
8      8           6         600 
9      9           7         700 
10     1           7         500 
11     7           8         1000    
12     9           9         700 
13     2           10        500 
14     9           10        600 

Each item_id can have more than one supplier(supplier_id).

I have two array item_id array and supplier_id array.

item_id array:

Array
    (
        [0] => 9
        [1] => 10
        [2] => 11
        [3] => 12
        [4] => 13
        [5] => 14
        [6] => 15
        [7] => 16
        [8] => 17
        [9] => 18
        [10] => 19
        [11] => 20
        [12] => 21
        [13] => 22
        [14] => 23
        [15] => 24
    )

supplier_id array:

Array
        (
            [0] => 9
            [1] => 2
            [2] => 5
            [3] => 1
            [4] => 1
            [5] => 9
            [6] => 6
            [7] => 4
            [8] => 6
            [9] => 9
            [10] => 1
            [11] => 9
            [12] => 9
            [13] => 4
            [14] => 5
            [15] => 9
        )

Both array is same length. I want to select minimum_order_qty from supplier_item based on this two array. Normally, I have to select inside loop like this:

$item_count = count($item);
$sup_count = count($supp_id);
for($i=0; $i<$item_count; $i++) {
    $itm_id = $item[$i];
    $s_id = $supp_id[$i];
    $sql = "select * from supplier_item where item_id=$itm_id and supplier_id=$s_id";
    $result[] = $this->query($sql);
}

But, the above code will run query many times. I don't want to do this. So, Is there any other way to select data with single query?

Answer 1

You can do it in one query

$item_count = count($item);
$sup_count = count($supp_id);
$predicates = [];
for($i=0; $i<$item_count; $i++) {
    $itm_id = $item[$i];
    $s_id = $supp_id[$i];
    $predicates[] = "(item_id=$itm_id and supplier_id=$s_id)";
}
$sql = "select * from supplier_item where " . implode(' OR ', $predicates);
$result = $this->query($sql);