[UPHPU] Dual ID numbers

Mac Newbold mac at macnewbold.com
Tue Jun 7 10:52:29 MDT 2005


Today at 10:31am, TJ Hunter said:

>> Let me give an example: I have a music collection of three composers. I
>> want to personally assign an ID number to each composer (1, 2, 3). Then
>> I want to enter the info for each CD (with a drop-down list to choose
>> the composer #1, 2, or 3), and have a MySQL auto-increment number
>> assigned for each CD. The problem is that I want to have CD 1-20 for
>> Composer 1, CD 1-20 for Composer 2, and CD 1-20 for Composer 3.
>
> As far as I know, MySQL doesn't support what you're trying to do.
> Here's what I would do though, let MySQL auto increment your primary
> key as normal and use that key for relationships, then create another
> field for your special id. you'll have to write your own php code to
> figure out what the next id is going to be instead of relying on the
> user to type it in or MySQL generating it for you.

The only way you'd get MySQL to generate independent autoincrement numbers 
for the CDs by each composer would be by having a separate table for each 
set of CDs. That probably isn't a wise way to go, since you'd have to 
dynamically create a new table every time a composer was added.

I'm with TJ on this one... have your PHP make your IDs. If you want to do 
it Right (with a capital R), you'll need transactions, so that you can 
find out what the highest number is, and put in the next one without 
someone else sneaking in between and duplicating your number. If that's 
not a problem, the more reasonable way to do it in practice, is just to 
put a check right next to an insert, since people will be unlikely to be 
adding a CD for the same composer at exactly the same time. Even if they 
tried, it would be hard to pull it off, and easy to fix if it did happen.

Mac

--
Mac Newbold		MNE - Mac Newbold Enterprises, LLC
mac at macnewbold.com	http://www.macnewbold.com/



More information about the UPHPU mailing list