[SOLVED] INSERT data with a SELECT

[ainoy31]

I am trying to insert data with a select statement into a table.  The tables are as follow:

 

-flight_schedule-

fs_ID int(16) NOT NULL auto_increment, fs_date varchar(32) NOT NULL, fs_deptport varchar(32) NOT NULL, fs_destport varchar(32) NOT NULL, PRIMARY KEY (fs_ID)

 

-carrier-

carrier_ID int(16) NOT NULL auto_increment, fs_ID int(16) NOT NULL, carrier_name varchar(64) NOT NULL, carrier_phone varchar(12) NOT NULL, carrier_email varchar(32) NOT NULL,

 

PRIMARY KEY (carrier_ID), FOREIGN KEY (fs_ID) REFERENCES flight_schedule (fs_ID)

 

Here is my script to insert my info:

$sql1 = "INSERT INTO flight_schedule (fs_ID, fs_date, fs_deptport, fs_destport) VALUES ('', '8/4/2007', 'KDOV', '2K3')";

mysql_query($sql1) or die("Error: " . mysql_error());

 

$sql2 = "INSERT INTO carrier SELECT * FROM flight_schedule WHERE carrier.fs_ID = flight_schedule.fs_ID;";

mysql_query($sql2) or die("Error: " . mysql_error());

 

I receive the message - Error: Unknown column 'carrier.fs_ID' in 'where clause'

Is this because I do not have carrier_ID in the flight_schedule table?

 

Much appreciation.

[ainoy31]

What I mean is that should I have carrier_ID as the foreign key in the flight_schedule table?

[Illusion]

 

$sql1 = "INSERT INTO flight_schedule (fs_ID, fs_date, fs_deptport, fs_destport) VALUES ('', '8/4/2007', 'KDOV', '2K3')";

I don't know why u r inserting NULL value as u have NOT NULL constraint on fs_ID column.

The length you have defined for fs_ID int(16) does not work as int can store the values between -2147483648 to 2147483647.

$sql2 = "INSERT INTO carrier SELECT * FROM flight_schedule WHERE carrier.fs_ID = flight_schedule.fs_ID;";

I don't know what u exactly want to do with that insert statement . As u have already have a foreign key referenced to fs_ID in flight_schedule so we can insert the rows that have carrier.fs_ID value that is in flight_schedule.fs_ID.Now tell what u want to do with that insert statement.INSERT with SELECT works only when the the no of columns and the datatypes of each column of both the tables match to each other.