Stacking Help needed ASAP!

[Bane_]

Gah, I have to open my website within the next few hours and I'm stumped! This is a vital part of my game so I need it fixed asap if anyone can help!

 

I'm basically trying to create an item inventory where the items are grouped by their name and then the first item that you click should be the item which will expire first. I'm fine with grouping it etc but it's getting it to display the item which runs out first that I'm having trouble with and no doubt it's an easy solution that I'm just missing.

 

I need to group my items table so that items with the same item_key are stacked, the problem I'm running into is that once they're stacked I need the first (clickable) item to be the one with the expiration date which is going to expire first and I am literally banging my head into a wall.

I've tried using MIN() to no avail and other then that I'm stumped!

 

$query = "SELECT item_key, MIN(expires), item_id, item_img, name, COUNT(item_name) FROM items WHERE owner_id = '$id' AND status = 'Av' AND type = 'Food' GROUP BY item_key ORDER BY item_name, expires";

 

Can anyone help?

[biggieuk]

Hi,

 

What format is your expiry date stored as in the database?

 

If it's a date field you could just ORDER BY item_name, expires ASC

 

this will first order by item_name then by expiry date starting with the most recent to expire.

 

SELECT item_key, expires, item_id, item_img, name, COUNT(item_name) 
FROM items 
WHERE owner_id = '$id' AND status = 'Av' AND type = 'Food'
AND expires >= DATE() 
GROUP BY name 
ORDER BY item_name, expires ASC

 

This will only return items that have yet to expire and will be ordered by the first to expire.

 

Depending on how your expiry field is structured:

 

DATE() returns the date without time

NOW() returns the current date & time

[Bane_]

It's stored as a DATETIME - 1970-01-01 12:59:00 format.

 

I don't need the items which are expired to not be shown, I need everything to show just the one's that will go first need to be the one on top of the stack? So that one will be the first one selected, I do think however I may have found a work around as the item_id (which is what it is currently being stacked by it seems) is selecting the oldest ID, which means that if the item_id in a group of items comes before another item_id of an item in the same group, the older item_id would have the older expiry date too... else I'm just confusing myself and trying to make things more difficult then then need to be. Aha.

 

 

[fenway]

Sorry, I don't follow.