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
Help of order tracking database
Moderator: General Moderators
-
archerford
- Forum Newbie
- Posts: 4
- Joined: Fri Aug 05, 2005 4:25 pm
-
The Monkey
- Forum Contributor
- Posts: 168
- Joined: Tue Mar 09, 2004 9:05 am
- Location: Arkansas, USA
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
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
-
archerford
- Forum Newbie
- Posts: 4
- Joined: Fri Aug 05, 2005 4:25 pm
-
The Monkey
- Forum Contributor
- Posts: 168
- Joined: Tue Mar 09, 2004 9:05 am
- Location: Arkansas, USA
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.
$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
Yes, userID was a bad example. See my previous post here, and make invoiceID primary.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
-
archerford
- Forum Newbie
- Posts: 4
- Joined: Fri Aug 05, 2005 4:25 pm