MySQL: INSERT INTO with static and dynamic values mixed

Question

I am trying to "syphon" data over from one table to another. The problem I run into is that some of the data which is going in the new table is static, other data is being copied from an existing table. Let me start by showing the query I was trying to run:

INSERT INTO current_cart 
    (cart_ID, 
    cart_PO, 
    cart_user, 
    cart_date, 
    cart_qty, 
    cart_sku, 
    cart_description, 
    cart_price, 
    cart_linetotal) 
VALUES 
    ('$cartID', 
    '$poNumberNew', 
    '$email', 
    '$lineDate',
SELECT 
    orderdetail_qty, 
    orderdetail_sku, 
    orderdetail_description, 
    orderdetail_price, 
    orderdetail_linetotal
FROM orderdetail
WHERE orderdetail_custemail = $email AND orderdetail_po = $poNumber)

Obviously, all the PHP variables are declared beforehand. Essentially what I have running is a shopping cart. This query would take the items from a previous order and enter them in a new shopping cart so our customer can start a new order based on a previous order.

The problem I run into is how to insert records into a table when some of the data for the record is static (cartID, poNumberNew, email and lineDate) and other information is coming out of a different table? I am hoping I can do this without creating a loop to repeat a query for however many items there were in the order the customer is duplicating... I think that would bog down our site significantly.

I've seen a myriad of awesome answers on many other web dev questions I've had in the past, I'm hoping the stackoverflow community can help me out here...

Thanks!

ESG · Accepted Answer · 2012-05-09 20:25:16Z

3

in a SELECT statement, you can have a column that returns an constant. For example,

INSERT INTO current_cart 
    (cart_ID, 
    cart_PO, 
    cart_user, 
    cart_date, 
    cart_qty, 
    cart_sku, 
    cart_description, 
    cart_price, 
    cart_linetotal) 

SELECT 
    '$cartID', 
    '$poNumberNew', 
    '$email', 
    '$lineDate',
    orderdetail_qty, 
    orderdetail_sku, 
    orderdetail_description, 
    orderdetail_price, 
    orderdetail_linetotal
FROM orderdetail
WHERE orderdetail_custemail = $email AND orderdetail_po = $poNumber)

answered May 9, 2012 at 20:25

ESG

9,4754 gold badges41 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

BelgianAlien Over a year ago

Wow... so simple... and so effective! Thanks, TheVedge!

ESG Over a year ago

Anytime! It also works in insert, delete, update, and wherever you would usually have a column name, really.

Collectives™ on Stack Overflow

MySQL: INSERT INTO with static and dynamic values mixed

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related