[SOLVED] Assigning a new ID

[almightyegg]

I wat to create 2 tables in the same page, they are connect so that the ID of table 1 is a foreign key in table 2

 

Because of this I need to code the new id in rather than auto_increment so I can place it in the table 2 when creating it. I've tried to do this:

$maxid = mysql_query("SELECT max(id) FROM log");
$newid = $maxid+1;
$insert = mysql_query("INSERT INTO log (`id`, `userid`) VALUES('$newid', '{$mem['id']}')") or die(mysql_error());

$insert2 = mysql_query("INSERT INTO fishlog (`fish`, `logid`) VALUES('{$fish['code']}', '$newid')") or die(mysql_error());

 

Only problem is $newid is always 16? Despite it being the first row it's 16.... then the second time it says multipul value of 16, so what do I do???

[GingerRobot]

That's not a good method. If you have simultaneous requests to this page, there's no guarantee that after selecting the maximum id and adding one, another record wont be inserted by another request before this request does the insertion. You should use mysql_insert_id to prevent this.

[almightyegg]

Hmm... well it inserts correctly into one table (table 1) but it has a 0 value in table 2....

$newid = mysql_insert_id();
$insert = mysql_query("INSERT INTO log (`id`, `userid`) VALUES('$newid', '{$mem['id']}')") or die(mysql_error());

$insert2 = mysql_query("INSERT INTO fishlog (`fish`, `logid`) VALUES('{$fish['code']}', '$newid')") or die(mysql_error());

[GingerRobot]

You need to call mysql_insert_id() after inserting - the description of the function does say "Get the ID generated from the previous INSERT operation".

 

For example:

 

$insert = mysql_query("INSERT INTO log (`userid`) VALUES('{$mem['id']}')") or die(mysql_error());
$newid = mysql_insert_id();
$insert2 = mysql_query("INSERT INTO fishlog (`fish`, `logid`) VALUES('{$fish['code']}', '$newid')") or die(mysql_error());