Say I have the following 2 tables:
说我有以下2个表：
TABLE 1: users user_id INT first_name VARCHAR
表1：用户user_id INT first_name VARCHAR
TABLE 2: items user_id INT item_title VARCHAR
表2：项目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?
我知道如何在循环中获取具有多个查询的数据，但这是不好的做法。我也知道我可以通过JOIN获取所有行，然后循环遍历数据以对其进行建模，如上所述..但最好的方法是什么？
As a side note question... Is this what you would use an ORM for?
作为旁注问题......这是您使用ORM的原因吗？
1 个解决方案
#1
0
Not sure if you are looking for a PHP based solution but you can use GROUP_CONCAT() along with JOIN like below .
不确定您是否在寻找基于PHP的解决方案，但您可以像下面一样使用GROUP_CONCAT（）和JOIN。
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;
#1
0
Not sure if you are looking for a PHP based solution but you can use GROUP_CONCAT() along with JOIN like below .
不确定您是否在寻找基于PHP的解决方案，但您可以像下面一样使用GROUP_CONCAT（）和JOIN。
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;