0

MySQL 5.5.36 Apache 2.2.15 CentOS 6.6 PHP 5.4.31

Sorry for the terrible title, but here is what I am looking for help with; I have 2 tables, each order has a single row and the order_items has multiple rows. 

order
id | date | customer | status

order_items
id | order_id | item_id | item_name

My current code is cycling through the open orders then doing a secondary db call to grab the items and place them into a single row of an html table like this:

order_id | date       | customer | status | item_name
1        | 2014-11-20 | 100233   | open   | widget a (item id 0004)
                                          | widget a (item id 0004)
2        | 2014-11-21 | 103327   | open   | widget c (item id 0005)
                                          | widget d (item id 0006)

The desired end result is exactly as shown in the above table, but I would like to get the entire thing within a singular query if possible, instead of having to do multiple queries per order displayed.

Now, after googling for a while, I haven't been able to find anything that really is like what I am trying to do. I looked at GROUP_CONCAT, but it only concatenates columns within a single table it looks like. I want to concatenate columns from another table while also grabbing all the rows found.

My attempted SQL is something like this:

select 
  o.id, o.date, o.customer, o.status, oi.id, 
  (select concat(item_name, ' (item id ', item_id, ')') from order_items where order_id = o.id) as 'item_list'
 from order o inner join order_items i on o.id = i.order_id 
 where o.status = 'open'
 order by date asc

That nested select statement is what I can't figure out. So, is this possible?

When I run my version of the query I get:

Error Code: 1242
 Subquery returns more than 1 row
tatorface
  • 72
  • 1
  • 8

2 Answers2

3

You can phrase what you want with a group_concat(), I think:

select o.id, o.date, o.customer, o.status,
       group_concat(i.item_name, ' (item id ', i.item_id, ')' separator '
') as item_list
from order o inner join
     order_items i
     on o.id = i.order_id 
where o.status = 'open'
group by o.id, o.date, o.customer, o.status
order by date asc;

I don't fully understand your data layout, in terms of having the items on separate lines. This version starts a new line, but more typically a comma or semicolon would be used.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks for actually answering the SQL part of the question. Though I don't think fixing the layout of the order item in the SQL is a good thing in a PHP web application. – mabi Dec 01 '14 at 23:04
  • The group_concat, as mentioned, only concats the values of the columns for a single row as the new column. There are multiple rows I am wanting to add to the 'item_list' column so i have just the order information plus a listing of all the items that are on the order in one column. – tatorface Dec 02 '14 at 00:22
  • 1
    This should put all the items in the order in a single delimited list in the column. – Gordon Linoff Dec 02 '14 at 02:30
  • Wow, after I was able to get my syntax right, this is exactly what I was looking for. It gives exactly what I am looking for. thanks! – tatorface Dec 02 '14 at 14:58
0

You're overthinking this. Since you already JOIN in the SELECT, you get as many rows as there are rows in order_items.

In your application code, do a one pass over the result set (using mysqli_fetch_row() or similar), collecting order items as keys into a array of order_items (creating or adding to the array as needed). That is, reconstruct what your relations in your table looked like.

Then do a second pass over the map you just created and output as desired. If you happen to do this often, take a look at using a ORM mapper.

Community
  • 1
  • 1
mabi
  • 5,279
  • 2
  • 43
  • 78
  • I thought of this. I wanted to try to reuse as much code as possible and not have to recreate the thing from scratch. Unfortunately, it might be what I have to do instead of just replacing a query and removing some unneeded functions. Thanks! – tatorface Dec 02 '14 at 00:23
  • What I've described is actually the most simplest (and thus most desirable) thing possible, IMHO. But the gist is - hang on to your result set, there's no need for your second query/queries. If you worry about code re-use, you might be ready for introducing Doctrine2 or similar ORM mapper. – mabi Dec 02 '14 at 12:51