Jump to content

PHP/MSSQL insert into joined tables


agr8lemon

Recommended Posts

Hello everyone,

 

I know there are a lot of articles about inserting into joined tables, but they all seem to do with auto incrementing both Keys at the same time. Mine is different and I'm hoping someone can help.

 

I have two tables with a PK/FK relationship

 

Emp_tbl

----------

ID (PK, int, not null, autoinc)

Depart_ID (FK, int not null)

 

Depart_tbl

----------

Depart_ID(PK, int, not null, autoinc)

 

On my form I have a drop down that pulls the data from B to a drop-down that creates the value of the dropdown the integer of Depart_ID as follows:

 

$sql_depart="SELECT Depart_Name, Depart_ID, Depart_Facility FROM dbo.Depart_tbl WHERE Disabled = '0' ORDER BY Depart_Name ASC";
$departResult=odbc_exec($dept_conn, $sql_depart);
$departOptions="";


while($departRows=odbc_fetch_array($departResult)){
$departId=$departRows["Depart_ID"];
$Departments=$departRows['Depart_Name'];
$departOptions.="<OPTION VALUE=\"$departId\">".$Departments.'</option>';
} 
odbc_close($dept_conn);

 

So what I want to happen is to insert the data data into the Emp_tbl along with the proper Depart_ID for Depart_tbl (Keep in mind I don't want to change the Depart_tbl at all, just grab the PK value of and put that into the Emp_tbl for the join).

 

Then this is my insert page:

 



$_POST['Depart_ID'] = (int)$Depart_ID;

///Define our insert
$sql="INSERT INTO dbo.Emp_tbl (Emp_First, Emp_Last, Emp_Title, Emp_Phone, Emp_Cell, Emp_Pager, Emp_Fax, Depart_ID) VALUES ('$_POST[Emp_First]','$_POST[Emp_Last]','$_POST[Emp_Title]','$_POST[Emp_Phone]','$_POST[Emp_Cell]','$_POST[Emp_Pager]','$_POST[Depart_Fax]','$Depart_ID')";

///Make the insert
$result=odbc_exec($emp_conn, $sql);

 

The error I get is:

Warning: odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][ODBC SQL Server Driver]

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Emp_Info_Emp_Department". The conflict occurred in database "PhoneDir", table "dbo.Depart_tbl", column 'Depart_ID'., SQL state 23000 in SQLExecDirect in 

 

 

Does this make sense? Can anyone help me?

 

Thank you!

 

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.