blogo blogo - 6 months ago 31
SQL Question

MySQL update inside PHP while loop

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:


mysql_select_db($database_lg, $lg);
$query_r1 = "SELECT * FROM tblposts WHERE status = 'live'";
$r1 = mysql_query($query_r1, $lg) or die(mysql_error());
$row_r1 = mysql_fetch_assoc($r1);

do {

$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'";
mysql_select_db($database_lg, $lg);
$Result = mysql_query($updateSQL, $lg) or die(mysql_error());

} while ($row_r1 = mysql_fetch_assoc($r1));

Is this the way to do it? It seems to me I could make my server go up in smoke by doing 8,000 updates this way, but I don't know of a more elegant solution. Any insight appreciated.

Note: I realize that mysql_* functions are deprecated. On the site in question I have to live with them for a while longer.


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 views, likes, tagcount and 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: