Welcome to WebmasterWorld Guest from 54.145.39.186

Forum Moderators: open

Message Too Old, No Replies

Custom Sort of mySQL Rows and Trigger to Update

     

suga

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

5+ Year Member



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.

 

Featured Threads

Hot Threads This Week

Hot Threads This Month