Incrementing value by one over a lot of rows
- by Andy Gee
Edit: I think the answer to my question lies in the ability to set user defined variables in MySQL through PHP - the answer by Multifarious has pointed me in this direction
Currently I have a script to cycle over 10M records, it's very slow and it goes like this:
I first get a block of 1000 results in an array similar to this:
$matches[] = array('quality_rank'=>46732, 'db_id'=>5532);
$matches[] = array('quality_rank'=>12324, 'db_id'=>1234);
$matches[] = array('quality_rank'=>45235, 'db_id'=>8345);
$matches[] = array('quality_rank'=>75543, 'db_id'=>2562);
I then cycle through them one by one and update the record
$mult = count($matches)*2;
foreach($matches as $m)
{
$rank++;
$score = (($m[quality_rank] + $rank)/($mult))*100;
$s = "UPDATE `packages_sorted` SET
`price_rank` = '".$rank."',
`deal_score` = '".$score."'
WHERE `db_id` = '".$m[db_id]."' LIMIT 1";
}
It seems like this is a very slow way of doing it but I can't find another way to increment the field price_rank by one each time. Can anyone suggest a better method.
Note: Although I wouldn't usually store this kind of value in a database I really do need on this occasion for comparison search queries later on in the project.
Any help would be kindly appreciated :)
