[SOLVED] mysql_insert_id ..... in just one query

[jonniejoejonson]

I am inserting a new row into a table that has an auto increment index.

However (only sometimes) I want to use the incremented index value as another value in the row being inserted.

 

eg.

 

i am inserting into table

 

id   name    original_id     message

 

however sometimes i want the original_id to have the same value as my unknown auto-increment id.

 

Is there a way to do this in one sql statement or do i have to:

 

 

eg:

 

1/ sql to insert new row

$valueId=mysql_insert_id();

2/ sql to update inserted row so that original_id=$valueId

 

can this not be done with just one sql statement?

thanks to any responders.

[obsidian]

I don't believe it can be done, because the initial INSERT query has to be completed for the initial auto_increment value to be generated. A possible workaround, though somewhat sloppy, would be to have the original_id value default to NULL unless you provide a value. Then, whenever you query that table, you can us an if null comparison to return the auto_increment value instead of the null.

 

So, to do the data as you require will take the two steps, but there are workarounds that may not require you to use two queries every time.

 

**EDIT**

One other thought would be to set them all to NULL when you want the auto_increment value represented and have a CRON job that runs every X number of minutes or hours and updates them all at once:

UPDATE table_name SET original_id = id WHERE original_id IS NULL;

[jonniejoejonson]

okay thanks obsidian,

will just do the 2 queries then... thanks for your help. J.