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("INSERT INTO CLIENT VALUES(
NULL, '$companyname', '$contactname', '$siteaddress', '$phonenumber', NULL, '$website', '$code', '$postaladdress')") or die (mysql_error());

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




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



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


mysql_query("INSERT 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: