> Steve McGill wrote:
> > I have a situation where I am doing lots of complex sorting on records
that
> > already exist in a MySQL table. The table has a 'sort_order' field which
> > means I can do a simple ORDER BY sort_order to keep it nice and quick on
> > SELECT, it's only UPDATE which is slow.
> >
> > If I change the order that I want 10000 rows to be displayed, I am
currently
> > making 10000 SQL queries such as:
> >
> > UPDATE products SET sort_order=0 WHERE id='5';
> > UPDATE products SET sort_order=1 WHERE id='2';
> > UPDATE products SET sort_order=2 WHERE id='32';
> > and so on.
> >
> > Obviously I'd love to be able to put all of this into one query.
> >
> > Is there some way I can combine this with IN() ?
> > UPDATE products set sort_order=**** WHERE id IN('5','2','32');
> >
> > Many thanks in advance for any help / advice.
>
> What is the exact operation you're doing? Is there any possibility you
> can do "UPDATE products SET sort_order=sort_order+1 WHERE sort_order >
> 30" for e.g. - as in you insert an 'item' at spot 30 and want all the
> rest below it to be bumped up one.
That's something I've done in the past, and it works well. But indeed, I am
letting the users completely change the order manually, using some
javascript, and letting them do it all in one go.
> You can certainly use IN(), but there is a (quite high) limit on how
> long the query string can be. It's pretty ridiculously high actually,
> but something to consider.
Could you help me with the syntax for that? I said 10,000 as an example, but
it's more like 500. :-)
> Personally, I'd look at how you're doing things - see if there isn't an
> alternative way to do it like outlined above. I'm assuming these aren't
> just some precalculated ordering, they're manually ordered for some
> reason? If it's not 'we want to manually order them' - In a products
> database, I would think you could just have an "ORDER BY product_group,
> product_name" or some such.
They already get a choice between manual ordering, or using mysql to sort
using 'order by'. Sometimes they need specific control.
Best wishes,
Steve
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
