Page 1 of 1

Help of order tracking database

Posted: Fri Aug 05, 2005 4:36 pm
by archerford
I have a rather newbie questions and I was hoping someone would be kind enough to help me out. I've gotten into programming PHP and MYSQL recently, Ive written my own shopping cart and was wanting to implement a order tracking feature. I not sure how to handle this though. Since each order will have a different number of items in the cart, how to plan for this. I could setup the database so that I had a limited number of (item and Qty) fields, but someone may order more items than the database is setup to store. The only other thing I could think of was to put all the items in a delimited text field. If there is a better way, I wish someone could explain it to me.

Thanks
Archerford

Posted: Fri Aug 05, 2005 4:43 pm
by The Monkey
Instead of a shopping cart table with a limited number of item and quantity fields, you need a shopping cart table with 3 fields: userID, itemID, and quantity.

Every time the user adds an item to their shopping cart, use this query:

'INSERT INTO `cart` VALUES (' . $userID . ', ' . $itemID . ', ' . $quantity . ')'

You can update the quantities using something like

'UPDATE `cart` SET `quantity` = ' . $new_quantity . ' WHERE `userID` = ' . $userID . ' AND `itemID` = ' . $itemID

Hope that helps :)

Posted: Fri Aug 05, 2005 4:51 pm
by archerford
Thanks for the rapid response, I appreciate your help. I believe I see what you are doing, but wouldn't I need a field for there Invoice# , so they could pull there invoice back up at a later date ?

Thanks
Archerford

Posted: Fri Aug 05, 2005 5:06 pm
by archerford
I have another rather stupid question, but what would you use as a primary key ? Wouldn't you have mutiple enteries for each of the 3 fields you described ( UserID, ItemID, Qty ). None of these fields will be unquie.

Archeford

Posted: Fri Aug 05, 2005 5:08 pm
by The Monkey
Yes, you would. Instead of userID, then, I would have invoiceID, and then a table of invoices which contained the fields: userID, invoiceID.

$invoices = mysql_query('SELECT * FROM invoices WHERE userID = ' . $_SESSION['userID']);

while ( $invoice = mysql_fetch_array($invoices) )
{
$items = mysql_query('SELECT * FROM cart WHERE invoiceID = ' . $invoice['invoiceID']);
while ( $item = mysql_fetch_array($items) )
{
echo 'Item: ' . $item['itemID'] . '; quantity: ' . $item['quantity'];
}
}

You would need to look into some MySQL Joins for different things, like querying the item name along with the itemID when selecting the items in the cart for an invoice. But this should get you started.

Posted: Fri Aug 05, 2005 5:09 pm
by The Monkey
archerford wrote:I have another rather stupid question, but what would you use as a primary key ? Wouldn't you have mutiple enteries for each of the 3 fields you described ( UserID, ItemID, Qty ). None of these fields will be unquie.

Archeford
Yes, userID was a bad example. See my previous post here, and make invoiceID primary. :)

Posted: Fri Aug 05, 2005 5:14 pm
by archerford
Thanks alot, I'll try what you suggest.

Archerford