douzang7928
2016-06-30 01:07 阅读 73
已采纳

PHP内部的MySQL更新循环

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.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

1条回答 默认 最新

  • 已采纳
    drl6054 drl6054 2016-06-30 01:20

    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.
    点赞 评论 复制链接分享