[UPHPU] Dual ID numbers
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 Newbold MNE - Mac Newbold Enterprises, LLC
mac at macnewbold.com http://www.macnewbold.com/
More information about the UPHPU