Complicated multiple table query advice

jdiver

I'm working on a site that is a menu-based food ordering site. I have the following tables (relevant fields listed):

calendar (stores all events and event info)
- event_id
- menu_id

menus (stores all available menus)
- menu_id

menu_items (stores all available menu items)
- item_id

menu_lists (stores which menu items are associated with which each menu)
- menu_id
- item_id

orders (stores information about each order placed)
- event_id

order_items (stores all items placed for each order)
- order_id
- item_id

What I need to show is a table that contains each day, and the total of each item ordered for that day (32 of item 108, 17 of item 112, etc.). I then need to show each event that day, and an item by item breakdown for that event (2 of item 108, 0 of item 112, etc.).

Currently what I'm doing is running a query that pulls all the days and events for the given time period (by default this week). I then store all the event info in an array, then loop through that array and for each day and each event for that day, I'm running a query to pull the needed menu item info. For the most part this way works, but it seems terribly slow and inefficient.

As if that isn't enough, I also need to show the number of menu items that are for new orders (customers that haven't ordered before), and return orders (customer who have > 1 order).

The attached image illustrates exactly what I need the end result to be.

Any ideas would be most appreciated. TIA.

nemonoman

You should be able to do most of this stuff pretty simply, but 2 needed hunks of info are missing your message:

Where is 'day' information stored? Calendar table?

Need better sense of customer table and relation to other tables.

By the way, thanks for keeping this message so very clear and your table descriptions so simple. Makes answering and considering the problem very much more pleasant.

jdiver

nemonoman- Thanks for the reply.

Days are obtained by querying the events table with the date range. So if I am getting the week to date events, the query (simplified) would be:

	$sql = "SELECT c.* 
			FROM calendar AS c 
			WHERE  c.event_end >= '$start_range' 
			AND c.event_start <= '$end_range' 
			ORDER BY c.event_start ASC";

The variables $start_range and $end_range are UNIX timestamps based on the desired date range. So for week to date, the $start_range is the timestamp equivalent for Sunday, June 20 at 12:00 am, and the $end_range is the current time().

I calculate the difference between the two timestamps, and divide by 86400 to figure out how many days. The code loops through each event and assigns it to the correct day.

That part works fine for me and seems fairly quick and efficient.

The customer table and its relation to the orders is:

users
- user_id

orders
- user_id

I currently don't have any field in the users table that tracks how many orders the user has placed, so the only way to obtain that needed info is to count the orders in the orders table that belong to each user_id.

Hopefully that clears some things up. Thanks again for your help and any insight/ideas you can offer.

jdiver

Since the image of the table I ultimately need didn't get attached in my first post, here it is.

nemonoman

I just don't understand the obsession php programmers have with unix timestamps!!

Before we do the rest, here's the approach I'll be taking:

select date_format( From_unixtime( calendar .event_end ) ,
'%y%m%d' )
FROM calendar
WHERE calendar .event_end >= '$start_range'
AND calendar .event_start <= '$end_range'
Group By 1

Use the above code to group each event by end day

You could also use start day to group them

Since events are the way orders, menus, items, etc, are tied together, what is the DAY you wish to group by? event_start, or event_end? Conceivably, events go on for days??

Anyway, once that is sorted out, it's easy to use SUM() and COUNT(), etc., to do group based queries by day.

nemonoman

SELECT user.user_id, count(order.order_id)
FROM user, order
WHERE order.user_id=user.user_id
GROUP BY user.user_id
HAVING count(order_id)=1

or HAVING count(order_id)>1

jdiver

Since events are the way orders, menus, items, etc, are tied together, what is the DAY you wish to group by? event_start, or event_end? Conceivably, events go on for days??

Any given event only occurs in one single day, so spanning days isn't an issue. What I was doing was looping through each event (which were ordered by event_start) and grouping them by day (by dividing by 86400). All the events were then put into an array that was something like: $events[$day_num] and all the event info (event_id, menu_id) contained in that. It was that array that I was looping through each day, and in turn each event to pull the needed info. However, that way seems overly convoluted now that I see your solution.

So once I have the events grouped by day using the query you suggested, can I pull the needed info with one query, or will more loops be needed.

I've tried so many different things that nothing makes sense now, so I'm starting from scratch using your events query as a beginning. But I wouldn't object if you wanted to save me some time. 😃

Thanks very much for your help.

nemonoman

table that contains each day, and the total of each item ordered for that day (32 of item 108, 17 of item 112, etc.).

SELECT DATE_FORMAT(FROM_UNIXTIME( calendar .event_end ) ,
'%Y-%m-%d' ) ,

menu_lists.item_id,
COUNT(menu_items.item_id)
FROM calendar, menus, menu_items, menu_lists
WHERE calendar.menu_id=menus.menu_id
AND menu_lists.menu_id=menus.menu_id
AND menu_lists.item_id=menu_items.item_id
AND DATE_FORMAT( FROM_UNIXTIME( calendar .event_end ) ,
'%Y-%m-%d' )='2004-12-23'
GROUP BY 1 , menu_lists.item_id

Something like that:
Although I still don't understand the relationship between events and orders and orders and menus. You've got 'items' in a number of tables.

You probably have to adjust this to traverse the orders table.