Help of order tracking database

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
archerford
Forum Newbie
Posts: 4
Joined: Fri Aug 05, 2005 4:25 pm

Help of order tracking database

Post 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
The Monkey
Forum Contributor
Posts: 168
Joined: Tue Mar 09, 2004 9:05 am
Location: Arkansas, USA

Post 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 :)
archerford
Forum Newbie
Posts: 4
Joined: Fri Aug 05, 2005 4:25 pm

Post 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
archerford
Forum Newbie
Posts: 4
Joined: Fri Aug 05, 2005 4:25 pm

Post 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
The Monkey
Forum Contributor
Posts: 168
Joined: Tue Mar 09, 2004 9:05 am
Location: Arkansas, USA

Post 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.
The Monkey
Forum Contributor
Posts: 168
Joined: Tue Mar 09, 2004 9:05 am
Location: Arkansas, USA

Post 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. :)
archerford
Forum Newbie
Posts: 4
Joined: Fri Aug 05, 2005 4:25 pm

Post by archerford »

Thanks alot, I'll try what you suggest.

Archerford
Post Reply