Page 1 of 1

Posting each item ordered in its own table row

Posted: Sun Dec 18, 2005 8:38 am
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

Posted: Sun Dec 18, 2005 8:43 am
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.

Posted: Sun Dec 18, 2005 9:13 am
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

Posted: Sun Dec 18, 2005 9:54 am
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