CODEWITHSUNDEEP
phpmysqlwhile-loop
blogo
blogo

Reputation: 329

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:

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.

Upvotes: 1

Views: 1150

Answers (1)

FirstOne
FirstOne

Reputation: 6217

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.

Upvotes: 6

Related Questions