hi
imagine that i have an UPDATE like this :
"UPDATE table SET item_rank='".n."' WHERE cat_id='".$cat_id."'"
that will update all the rank column values to n
is it possible that the value n gets incremented with each row that is affected ?
the reason i'm asking is that at the moment i have do to a select to get a list of the id's of items in that category - then have a loop that updates each item in turn, then increment n and do the next one - this works fine but it's not very elegant
thanks
If MySQL, I think you could do something like the following (untested):
$num = 1;
mysql_query('SET @num = ' . $num);
mysql_query("UPDATE table set item_rank = (SELECT @num := @num + 1) WHERE cat_id = '" . $cat_id . "'");
yessss !!!
that's perfect !
I had to initialize $num = 0; at the start otherwise it started numbering from 2 upwards - but apart from that it's great
many thanks
erm, i have another question :
is it possible to include something in that query that would update the rows in a specific order ?
the reason i'm asking this is that the items are already ordered by rank but if the rank order gets messed up (eg: 1,2,2,4,5,6,7 etc) I can detect that but i need to re-order the items using the existing order as a base
if it's not possible then i would need to do a SELECT anyway and order it by the existing order then do an update on each one
You can add an ORDER BY clause at the end of the query (just like you would for a SELECT).
aha - as simple as that, eh ?!
I wouldn't have thought of doing that
thanks 