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
– FirstOne, Commented Jun 30, 2016 at 1: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
– Drew, Commented Jun 30, 2016 at 1: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
– blogo, Commented Jun 30, 2016 at 1:25

Community · Accepted Answer · 2017-05-23 10:28:48Z

6

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.

edited May 23, 2017 at 10:28

CommunityBot

11 silver badge

answered Jun 30, 2016 at 1:20

FirstOne

6,2857 gold badges28 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

blogo Over a year ago

Thanks for your time. That's considerably more elegant than what I came up with.

Collectives™ on Stack Overflow

MySQL update inside PHP while loop

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related