Need help in INSERT Statement

[smitagodbole]

Hi,

I would like to know how to write Insert data  into a table statement where some of the data is coming from another table?

I tried using Insert select statement but did not work.

Here is what I am trying....

1.........$sql = "INSERT INTO table1(id,fname, lname, gender, add1, add2, city, state, zip, country, email, phone, cellphone,employment,employmentinfo,dob, photo1,info)

VALUES('$id','$fname','$lname','$gender','$add1','$add2','$city','$state','$zip','$country','$email','$phone','$cellphone','$employment','$employmentinfo','$dob','" . $image['name'] . "','$info')

        SELECT table2.id from table2 where table2.id=$id";

 

2..........$sql = "INSERT INTO table1(id,fname, lname, gender, add1, add2, city, state, zip, country, email, phone, cellphone,employment,employmentinfo,dob, photo1,info)

VALUES((SELECT id FROM table2 WHERE table2` WHERE username='".$_POST['username']."'),'$fname','$lname','$gender','$add1','$add2','$city','$state','$zip','$country','$email','$phone','$cellphone','$employment','$employmentinfo','$dob','" . $image['name'] . "','$info')";

 

I would appreciate your help.

Thanks

Smita

 

       

[gizmola]

The syntax you want is

 

INSERT INTO table1 (columnlist....) SELECT columnname, 'constants', ...

 

You need to intermix the columns you need from the SELECT with constants you pass as the $var's you will be passing.

 

This example should illustrate the idea.

 

mysql> select * from teams;                              
+--------+-------------+
| member | hockey      |
+--------+-------------+
| bob    | Black Hawks | 
| bob    | Flyers      | 
| bob    | Kings       | 
| joe    | Caps        | 
| joe    | Penguins    | 
| bob    | Flyers      | 
+--------+-------------+
6 rows in set (0.00 sec)

mysql> select member, hockey, 'ConstantValue' from teams;
+--------+-------------+---------------+
| member | hockey      | ConstantValue |
+--------+-------------+---------------+
| bob    | Black Hawks | ConstantValue | 
| bob    | Flyers      | ConstantValue | 
| bob    | Kings       | ConstantValue | 
| joe    | Caps        | ConstantValue | 
| joe    | Penguins    | ConstantValue | 
| bob    | Flyers      | ConstantValue | 
+--------+-------------+---------------+
6 rows in set (0.00 sec)