I have the following function which works properly and returns all of the rows from a specific table, then loops through each row to add data related to it as a subarray.
public function fetch_products() {
$return = $this->db->all("SELECT * FROM consumables_products");
foreach($return as &$r) {
$r['consumables'] = $this->db->all("SELECT A.*, B.description FROM consumables_product_consumables as A, consumables_consumables as B WHERE product_id=? AND B.uid=A.consum_id", [$r['uid']]);
}
return $return;
}
The result looks like this:
Array
(
[0] => Array
(
[uid] => 1
[description] => Test
[consumables] => Array
(
[0] => Array
(
[uid] => 1
[consum_id] => 186
[product_id] => 1
[amount] => 5
[description] => 3 X 1" GREEN Direct Thermal
)
[1] => Array
(
[uid] => 2
[consum_id] => 185
[product_id] => 1
[amount] => 1
[description] => 3 X 1" ORANGE Direct Thermal
)
)
)
)
This could get very inefficient with hundreds or thousands of rows in my consumables_products
table, and an unknown number of rows in the consumables_product_consumables
table.
I'm wondering if there is a way that I can do this in a single query, so that I don't have to do a separate call for every row in my consumables_products
table.