up vote 5 down vote favorite
1

I am trying to insert 2 rows into the same table. The first will input data from a select, the second will use vars for data. I am able to insert the first row but having trouble inserting multiple rows.

The $partner_id is to link the rows to each other. For this im using a generated 32char value in php. Is there anyway to set the edit_partner_id with mysql as the id of the first row inserted or is this not possible due to the first row has to be created before you can get the last id?

Is it possible to also add an update to this or would I have to run this in a seperate query?

$sql = "INSERT INTO edits_customers (customer_id, creator_id, firstname, surname,
            house_no, address_1, address_2, address_3, city, county, postcode,
            country, email, home_tel, mobile_tel, work_tel, notes, edit_type,
            edit_partner_id )
        (SELECT *, 'before', '{$partner_id}' FROM customers WHERE customers.id = 123),
        ('{$var1}', '{$var2}', '', '', '', '', '', '', '', '', '', '', '', '', '', '',
            '', 'after', $partner_id)";

Thanks

share|improve this question

1 Answer

up vote 6 down vote accepted

If I'm understanding your question correctly, where you're trying to insert some data from another table and some data you provide yourself, you should be able to do something like this using UNION:

INSERT INTO SomeTable ( Col1, Col2, Col3 )
SELECT Val1, Val2, Val3 FROM SomeOtherTable
UNION
SELECT 'MyProvidedVal1', 'MyProvidedVal2', 'MyProvidedVal3'

Hope that helps...

share|improve this answer
Just the job. Thanks – arbme Mar 20 '11 at 5:27

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.