Posting each item ordered in its own table row

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
michlcamp
Forum Commoner
Posts: 78
Joined: Mon Jul 18, 2005 11:06 pm

Posting each item ordered in its own table row

Post by michlcamp »

Up until now I've had good success with entering individual orders from my shopping cart into its own MySql table row in the 'orders' table - this enters the order data ACROSS the row - using a query such as:

Code: Select all

$query = "INSERT INTO orders VALUES ('order_id','$item1','$item2','$item3','$subtotal','$shipping','$total')";
where $item# is actually the qty of product item ordered, then I can cross-reference against the products table and create a report and output the HTML

What I really need to do is post each product ordered to it's own table row...in other words, to create a row entry for each item ordered with fields like :

order_id | item_num | item_descr | item_qty | item_price | item_total

this would give me a table that reads something like:

101 | paint | 5 gallon pail | 2 | 25.00 | 50.00
101 | brush| 2 inch broad | 2 | 10.00 | 20.00
101 | paint | 1 gallon pail | 3 | 12.50 | 37.50

(obviously I would have to include additional fields to those above)

can this be done?
I'm wondering how to write the INSERT INTO query so that it posts each item ordered across the row, then moves to the next row to enter the next item ordered, for as many items ordered?

hope this makes sense..
any/all help appreciated.
thanks in advance.
mc
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

SQL allows you to insert lots of things at once into seperate rows using the extended insert syntax:

Code: Select all

insert into table (order, thing) values (1,'Teddy Bear'),(1,'Doll'),(1,'Football'),(1,'Playstation')
As you can see, it's a normal insert except the values are repeated for each row you're entering.
michlcamp
Forum Commoner
Posts: 78
Joined: Mon Jul 18, 2005 11:06 pm

Post by michlcamp »

so, what if I have 4 or 5 fields to enter into each row...?

$item, $item_desc, $item_qty, $item_price, $item_total
michlcamp
Forum Commoner
Posts: 78
Joined: Mon Jul 18, 2005 11:06 pm

Post by michlcamp »

I failed to mention that the data I want to enter into the table is coming from a form - customer selects items ordered and then posts the form.
mc
Post Reply