Last Inserted ID

[Canman2005]

Wonder if someone can advise

 

I have a script which runs on a CRON job, so every hour a script is activated.

 

When the script runs, it INSERTS between 1 and 12 records into a database table, works a charm so far.

 

When each QUERY is run on the script, it grabs the last ID number that was INSERTED and that is entered into another table, so if my script INSERTS a new row with an ID of 34877, then the ID number 34877 is entered into another database table.

 

To grab the last ID number, I have always used

 

$last_id = mysql_insert_id();

 

as seen on http://php.net/manual/en/function.mysql-insert-id.php

 

which has always worked great.

 

I now have to create another script on a CRON job, which does a similar TASK, it INSERTS a record into the same database and then grabs the last ID number.

 

The plan is to roll about 150 of these script out, so each one is INSERTING data, and grabbing the last ID of the row just created.

 

By 2015, they plan to have several thousand of these scripts, all being run at the same time.

 

This is basically part of a bigger system and this is the method in which the 3rd party suppliers need data handled, so I have no option.

 

My question is, if I have tons of scripts INSERTING data to the same database table and each time an INSERT is done, the last ID is grabbed, can PHP get overloaded and confused and then end up returning the wrong ID number of the row INSERTED. Or if I put

 

$last_id = mysql_insert_id();

 

straight after each INSERT, then is it gurenteed that the right ID number is returned.

 

Just concerned the QUERIES will end up in a que and incorrect ID numbers will be returned.

 

Basically, is

 

$last_id = mysql_insert_id();

 

flawless in getting the ID number of the row just INSERTED?

 

Cheers everyone

[BlueSkyIS]

flawless. the id is taken from the specific connection. it is not MAX(id)

[n1concepts]

It would require more insight to the actual setup you are building but based on your posted comments, here's my response (and I am currently doing something very similar to what you're addressing):

 

1. PHP won't get overloaded nor confused as it will execute the required functions or procedures FIFO method (sort of speak) and do so based on the available resources that really relates back to your physical server resources (CPU, memory, etc...)

 

The question is will you have multiple scripts all running from one server (source) or will there be multiple <individual> scripts all firing independently of one another from multiple locations. If the later, then there could be "one-off" situations to where you may have duplicate entries - two instances running to where the same ID is entered by different scripts firing at the same time.

 

It would be best to run the process from one main script - if possible - that way you can control the schedule.

If not, then figure out "the schedule" for each cron job and just ensure they (per setup or script) do not overlap one another and that should help with two scripts/jobs running at the same time.

 

There are security measures you could take - within the db - to prevent multiple insertions but that's dealing with a totally different subject.

 

Hope this helps with your design.

 

brgds,

Craig