On Wed, Jan 29, 2003 at 12:10:38PM +0000, the hatter wrote:
> On Wed, 29 Jan 2003, Paul Makepeace wrote:
>
> > So there are a bunch of things "order by rank". I'd like to implement a
> > move up/down in SQL. So say the target was id=20 moving up, I'd like its
> > rank to become 1, and id=10's rank to become 2.
>
> $query = "UPDATE fw SET rank=rank+(-2*(rank-($position+0.5))) WHERE ";
> $query .= "(rank=$position OR rank=$position+1)"
Oh my word. Rewritten with bound variables we have,
UPDATE pageplans
SET rank = rank - 2*rank + 2*:1 + 1
WHERE rank=:1 OR rank=:1+1
(verified OK on PostgreSQL)
Thanks!
For your entertainment, the sayings in my sigs, which are in a MySQL db,
are pulled out with:
select rand() as randorder, quip
from surreal_quip where type = ?
order by randorder limit 1
Cheers,
Paul
--
Paul Makepeace ....................................... http://paulm.com/
"If only life were like the musicals: who would be dancing, then we'd
make love from thorns."
-- http://paulm.com/toys/surrealism/
