Assigning a complex multi-variable ID with auto-increment?

[openrhodes]

Hello all.  I'm new here and I'm stuck on this one:

 

My DB has two tables:  Artists and Songs.  Because of the amounts of different fields and variables for each song, we need to have those two separate tables.

 

We first enter an artist and the DB assigns a unique ARTIST_ID, by auto-increment.

 

Then we use the other form and enter the songs one by one into the songs table.  A unique SONG_ID is assigned.  As part of the song input form, we enter the artist ID to associate the song with.  This way we can keep adding songs whenever we want, and simply enter the artist ID as its own text field, which we will then query to list all the appropriate matches.

 

I have a site that pulls up mp3s based on the SONG_ID, but  I want a way to assign each new song an ID that's also associated with an artist -- a more "intelligent" ID than a simple autoincrement.

 

For example, artist #1 should have song ids of 0001001, 0001002, 0001003, etc.  and  artist #2 should have song ids 0002001, 000202, 000203, etc.  The first four digits are the artist, and the last three are the song id.

 

Is it possible to assign a song ID in my DB based on the artist id, rather than counting up by one every time? 

 

I'm trying to find a solution that will do something like this: If the artist id has no songs associated with it, begin assigning IDs at 0.  If it does have songs associated with it, then begin it at the next number.  Also, add the artist ID to the beginning of that song ID, in a similar way to an Lpad function.

 

Thanks in advance for any assistance!

 

Jeff

[cmgmyr]

why don't you just use the regular auto_increment numbers?

[openrhodes]

why don't you just use the regular auto_increment numbers?

 

I was doing just that, but then it was decided that each song id should start over for each new artist.  That way, each associated MP3 file would be easy to locate by artist and number.  Otherwise, the song numbers just keep counting up, no matter who they're associated with.

[cmgmyr]

but you can easily do a query to see all of the songs for an artist. i think you would be creating a lot of uneeded work for yourself doing it the way you want

[openrhodes]

but you can easily do a query to see all of the songs for an artist. i think you would be creating a lot of uneeded work for yourself doing it the way you want

 

Agreed, but the issue here is that I have been asked to set it up so that we can upload MP3 files with that specific filename format (XXXXxxx.mp3) with "X" = artist id and "x" = song id.

 

Without that part of the project, you're right that I wouldn't need to deal with this at all.

[cmgmyr]

well thats fine...you can rename the physical mp3 files to whatever you want...but when it comes to the database you shoud stick with the auto numbers

[openrhodes]

So how do you suggest I point to those files if they have a totally different numbering system?  It would mean hard coding every link to every mp3, which isn't possible.    If I can somehow set up the DB to assign those IDs in that format, then the php code will already know where to look for each new mp3 file.

[cmgmyr]

well what I did before is add their user id to the front of the file name. but now I randomly generate a mp3 file name...then I don't have to worry about what the file name is.

[fenway]

cmgmyr is right... name them by UID if you must, but that doesn't impose any other limitations.