Thanks Chris, I've got it all working now. On Dec 1, 12:22 pm, Chris Hope <[email protected]> wrote: > You could do it this way instead: > > UPDATE Stores SET `Order` = `Order` + 1; > UPDATE Stores SET `Order` = 0 ORDER BY `Order` DESC LIMIT 1; > > I would recommend changing the name of the column to something else though. > > 2009/12/1 Super Steve <[email protected]>: > > > > > > > You're right Chris, Ken's query did result in an error #1093. > > > Maybe I should just do it as two separate queries, one to get the MAX > > value and another to do the update. > > > On Dec 1, 11:36 am, Chris Hope <[email protected]> wrote: > >> That won't work in MySQL. You'll get this error message: > > >> #1093 - You can't specify target table 'Stores' for update in FROM clause > > >> And by the way, if you are going to use "Order" as a column name > >> you'll need to enclose it in backticks in the SQL query otherwise it > >> will fail on that count i.e.: this is right (even though the SQL will > >> fail): > > >> UPDATE Stores Set `Order` = (SELECT (MAX(`Order`) + 1) FROM Stores > >> LIMIT 1) WHERE `Order` = 0 > > >> 2009/12/1 Ken Golovin <[email protected]>: > > >> > just use a nested select. not tested, but this should work: > > >> > UPDATE Stores Set Order = (SELECT (MAX(Order) + 1) FROM Stores LIMIT 1) > >> > WHERE Order = 0; > > >> > ----- Original Message ----- > >> > From: "Super Steve" <[email protected]> > >> > To: "NZ PHP Users Group" <[email protected]> > >> > Sent: Tuesday, December 01, 2009 11:29 AM > >> > Subject: [phpug] Re: Rotating Items on a Daily Basis? > > >> > Hmmm... > > >> > UPDATE Stores Set Order = MAX(Order) + 1 WHERE Order = 0; > > >> > Results in Error #1111 - Invalid use of group function > > >> > Note sure why.... > > >> > On Dec 1, 10:13 am, Simon J Welsh <[email protected]> wrote: > >> >> One way would be to add a column to the table, say Order, with values > >> >> from > >> >> 1 to max. Then, using a crontab or some such, UPDATE Stores SET Order = > >> >> Order - 1; UPDATE Stores Set Order = MAX(Order) + 1 WHERE Order = 0; > > >> >> Grab the stores sorted by Order. > > >> >> On 1/12/2009, at 10:09 AM, Super Steve wrote: > > >> >> > I've got a client that wants to display on his website a list of > >> >> > retail stores that stock his products. To facilitate this we have all > >> >> > of the store details in a MySQL database. > > >> >> > But rather than display all of the stores in alphabetical or location > >> >> > order, my client wants to rotate the stores so each one gets a turn at > >> >> > being at the top. The idea is that at the beginning of each day the > >> >> > store that was on the top yesterday will move to the bottom and all > >> >> > the other stores move up one. For the rest of the day the stores are > >> >> > displayed in this order, i.e they maintain their positions for the > >> >> > whole day. Then next day the top one goes to the bottom and they all > >> >> > move up one. > > >> >> > Does anyone have any idea on how I can achieve this? I'm not looking > >> >> > for actual PHP code, just pseudo code to get me on the right track. > > >> >> > -- > >> >> > NZ PHP Users Group:http://groups.google.com/group/nzphpug > >> >> > To post, send email to [email protected] > >> >> > To unsubscribe, send email to > >> >> > [email protected] > > >> >> --- > >> >> Simon Welsh > >> >> Admin ofhttp://simon.geek.nz/ > > >> >> Who said Microsoft never created a bug-free program? The blue screen > >> >> never, ever crashes! > > >> >>http://www.thinkgeek.com/brain/gimme.cgi?wid=81d520e5e > > >> > -- > >> > NZ PHP Users Group:http://groups.google.com/group/nzphpug > >> > To post, send email to [email protected] > >> > To unsubscribe, send email to > >> > [email protected] > > >> > -- > >> > NZ PHP Users Group:http://groups.google.com/group/nzphpug > >> > To post, send email to [email protected] > >> > To unsubscribe, send email to > >> > [email protected] > > >> -- > >> Chris Hope > >> The Electric Toolbox Ltd > > >> Email: [email protected] > >> Web:www.electrictoolbox.com > >> Phone: +64 9 522 9531 > >> Mobile: +64 21 866 529 > > > -- > > NZ PHP Users Group:http://groups.google.com/group/nzphpug > > To post, send email to [email protected] > > To unsubscribe, send email to > > [email protected] > > -- > Chris Hope > The Electric Toolbox Ltd > > Email: [email protected] > Web:www.electrictoolbox.com > Phone: +64 9 522 9531 > Mobile: +64 21 866 529
-- NZ PHP Users Group: http://groups.google.com/group/nzphpug To post, send email to [email protected] To unsubscribe, send email to [email protected]
