Insert into two tables from a single form

Question

Insert into two tables from a single form. The first insert go in fine the second generates this error Duplicate entry '0' for key 1 any idea what is happening?

$connection=mysql_connect ("localhost", "foo", "bar") or die ("I cannot connect to the database.");
$db=mysql_select_db ("database", $connection) or die (mysql_error());
$query = "INSERT INTO worklog (id, newtime, datetime, clientname, clientcode, startmo, startday, startyr, endmo, endday, endyr, duemo, dueday, dueyr, market, job, allTypes, spec, status, designer, dsgnemail, adrep, ademail, frame1, frame2, frame3, rush) VALUES ('$id', $newtime, now(), '$clientname', '$clientcode', '$startmo', '$startday', '$startyr', '$endmo', '$endday', '$endyr', '$duemo', '$dueday', '$dueyr', '$market', '$job', '$allTypes', '$spec', '$status', '$designer', '$dsgnemail', '$adrep', '$ademail', '$frame1', '$frame2', '$frame3', '$rush')";
$sql_result = mysql_query($query, $connection) or die (mysql_error());

$worklog_id=mysql_insert_id($connection); 

$connection2=mysql_connect ("localhost", "foo", "bar") or die ("I cannot connect to the database.");
$db2=mysql_select_db ("database", $connection2) or die (mysql_error());
$query2 = "INSERT INTO worklognotes (worklog_id, spec) VALUES ('$worklog_id', '$spec')";

$sql_result = mysql_query($query2, $connection2) or die (mysql_error());    

Answer 1

normally with and table ID column you set it to auto-increment and never explicitly insert it. The database management system will take care of inserting that column. The error means that you are inserting a row that has that ID already, meaning the column has a UNIQUE constraint.

Answer 2

I thin the culprit is the line:

$worklog_id=mysql_insert_id($connection); 

according to the PHP documentation:

"The ID generated for an AUTO_INCREMENT column by the previous query on success, 0 if the previous query does not generate an AUTO_INCREMENT value, or FALSE if no MySQL connection was established"

So if the id in worklog is not AUTO_INCREMENT it will always return 0 ... your second attempt at running the code will cause:

Duplicate entry '0' for key 1

Two ways to fix this:

1. id for worklog should be AUTO_INCREMENT ... this way mysql_insert_id will return ther ID generated by the database and you can use it as a working id for the next query

2. just use $id instead of $worklog_id