Sorting when new entries are added to DB

dackerman

Hello again,

I am loving MySQL more and more each day that I use it. I find that the query system is just amazing, it's almost like English! Anyways, I have this one problem that I can't get around.

My site, http://www.splix.net/music.php, as you can probably see has 3 fields, ID, ARTIST, and SONG. ID is an auto_increment, and the others are just normal VARCHARS. Right now I am sorting by ID, because I can't get around this one problem. (you can see the problem if you scroll down to the bottom)

Everytime I add a new song to the database, I want the SONG to be automatically placed in the correct ID relative to it's alphabetical position. Does that make sense?

For example, right now when I add a new entry, it will always go to the very last ID # and MySQL will sort it alphabetically, but the ID # remains the same. So sometimes I get stuff like this:

ID ARTIST SONG

285 Foo Fighters Lonely As You
286 Foo Fighters Low
287 Foo Fighters Monkey Wrench
288 Foo Fighters My Hero
289 Foo Fighters Next Year
290 Foo Fighters Overdrive
291 Foo Fighters Times Like These
292 Foo Fighters Tired Of You
894 Fountains Of Wayne Denise
895 Fountains Of Wayne It Must Be Summer
896 Fountains Of Wayne Prom Theme

As you can see, they are in alphabetical order, but the ID's do not match up. Is there a way for MySQL to automatically adjust the ID's everytime I add a new entry to the database?

I am using the query:

$query = "SELECT * FROM mp3 ORDER BY artist ASC";

To sort alphabetically.

Any ideas of how I can fix this problem?

Thanks 🆒

LordShryku

MySQL can't do that for you. The point of autoincrement is to assign a unigue id to a record. It doesn't care what order they are assigned. Any sort of ordering should just be done in the orde rby statement. If you wanted to do this, you'll have to write something to go through and reassign those ID's on a regular basis.

dackerman

Any ideas on how to do that? I am very new to this stuff and I barely got through the basics without running into some problems!

Thanks for that though. 😃

LordShryku

Well, I honestly don't think it's worth doing. It defeats the purpose of having order by statements. While I won't write the code for you, I'd say you'd probably want to write a cron job to take everything into an array, [man]sort[/man] it, truncate the table, then reinsert every record.

dackerman

Yeah I guess you're right. I could probably just backup the table and then empty it. Add the new songs manually into the correct alphabetical order and do it that way.

Thanks for all your help.