Unique Order Num in multiple DB columns

[dongbamage]

Hi guys,

heres my problem:

I have a shop system that must (for needs of payment) insert a seperate row in my orders table for each product and the quantity of that particular product ordered. however a cart can contain different items with differing quantities.

So I have an order table that records :

OrderID an autonumber
CustomerID
StockID
AmountOrdered
TotalCost
VAT
OrderDate
Dispatched
PaymentType
IP

Now what I want to do is to add another column (OrdNum) which is unique for the cart order but will be the same for each of the items in the cart.

What i'd like to know is how to insert a OrdNum that is not already in the database so (unique in that sense) but that will be the same for each of the cart items...

If I haven't made that clear please let me know and I'll have another go

Can anyone help me with this please?

Thanks again guys, I've learnt so much from visiting here its a gereat site!

 

[estrellas]

kinda foggy here on your question .. but your database should autoincroement the orderNum if you set the sql part up right. you'll just want to make sure than when you update the tables ...

well here ..

when you install database.sql, you usually do

drop customer;
drop order;
drop other table;
...

this deletes what you have in the tables, so unless you want to repopulate the enitre database, don't do the "drop" ... do something like this

alter customer;

create table customer(
orderID integer autoincrement,
customerID integer primary key,
stockID integer,
.....
ordnum integer autoincrement);

else i'm pretty sure that's what you're wanting to ask ... if not .. then you're gonna want to do some stuff with refcursors ... in which case i'll get back to you , but keep me notified if ^^^ is what you're looking for or something else and i'll try to help

-------

http://www.letour.fr

 

[Itshim]

When the cart is inserting the first row of a new order, you could query the db for distinct OrdNum and order by descending with a limit of one. The query would look something like this:

SELECT DISTICNT OrdNum
FROM Cart_Table
ORDER BY DESC
LIMIT 1

The number returned from this query would be the highest OrdNum, and then in PHP increment that number by one and you will have a unique number for the current order.

Hope this helps,
Itshim

 

[ericbrunson]

Basically these problems go away if you don't use autonumber. If you create a sequence and go get the next id when you need it, you have it and don't need to worry about getting it from the row you've already inserted.

Autonumber is useful *sometimes* but usually doesn't save any programming when you need to get the id of a parent record for the child records. I just don't bother using it ever.

 

[dongbamage]

Thanks for all of your replies ppl!

I think Itshim has hit the proverbial nail on the head, thanks for the super speedy response guys!

you guys really need to get jobs or something

Thanks again!!!

 

[dongbamage]

Yep heres what I ended up using:

<?
include ('db_connect.php');
$sql="SELECT DISTINCT OrdNum FROM orders ORDER BY OrdNum DESC LIMIT 1";
echo $sql;
$result = mysql_query($sql);
if ( $result === FALSE )
{
print "An Error occured while attempting to save your form";
if ( $MYSQL_ERROR_DEBUG ) print "<P>MySQL Error: " . mysql_error();
}
else
{
while($row=mysql_fetch_array($result))
{
$NewOrdNum=($row["OrdNum"]+1);
echo $NewOrdNum;
}
}
?>

Nice

Cheers guys!!

 

[ericbrunson]

You reallize that you're in a race condition with any other users when you do this: "SELECT DISTINCT OrdNum FROM orders ORDER BY OrdNum DESC LIMIT 1"

If two users place an order at the same time they will generate the same ordnum.

If you're using an autonumber field you should use LAST_INSERT_ID() after you insert the record, which is specific to your connection and guaranteed to work.

 

[dongbamage]

hi eric,

can u explain this in a little more depth pls?

If you're using an autonumber field you should use LAST_INSERT_ID() after you insert the record, which is specific to your connection and guaranteed to work.

I'm not using an autonumber for OrdNum...

hmmmmm maybe I could use the CustomerID to solve this 'race' problem...

ie SELECT DISTINCT OrdNum FROM orders ORDER BY OrdNum DESC LIMIT 1 where customerID='$logname'"

do u think that would help?

cheers!!

 

[Itshim]

First, I apologize for giving you some bum advice earlier I had not thought of what Eric described.

I believe what he means by a 'race condition' is when two people are on the site at the same time and they add something to their cart (at the same time), with the code I suggested earlier they would both come up with the same 'OrdNum', and which ever one gets inserted into the db first would have the correct 'OrdNum' and the other person would not have a unique 'OrdNum' hence it is a race to get the insert into the db.

I believe what you suggested in your last post would be a way around this race condition (but then again I may not be the one to listen to). You just need the where clause in front of the order by clause.

SELECT DISTINCT OrdNum 
FROM orders 
WHERE customerID='$logname'
ORDER BY OrdNum 
DESC LIMIT 1

Sorry again for the bad suggestion.

Itshim

 

[dongbamage]

Hey man between you, me and Eric we have figured it out!

I couldn't ask for more, thans for all your help guys!