472,133 Members | 1,045 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,133 software developers and data experts.

Problems with last_insert_id()

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

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());
Nov 27 '06 #1
1 4040
4,258 Expert 4TB
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:
Expand|Select|Wrap|Line Numbers
  1. create table person (
  2. id bigint not null primary key auto_increment,
  3. name char(100) not null
  4. );
  5. insert into person set name = 'Joe';
  6. select @id:=id as id from class where id = last_insert_id();
  7. insert into some_other_table set person_id = @class_id;
  8. insert into yet_another_table set person_id = @class_id;
Ronald :cool:
Jan 17 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Irene Mettias | last post: by
1 post views Thread by Michal Adamczakk | last post: by
6 posts views Thread by jim | last post: by
3 posts views Thread by Vic Spainhower | last post: by
3 posts views Thread by jx2 | last post: by
2 posts views Thread by WebSnozz | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.