non-autoincrement incrementing

[Pig]

I have a table named articles, with 2 rows of interests, article_id and article_set.

article_id is the primary and is autoincremented.
article_set is identifies a set of articles that are entered into the database at the same time. All of the articles entered as a set should (obviously) have the same article_set.

I would like to be able to increment the sets, but I'm not sure how to go about it.

My orginal plan was to query the table and find the current max article set, then just add one to it, and use that for the article_set for each subsequent insert query. However, if 2 users were uploading at the same time, then there is the chance that the second user could get the old max value before the query updates the database, giving both users the same article_set.

I have overcome this before by using mysql_insert_id, but that won't solve this problem since I can't have 2 autoincrements.

The work around I came up with is to have a separate table with 2 columns: article_id and article_set(autoincremented). I could insert the first item without an article_set, and let it be auto-assigned. I could then grab the article_set with mysql_inserted_id and use that for the remaining queries.

That seems like it would work, but it also seems like an overly complicated method. Does this method sound OK? Does anyone have a better thought of how to do this?

Thanks for your help.

[Pig]

errrg... now that I read this again, I don't think even that will work. If you have an auto-increment, then it must be a primary key, and that means that you can't have duplicate entries...

[[deleted]]

I think a little more explenation is in order.

You are inserting articles and every article is a member of a set? Then why is there not a table 'set's that defines which sets exist (with an auto_increment key)

The you'd first create a set, fetch the auto_increment ID from that, and use that number as the set_id in the articles table, completely seperated from the article_id which is also auto_increment.

[Pig]

That makes sense... use a separate table just for incrementing, and keep the actual data in the original table. Thanks.

[[deleted]]

NO, not just for incrementing, the new table contains the information about which sets exist. YOu can use this table for a lot more than just the numbers.

[Pig]

All of my information is unique to the article, except the set. I see what you are saying. If the set had attributes, it would work fine for those as well. The set just doesn't have any unique attributes.

Thanks.