Welcome to WebmasterWorld Guest from

Forum Moderators: open

Message Too Old, No Replies

Custom Sort of mySQL Rows and Trigger to Update

11:05 pm on Jul 9, 2008 (gmt 0)

Junior Member

10+ Year Member

joined:Jan 23, 2007
posts: 48
votes: 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.


Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members