How to insert query result into new table

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
seetherman
Forum Newbie
Posts: 2
Joined: Mon Feb 17, 2003 12:23 pm

How to insert query result into new table

Post 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.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

What does your INSERT statement look like?

Mac
Elena Mitovska
Forum Newbie
Posts: 6
Joined: Mon Nov 18, 2002 3:43 am
Location: Ukraine
Contact:

Post 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.
seetherman
Forum Newbie
Posts: 2
Joined: Mon Feb 17, 2003 12:23 pm

Post 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!



?>
Post Reply