Inserting rows in autoincrement table

[richclever]

Hi,
I have a script that integrates a set of data from one database with another ro display real estate listings. The database uses an autoincrement ID field to store the listings.

At the moment, my integration script sets the ID number a lot higher that the last record so as to not intefere with the existing data in the database. Obviously when other users enter a property manually the autoincrement will continue from my latest property ID and not fill the space between the previous last Id and my imported data.

Can anyone think of a workaround so that my data doesn't intefere with any previous listings; I think I need to use a function that gets the ID number from the last row and add records from there but just need someone to confirm my logic.

Many thanks

Richard

 

[kenrbnsn]

If the autoincrement is working properly, then just inserting your records will get you a new ID, which will not interfer with anything else there.

Why try to change how it works already?

Ken

 

[richclever]

Unfortunately the properties that are inserted by the integration script have to be be deleted every night and new ones are then automatically inserted. Also, I need to know what the ID is as photos and other info need to reference them. Because of the way the main script works I have to give them my own ID numbers otherwise I would just use the autoincrement.

 

[BitFuzzy]

I'm not quite sure what you're trying to do, but if I understand the basics you're trying to update 1 table with data from another table each using its own autoinc. ID.
Then obtain the "new" id to reference for images data etc.

you can't just get the last ID used and increment by 1 because if someone adds a listing before you do, then you'd corrupt the listing that was there. What I would do is store a temporary session id, then grab the ID associated to it to use as your needed reference. You will ofcourse need to delete the field incase you add another listing in the same session.

This can also be done using auto generated keys but I'd still recommend you delete the field in the event a key is duplicated at some point.

 

[richclever]

Almost,

It actually takes data from one database then connects to a different one (different host) and updates 3 tables. Unfortunately, the ID of the main listings table is not the ID of the other two tables (they both have a different field that relates to the original ID); So, I have three different ID numbers (one for each table) and when I add a listing (actually doing it as a cron php task which normally adds 200 properties).

Each night, the cron php task deletes all the properties that relate to me (there is a user ID field in each table) and then reinserts them along with any new ones (I know it's a bit of a waste doing it that way but I have my reasons!).

I have got the script to start adding fields at a high ID number (to really seperate my data from other postings) but obviously any postings that are done by others will be appened to the tables after my listings (because of the auto increment). I would like my data to be inserted after the other posters data and need to know the ID numbers of the main table so that I can correctly update the other two tables to have the correct ID number relating to the main table (hope tis makes sense!!!!!)

What I'm now thinking is that for the last two tables it's not really important to know the ID number (primary key) that auto updates but it is very important to know the primary key (ID) for the first as it is in a field in the other two. That's why I thought I would just get the last ID number from the database and auto increment from there. It won't affect others as the task runs when nobody else is using the site.

Hope this makes some sort of sense, it seems to have got me thinking and given new ideas on how to sort it out.

Richard

 

[DRJ478]

For MySQL:
Have you considered using the mysql_insert_id() which returns the ID of the last inserted row?
a) Insert the new row (without specifying the ID)
b) retrieve the ID generated by that insert statement with mysql_insert_id()

Is that what you are looking for?