Problems with last_insert_id( )

[simbarashe]

I am trying to insert an auto_increment value into multiple tables but its seems to lose its value after the the second insert statement and my code is as follows can any1 please help. The auto_increment value that i want to use is the one from the client table and it will insert in the CONTACTS table but it will not in the JOBSHEET and KNOWLEDGE table. last_insert_id( ) has not worked because i have auto increment fields in all the tables pliz pliz help

mysql_query("IN SERT INTO CLIENT VALUES(
NULL, '$companyname', '$contactname', '$siteaddress', '$phonenumber', NULL, '$website', '$code', '$postaladdress ')") or die (mysql_error()) ;

mysql_query('SE T @clientID = last_insert_id( )') or die (mysql_error()) ;




mysql_query("IN SERT INTO CONTACTS VALUES( NULL, '@clientID' )") or die (mysql_error()) ;



mysql_query("IN SERT INTO JOBSHEET VALUES( NULL, '@clientID')") or die (mysql_error()) ;


mysql_query("IN SERT INTO KNOWLEDGE VALUES( NULL, '@clientID', '$categoryID', '$staffID', '$knowledge')") or die (mysql_error()) ;

[ronverdonk]

MySQL doc: The currently executing statement does not affect the value of LAST_INSERT_ID( ). Suppose that you generate an AUTO_INCREMENT value with one statement, and then refer to LAST_INSERT_ID( ) in a multiple-row INSERT statement that inserts rows into a table with its own AUTO_INCREMENT column. The value of LAST_INSERT_ID( ) will remain stable in the second statement; its value for the second and later rows is not affected by the earlier row insertions. (However, if you mix references to LAST_INSERT_ID( ) and LAST_INSERT_ID( expr), the effect is undefined.)

Sample from M ySQL docs: If you want store the last id for use in multiple insert statements:

create table person (
id bigint not null primary key auto_increment,
name char(100) not null
);
insert into person set name = 'Joe';
select @id:=id as id from class where id = last_insert_id();
insert into some_other_table set person_id = @class_id;
insert into yet_another_table set person_id = @class_id;

Ronald :cool: