insert primary key into foreign key

andynightingale

I'm trying to alter my dreamweaver insert page code so that it takes the auto-incremented primary key number and inserts it into a second table as a foreign key so I can link the tables. Im using the "LAST_INSERT_ID" though I understand there is a similar bit of php. At the moment it keeps saying "Column 'road' cannot be null" IS this the best route as I'll have several tables all with the foreign key.

if ((isset($HTTP_POST_VARS["MM_insert"])) && ($HTTP_POST_VARS["MM_insert"] == "form1")) {
$insertSQL = sprintf("INSERT INTO parent (parent_id, f_name, l_name) VALUES (%s, %s, %s)",
                       GetSQLValueString($HTTP_POST_VARS['parent_id'], "int"),
                       GetSQLValueString($HTTP_POST_VARS['f_name'], "text"),
                       GetSQLValueString($HTTP_POST_VARS['l_name'], "text"));

mysql_select_db($database_test_conn, $test_conn);
$Result = mysql_query($insertSQL, $test_conn) or die(mysql_error());				   

$insertSQL = sprintf("INSERT INTO child (child_id, parent_id, road, city) VALUES (%s, LAST_INSERT_ID(), %s, %s)",
					   GetSQLValueString($HTTP_POST_VARS['child_id'], "int"),
                       GetSQLValueString($HTTP_POST_VARS['parent_id'], "int"),
                       GetSQLValueString($HTTP_POST_VARS['road'], "text"),
                       GetSQLValueString($HTTP_POST_VARS['city'], "text"));

mysql_select_db($database_test_conn, $test_conn);					   

$Result = mysql_query($insertSQL, $test_conn) or die(mysql_error());

Roger_Ramjet

I'm sure that code makes sense to some people and most of it works. To me it just looks like a jumbled mess, but then that is what I expect from code generated by Dreamweaver. I personally have no idea what the '%s' in your VALUES is all about, and I'm not likely to find out as I would code the whole thing completely differently.

That said, your error means that there is nothing in the $HTTP_POST_VARS['road'], and the column has been defined as NOT NULL, so the insert failed.

On your second point, you should use the php function mysql insert id immediately after the insert query and pass the value it returns to the rest of your insert queries. Makes it much more efficient and reliable. It also give you a test for success of the insert query as a zero means that no autoinc was generated ie the insert failed.

andynightingale

I'm afraid Im not a coder and have to use a crutch (metaphorically speaking) Heres the new code using the mysql_insert_id();. No errors and it inserts into both tables, but in the child table, parent_id it just puts NULL for each row. The column is set to NULL, but surely will to force a value ie not null

if ((isset($HTTP_POST_VARS["MM_insert"])) && ($HTTP_POST_VARS["MM_insert"] == "form1")) {
  $insertSQL = sprintf("INSERT INTO parent (parent_id, f_name, l_name) VALUES (%s, %s, %s)",
                       GetSQLValueString($HTTP_POST_VARS['parent_id'], "int"),
                       GetSQLValueString($HTTP_POST_VARS['f_name'], "text"),
                       GetSQLValueString($HTTP_POST_VARS['l_name'], "text"));
					   $parent_id = mysql_insert_id();

mysql_select_db($database_test_conn, $test_conn);
$Result = mysql_query($insertSQL, $test_conn) or die(mysql_error());

if(isset($parent_id));

$insertSQL = sprintf("INSERT INTO child (child_id, parent_id, road, city) VALUES (%s, %s, %s, %s)",
					   GetSQLValueString($HTTP_POST_VARS['child_id'], "int"),
                       GetSQLValueString($HTTP_POST_VARS['parent_id'], "int"),
                       GetSQLValueString($HTTP_POST_VARS['road'], "text"),
                       GetSQLValueString($HTTP_POST_VARS['city'], "text"));

mysql_select_db($database_test_conn, $test_conn);					   

$Result = mysql_query($insertSQL, $test_conn) or die(mysql_error());