Say I have the following 2 tables:
TABLE 1: users user_id INT first_name VARCHAR
TABLE 2: items user_id INT item_title VARCHAR
What is the best method to SELECT the data so I end up with data looking like:
$user[0]['user_id']=1;
$user[0]['first_name']='Dave';
$user[0]['items'][0]['item_title']='car';
$user[0]['items'][1]['item_title']='house';
$user[0]['items'][2]['item_title']='horse';
$user[1]['user_id']=2;
$user[1]['first_name']='Steve';
$user[1]['items'][0]['item_title']='bike';
$user[1]['items'][1]['item_title']='kite';
I know how to get the data with multiple queries in a loop, but that is bad practice. I also know that I can get all the rows with a JOIN and then loop through the data to model it like above.. But what is the best approach?
As a side note question... Is this what you would use an ORM for?
Not sure if you are looking for a PHP based solution but you can use GROUP_CONCAT() along with JOIN like below .
select u.user_id,u.first_name, xx.itemlist
from users u join (
select user_id,
group_concat(item_title) as itemlist
from items
group by user_id) xx on u.user_id = xx.user_id;