[RESOLVED] Insert Into From Table AND Values

cisforcasey

Hello,

I am trying to write a PHP script that has a SQL statement which will insert rows from table A into table B. Table B has the same schema as table A except that table B has one additional field. I want to insert all the data from table A into table B and also insert a value that does not exist in table A (it's a separate variable I define in the PHP script) into the new field of table B. Is this possible to do this in one SQL statement? I've been trying to modify a INSERT INTO table A SELECT FROM table B query, but I can't get it to work. Thanks for your help.

Casey

sneakyimp

It would be helpful to see the query and know more specifically what the error is.

cisforcasey

sorry, I guess I was just asking if it could be done in theory. Here is the code I have so far:

$workshop = $_REQUEST['workshop'];

$signup_list = $workshop . "_signup_list";

//insert all the records from the signup list into the master list
$query = "INSERT INTO master_rb_list (ID,fname,lname,address,zipcode,watershed,lat,lng,phone,email,btype,downspout,wateruse,refer,rights,org,workshop) SELECT ID,fname,lname,address,zipcode,watershed,lat,lng,phone,email,btype,downspout,wateruse,refer,rights,org $workshop FROM $signup_list";

$workshop is the variable that isn't in table A, but that I want to insert into table B when I insert table A's row.

Here is the error I'm getting from the above code:

Invalid INSERT query: Column count doesn't match value count at row 1

I know my code is not the way to do this, but I'm not sure what to do. thanks for your help.

sneakyimp

you need a comma after org in the second half of your query.

Also, $workshop needs to be quoted and escaped ([man]mysql_real_escape_string[/man]) if it is a string value. Numeric values don't need quotes.

Also, if you want populate B with the same value of $workshop for every record, then this approach will. If $workshop must be different for every record, it must either be derived from A using SQL syntax or you must repeatedly run insert queries from php.

hope that makes sense.

cisforcasey

thanks for your help. I read up on mysql_real_escape_string and implemented it in my query. here is my new query


$query = sprintf("INSERT INTO master_rb_list (ID,fname,lname,address,zipcode,watershed,lat,lng,phone,email,btype,downspout,wateruse,refer,rights,org,workshop) SELECT ID,fname,lname,address,zipcode,watershed,lat,lng,phone,email,btype,downspout,wateruse,refer,rights,org,%s FROM $signup_list", mysql_real_escape_string($workshop));

now the error message I get is:
Invalid INSERT query: Unknown column 'PP_20080707' in 'field list'

I understand why it is returning this message. PP_20080707 (the value of $workshop) is not a column in table A and its trying to select the data from the column. how do I modify this query to say, select a record from table A AND take the variable $workshop, and insert them into Table B?

thanks for your help.

leatherback

I think you need a 'values' right before the select. Assuming that you want to insert the selected data. I am not sure whether you can actually select more than 1 column here though?

cisforcasey

I tried adding VALUES to my query and I got an invalid syntax error. thanks for the tip though.

I've done some more reading, and I'm starting to think what I want to do isn't possible in one query.

Is there a way to return the number of rows that were added/modified after running a query?

leatherback

you need the values keyword somewhere, otherwise mysql doen't know what are colums and what values.

Yes: mysql_num_row/mysql_affected_rows

cisforcasey

as far as I understand, since I'm selecting the values from another table to insert, I don't use VALUES. my problem is that I want to insert values from another table and insert a value from a variable at the same time.

leatherback

even if you are slecting the values from a table, you need to specify in the query when your values start. Anyway. I'll leave you potter as you seem to know best anyway.

cisforcasey

well, thanks for your help anyway. my last post was just based off what I've read from here: http://dev.mysql.com/doc/refman/5.0/en/insert-select.html. no harm intended.

sneakyimp

i think the insert/select statement drops the values keyword.

A query like this can insert many records:

$foo = 'some value';
$sql = "INSERT INTO new_table_name (field1, field2, field3, newField)
    SELECT field1, field2, field3, '" . mysql_real_escape_string($foo) . "' from old_table_name";

but every record will have the same value inserted into newField, namely 'some value'.

The reason you were getting that error is probably because you don't put quotes around your value for $workshop so the sql statement was interpreting the value of $workshop as a field name rather than as a text string.

cisforcasey

beautiful! thanks a lot man. I didn't know how to get it to recognize that $workshop wasn't from the table. I guess the quotes did it. thanks a lot for your help.