Forum Moderators: coopster
I have a web form for updating projects that feeds a bunch of mysql tables. The problem is that some of the fields on the form may generate more than one entry. So for example, I may have a project that is working into 5 countries.
Now, my question is this...
Say I have a join table that looks like this:
________________________________
¦ ID ¦ project_ID ¦ country_ID ¦
¦ 1 ¦ 1 ¦ 4 ¦
¦ 2 ¦ 1 ¦ 3 ¦
¦ 3 ¦ 1 ¦ 37 ¦
Now, say I update project 1, so that now I'm working in countries 5,7,9,15.... how do I do that?
At the moment, the only way I can think to do it is to delete all existing records, then replace them with entirely new ones. This works, but as you can imagine, eventually we will run up to very high index numbers, and have lots of "empty space" in the lower IDs.
Hope that makes some sense. Any help appreciated. Oh, and while I'm here, is there a way to re-set auto_increment fields?
Many thanks
Andy
The only other option I can think of would be to read the rows where the current project_ID is the one you want to UPDATE and store the country_ID's in an array. Then perform an UPDATE on the same project_ID records replacing each country_ID with the new country_IDs you have. The problem here is that you are going to have to count how many old and how many new country_IDs you have in case you need to DELETE any extras still existing in the file or INSERT for any new country_IDs. What a mess. Stick with your original plan.
The real question here is why do you even have an ID field in this table? Is it necessary? If not, get rid of it. It seems the project_ID is your PRIMARY KEY here. Now you don't have to worry about the AUTO_INCREMENT whatsoever.
As far as resetting an AUTO_INCREMENT column, have a look at this thread:
Speeding up mysql inserts with large amount of data [webmasterworld.com]
The ID was habit, there's no need for it in this table! Thanks matey :)
Andy
