I want to retrieve 8,000 records from a MySQL database, perform a calculation to give each record a rating, then update the database with the rating.
Here's what I have:
//---RETRIEVE FROM DB
$query_r1 = "SELECT * FROM tblposts WHERE status = 'live'";
$r1 = mysql_query($query_r1, $lg) or die(mysql_error());
$row_r1 = mysql_fetch_assoc($r1);
$id = $row_r1['id'];
$v1 = $row_r1['views'];
$v2 = $row_r1['likes'];
$v3 = $row_r1['tagcount'];
$v4 = $row_r1['dcount'];
$v5 = $_POST['content_rating'];
$rating = $v1 + $v2 + $v3 + $v4 + $v5;
$updateSQL = "UPDATE tblposts SET rating='$rating' WHERE id = '$id'";
$Result = mysql_query($updateSQL, $lg) or die(mysql_error());
} while ($row_r1 = mysql_fetch_assoc($r1));
How about this for an insight?
UPDATE tblposts SET rating=views+likes+tagcount+dcount+$value WHERE status = 'live'
This means: "For each status that is currently
live, make the column
rating be the sum of
dcount and a php variable. Pretty much a 'translation' of your php code".
This removes the need for any selects and loops. Let the DB do the work for you.
Sidenote, since you are using mysql_*, I'm not even going deep about security and that stuff. I'll just leave this here for future reference: