MySQL update inside PHP while loop

Question

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:

require_once('db-connect.php');

//---RETRIEVE FROM DB
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;

  //---UPDATE DB
  $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));
mysql_free_result($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.

`UPDATE tblposts SET rating=views+likes+tagcount+dcount WHERE status = 'live'` — FirstOne, Jun 30 '16 at 01:08
You can also involve a limit clause if you want. Often I will do an `order by someDate desc limit 1000` having also set another column to bProcessed=1 ... so the where clause could include a `bProcessed=0` part — Drew, Jun 30 '16 at 01:16
@FirstOne: Thanks. I should probably have mentioned that the calculation of the rating will include data not from the database. Sorry for the oversight. I updated the question to include the variable $v5. — blogo, Jun 30 '16 at 01:25

score 6 · Accepted Answer · edited May 23 '17 at 10:28

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.

Since you are using mysql_*, I'm not even going deep about security and that stuff. I'll just leave this here for reference:

And take care when using do..while like that. Since it only checks after running at least once, you might get some notice/warning in case there are no results.

Thanks for your time. That's considerably more elegant than what I came up with. — blogo, Jun 30 '16 at 01:55

MySQL update inside PHP while loop

1 Answers1