homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

Custom Sort of mySQL Rows and Trigger to Update

5+ Year Member

Msg#: 3694934 posted 11:05 pm on Jul 9, 2008 (gmt 0)

For simplicity's sake, I have a mySQL table like this:

Flavor Color

Vanilla White
Chocolate Brown
Strawberry Pink

Right now, if I run a select statement, my only options for "order by" are by flavor or color. What I really need to be able to do is order by favorite (nothing to do with alphabetical order or insertion order).

So, I will add a "favorite" column whose values will be 1, 2, 3 based on my favorite flavor. However, depending on the day and my mood, my favorite can change. So if yesterday my favorite was Vanilla and today my favorite is chocolate, I'd like to be able to update the favorite column for chocolate to 1 and then set all other favorite values = favorite + 1.

Is the best way to accomplish this to create a trigger on insert or update to the favorite column?

Should the favorite column be auto increment, or is that not necessary since I will have the trigger update the rest of the values as favorite + 1?

Thanks for your help.


Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved