php - 我怎样才能改进这个 PHP MySQL 调用？

Question

我正在尝试更新较旧的 MySQL PHP 方法调用，但最终调用相同的 SELECT 调用两次以获得相同的结果。有更好的方法吗？

原文如下:

function updateProbabilities()
{
    // first update the word count of each category
    $rs = $this->con->select("SELECT category_id, SUM(count) AS total FROM nb_wordfreqs WHERE 1 GROUP BY category_id");
    $total_words = 0;
    while (!$rs->EOF()) {
        $total_words += $rs->f('total');
        $rs->moveNext();
    }
    $rs->moveStart();
    if ($total_words == 0) {
        $this->con->execute("UPDATE nb_categories SET word_count=0, probability=0 WHERE 1");
        return true;
    }
    while (!$rs->EOF()) {
        $proba = $rs->f('total')/$total_words;
        $this->con->execute("UPDATE nb_categories SET word_count=".(int)$rs->f('total').",
                                    probability=".$proba."
                                    WHERE category_id = '".$rs->f('category_id')."'");
        $rs->moveNext();
    }
    return true;
}

这是我的版本:

function updateProbabilities() {

    $sql = "SELECT category_id, SUM(count) AS total FROM nb_wordfreqs WHERE 1 GROUP BY category_id";
    $res = mysql_query($sql) or die (mysql_error());

    $total_words = 0;

    while ($row = mysql_fetch_array($res, MYSQL_ASSOC)) {
        $total_words += $row['total'];
    }

    if ($total_words >= 1) {

        $sql = "SELECT category_id, SUM(count) AS total FROM nb_wordfreqs WHERE 1 GROUP BY category_id";
        $res = mysql_query($sql) or die (mysql_error());

        while ($row = mysql_fetch_array($res, MYSQL_ASSOC)) {

            $proba = $row['total']/$total_words;

            $count = (int)$row['total'];

            $sql = "UPDATE nb_categories SET word_count=".$count.",
                    probability=".$proba."
                    WHERE category_id = '".$row['category_id']."'";

            mysql_query($sql) or die (mysql_error());
        }

    }
    else {

        $sql = "UPDATE nb_categories SET word_count=0, probability=0 WHERE 1";
        mysql_query($sql) or die (mysql_error());
    }

    return true;
}

您认为有什么方法可以避免第二次重复的 SELECT 调用吗？

Answer 1

第一次运行查询时，将其添加到 while 循环中以存储结果:

$rowarray[] = $row; // added to prevent running it twice

然后第二次出现时，替换它:

$sql = "SELECT category_id, SUM(count) AS total FROM nb_wordfreqs WHERE 1 GROUP BY category_id";
$res = mysql_query($sql) or die (mysql_error());
while ($row = mysql_fetch_array($res, MYSQL_ASSOC)) {

这样:

foreach ($rowarray as $row) {

另外:远离 mysql_ 函数，因为它们已被弃用，并将很快从 PHP 中删除。更改为 PDO 或 MySQLi。您已经在更新代码，不妨也进行更改。