One insert into to two tables form one form +array?

ovim

I've edited this because I've made some progress, but still need help.

I'm trying to insert into a table "order_details" data fetched from another table ("shoping_cart") and I also need to insert an order_id which I'm getting from previous insert with "mysql_insert_id". I'm getting the data from "shopping_cart" with session_id and it can be one or more rows.

The problem is that my query only inserts one row even if there should be more. I've tried for and while (not sure if I did them right though) loops. The only result was that the one inserted row was the last one of selected (from "shopping_cart") when it earlier was the first one.

This is my query now:

$myResult1ID = mysql_insert_id();

$query = "SELECT  sel_item_id, sel_item_qty FROM shopping_cart WHERE session_id = '$sessionnbr'"; 

$result = mysql_query($query) or die(mysql_error());

while ($row = mysql_fetch_array($result)) {
$sel_item_qty = $row['sel_item_qty'];
$sel_item_id = $row['sel_item_id'];
$insertSQLtwo = "INSERT INTO order_details(order_id, sel_item_id, sel_item_qty)
VALUES (".$myResult1ID.", ".$sel_item_id.", ".$sel_item_qty.")";
}

before edit : I dont know much of php, but i started making this webstore (just for fun, not for real life) using Dreamweavers recordsets and data insertion form wizards and such.

Soon I realized that I couldn't do everything using wizards and now I've got a problem.

I've got a "shopping cart table" to which customers' selected products go.
Each row in that table hold id, session_id, item_id and item_qty.

When customers check out they fill a form where they enter their address etc. This forms data is inserted to "orders" table which holds order_id, customer info, order total sum etc. The details of items in every order are stored in "order_details" table which holds id, order_id, item_id, item_qty.

So basically every order creates one row to "orders" table and one or several rows to "order_details" table.

I first made the checkout form with dreamweaver and it inserts into "orders" (this works) then I modified (according to some tutorial )the form so that it should also insert into "order_details" (this works partly).

After the first part of insert into (to orders) I used mysql_insert_id to retrieve the order_id and insert it to order_details (this works, for every order I get one row to "order_details" with the correct order_id and NULLs to item_id and item_qty)

Here is my problem. I dont know how to make the insert into query so that it inserts one or multiple rows (of item_ids and qtys) to order_details and inserts the same order_id to all of these rows.

I've managed to get the required info from "shopping cart" with this query.

$query = "SELECT session_id, sel_item_id, sel_item_qty FROM shopping_cart WHERE session_id = '$sessionnbr'"; 

$result = mysql_query($query) or die(mysql_error());


while ($row = mysql_fetch_array($result)) {
$sel_item_qty = $row['sel_item_qty'];
$sel_item_id = $row['sel_item_id'];
}

How can I insert this data to my "order details"?

Please Help!

auday1982

hello

i am new to php as well , but i will suggest something as an idea because i dont have the ability to share codes yet

call the sel_item_qty and the id new names ( lets say sell_item_qty2 )

run insert into again but this time make it insert into order details

you can add a code to the cart processor file ( the php file that insert new data into shopping cart table ) and insert the same or whatever you want to pick from the details into order details table

simply if i want to insert 1 2 3 4 5 6 into a table and 1 3 5 into another table i will have 2 lines

insert into table1

INSERT INTO table_1 (column1, column2,column3,column4,column5)
VALUES (1, 2,3,4,5)

and

INSERT INTO table_2 (column1, column2,column3)
VALUES (1, 3,5)

you can have some if statement to control when and what to insert

i am sorry if i can not help , new and just sharing ideas

ovim

Thanks for your reply, but I think I'm looking for somekind of a loop.

Now I've gotten the sel_item_id and sel_item_qty to insert into "order_details".
But only once for an order even if I have more than one product.

this is my query now, how can I make it insert as many rows as there is in "shoppingcart"?

$myResult1ID = mysql_insert_id();

$query = "SELECT session_id, sel_item_id, sel_item_qty FROM shoppingcart WHERE session_id = '$sessionnbr'"; 

$result = mysql_query($query) or die(mysql_error());

while ($row = mysql_fetch_array($result)) {
$sel_item_qty = $row['sel_item_qty'];
$sel_item_id = $row['sel_item_id'];
$insertSQLtwo = "INSERT INTO order_details(order_id, sel_item_id, sel_item_qty)
VALUES (".$myResult1ID.", ".$sel_item_id.", ".$sel_item_qty.")";
}

auday1982

hello

from what i can understand , you have like more than one item sold to the customer , and you want to have the shopping cart displaying each item with ID number and Quantity , even if not displaying it to the user ,,, u want to keep it for your record

i think you can try
and lets say $id is what you definded for the item id as a variable to store the form $_post or get , or the database column data

foreach($sel_item_id as $id) {
insert into statment 
}

this maybe will loop for every existing item id ... , so for id 1 - insert . id2 insert ... when done with all ids ... nothing left ...the foreach will end

this what i know about php ..it can do it that way ... so go read more about foreach and sql ...

see this page as well

they r creating functions and doing the foreach to insert into database a form data

http://ca3.php.net/function.mysql-query

benracer

Hi,
i have created a live ecommerce site and it is far easier to store the carts products in a session by there id and qty. Then on the cart a loop looks at the session and pulls each id and finds the data from the items database. The order can then be saved as it is in the session, into another database. this is done by using serialize which converts the session's array into a text string.

benracer

So to make it clear we have...

items - this has the columns id, title, price ect

orders - this has order number, carts array that has been serialize'd

ovim

I updated also my first post with this:

This is my query now:

$myResult1ID = mysql_insert_id();

$query = "SELECT  sel_item_id, sel_item_qty FROM shopping_cart WHERE session_id = '$sessionnbr'"; 

$result = mysql_query($query) or die(mysql_error());

while ($row = mysql_fetch_array($result)) {
$sel_item_qty = $row['sel_item_qty'];
$sel_item_id = $row['sel_item_id'];
$insertSQLtwo = "INSERT INTO order_details(order_id, sel_item_id, sel_item_qty)
VALUES (".$myResult1ID.", ".$sel_item_id.", ".$sel_item_qty.")";
}