database parsing: multiple dates, multiple rows

[ShenaCat]

Hi all

I'm looking to bounce my prob off someone else's head so I can figure it out quicker.. I know there's something I'm missing because I'm too close to it.

Here goes: my office has an internal inventory management database I designed in PHP & mysql. I'm working on a "shopping cart" type system right now so our people can order inventory online for their specific locations.

I've designed it so that all products ordered go into a cart which then has to be approved by a manager before submitted to order personnel. All these orders in the cart go into the same table in a db... once it is submitted by the manager it gets moved from the cart table to the orders db..

We have 2 week ordering periods where everything ordered within that period isn't submitted until the end of week 2... so I've datestamped all orders made within the specific time period so it differentiates carts. I also have all entries in the carts and orders db have a key telling me which location the entry goes to. So there are two keys in essence: datestamp and location.

My problem is on my shopping cart when a user clicks on their cart there may be items in the cart from multiple datestamps... I want to sort it so if there are items from multiple datestamps, it presents the user with a dialog asking them to choose the datestamp they want to view the cart from.

This is becoming problematic. I query the db for all entries in the carts table that matches the specific location of the user that is logged in, but then sorting them by datestamp is difficult. I can't query for both location and datestamp at first because I don't know the datestamp until I do the initial query, and then I know datestamps of each individual product but they are all isolated inside of a foreach() statement so I can't figure out how to compare them to each other without doing odd things with arrays, anyway it's a mess.

Anyone up to helping me with this...?

[Jay]

Why can't you order them by the date stamp? MySQL also has quite a few functions for manipulating the datestamp (and thus performing searches on it, etc).

[ShenaCat]

Well the datestamp isn't saved anywhere except in those rows I need to pull. I have this little snippet of code that generates them on the fly when a product is ordered. So it's not like I know the datestamp ahead of time in order to pull products from the db with it. I have to pull the products from the db, find the datestamp, and then sort them by datestamp.

[Jay]

Why can't you store the datestamp in the table?

Either your DB structure is seriously screwed, or I'm just not understanding it

[ShenaCat]

The datestamp *is* stored... but I do not *know* the datestamp until I do the initial query (to find cart entries by user). Once I have found cart entries by user, I have a set of mysql rows in a $result variable. then I have to go through each of them in a for loop in order to find out what each of the rows has in it. Each row has a datestamp field in it... that's where I find my datestamps..

So I can't figure out how to order these since the datestamps could be anything and may not match at all... the datestamps aren't all necessarily the same. But I want to order the cart so that if there are multiple datestamps then there will be a 2nd step where the user chooses which date's cart to look at.

Is this any clearer yet..?

[Jay]

No, why do you NEED to know the datestamp?? Why can't you just pull out all the records for that particular user, and manipulate the datestamp afterwards? Or just order the table by date, whatever. The idea is that you use variables, you're not supposed to know in advance!

[DutchBen]

From the post in this thread i understand that you want to know if there is more than one timestamp in an order and base a decision on wheter or not there is.

In this case you could do 2 things
- Run a query to find out
if the query

SELECT Datestamp FROM `carts` WHERE location = $location GROUP BY Datestamp

gives you more than one result there will be more than one timestamp.
The group by option makes little groups of rows with identical Datestamps, if there is more than one timestamp there will be more than one group.

- Look at the result
if you run a query like so

SELECT * FROM 'carts' WHERE location = $location ORDER BY Datestamp

You know that whatever result you get it will always be ordered by value in the field Datestamp. So a result might look like this:

'Paper' 'Reggy' 'PostRoom' '11'
'Pen' 'Reggy' 'Postroom' '11'
'Pencil' 'Reggy' 'Postroom' '11'
'Petrol' 'Reggy' 'Postroom' '8'

You can traverse this result like this

$datestampcheck = mysql_result($result,3);

while($order = mysql_fetch_object($query)){

echo order->Item;

if($datestampcheck <> $order->Datestamp){
// He he .. theres a different datestamp
echo "datestamp number 2 = ".$order->Datestamp;
$datestampcheck = $order->Datestamp;
// make sure the next check works
}

}

Ok... i hope i understood what you we're trying to solve here.

[Jay]

Nope, still clueless, sorry.

Do you want to display everything so you can tell which department has ordered what, and then group that by the date? You can group by multiple fields, so you would use GROUP BY location,datestamp which would group everything by the location, and subgroup each location by the date. Is that what you're looking for?

[ShenaCat]

GROUP BY sounds promising. I'll look into it and let you all know. Thanks!

Sorry I have such a confusing problem! ;)