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?
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);
We can take advantage of MySQL's extended WHERE IN syntax, and use a query of the form:
SELECT *
FROM supplier_item
WHERE (item_id, supplier_id) IN ((9, 9), (10, 2), ...);
PHP code:
$item_id = array(9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24);
$supplier_id = array(9,2,5,1,1,9,6,4,6,9,1,9,9,4,5,9);
$where = "(";
for ($i=0; $i < count($item_id); $i++) {
if ($i > 0) $where .= ", ";
$where .= "(" . $item_id[$i] . ", " . $supplier_id[$i] . ")";
}
$where .= ")";
$sql = "SELECT * FROM supplier_item WHERE (item_id, supplier_id) IN " . $where . ';';
Important note: Raw string concatenations can leave the door open for SQL injection. This answer, if used in production, should only be used if you have already sterilized the item and supplier ID arrays, and you are certain that they only contain integers.