Page 1 of 1
How to insert query result into new table
Posted: Mon Feb 17, 2003 12:23 pm
by seetherman
Newbie here:
I basicly have a shopping cart table that stores qty, items, etc. then when someone 'checks out' I want that info put into my orders table along with , payment info,etc.
I can not figure out how to insert my query result
<?php echo $row_Recordset1['itemName']; ?>
into the new table, I am only getting the first record inserted. If someone has bought several items , only the first item gets inserted.
How do i get all records from my query to go to the new table?
Thanks for any help.
Posted: Tue Feb 18, 2003 1:58 am
by twigletmac
What does your INSERT statement look like?
Mac
Posted: Wed Feb 19, 2003 7:43 am
by Elena Mitovska
Insert into orders (field1, field2, .....fieldn) SELECT field1, field2,...., fieldn from shopping_cart WHERE id_user=....
It will select from you shopping cart table values and insert that recordset into order table. Only make sure that amount and types of columns of table order exactely match the amount and types of selected columns from table shopping cart.
ust one more trick: if amoutn of columns in orders table more that amount of columns in table shopping cart, you can use this:
Insert into order SELECT NULL, field1, NULL, field2, field3.... from shoppping_cart
So to keep theamount of columns equal use NULL to put them on place of
missing columns.
Posted: Wed Feb 19, 2003 2:09 pm
by seetherman
I have tried 2 different insert statements. I am using DreamweaverMX and this is what it generates:
if ((isset($HTTP_POST_VARS["MM_insert"])) && ($HTTP_POST_VARS["MM_insert"] == "form1")) {
$insertSQL = sprintf("INSERT INTO orders (name, qty, itemNo, ccNo) VALUES (%s, %s, %s, %s)",
GetSQLValueString($HTTP_POST_VARS['name'], "text"),
GetSQLValueString($HTTP_POST_VARS['qty'], "int"),
GetSQLValueString($HTTP_POST_VARS['itemNo'], "text"),
GetSQLValueString($HTTP_POST_VARS['ccNo'], "text"));
But it does not work, only the first record in the cart table gets inserted. I can see why, the variable can only be one item not several.
So, with my limited knowledge I thought I might need something like this:
if ((isset($HTTP_POST_VARS["MM_insert"])) && ($HTTP_POST_VARS["MM_insert"] == "form1")) {
$insertSQL = sprintf("INSERT INTO orders (name, qty, itemNo, ccNo) VALUES (%s, {$row_Recordset1['qty']}, {$row_Recordset1['itemNo']}, %s)",
GetSQLValueString($HTTP_POST_VARS['name'], "text"),
GetSQLValueString($HTTP_POST_VARS['ccNo'], "text"));
But that is even worse, it generates errors.
I am confused on how to write the insert statement with the previous advice. My Orders table does have more columns then cart table. In cart table I add a payment type, credit card and status field and instead of a cartId field I have a username field.
This is my table structure
CART TABLE
cartId,qty,itemNo,price,itemName
in ORDERS TABLE
date,name,qty,itemNo,status,paymentType,ccNo
SO can you help me with the INSERT staement. I don't know where to put the NULL and does the order of the fields need to match?
Do I need to change the structure of my cart table to have qty,itemNo first. NOT cartID like it is now.
Also, as if that was not confusing enough , Not only do I need the cart contents sent to the orders table but the user is adding the credit card number too.
I display the cart results then have a field for card number input, then the submit button, which I need to transfer to the cart table and the new field with card # to the orders table.
Well, any advice you all can give me is much appreciated...
Thanks alot!
?>