有没有更有效的方法来做到这一点？

Question

function countBrand($brand_id, $brand_name) {
    $sql = "SELECT brand FROM coupons WHERE expire >= CURRENT_DATE AND brand='".$brand_id."'";
    $result = mysql_query($sql);
    $row = mysql_fetch_array($result);
    echo '<li>'.$brand_name.'</li>';
}

function brandCount() {
    $sql = "SELECT DISTINCT brand,brand_id,brand_name FROM coupons,coupons_brand WHERE brand=brand_id AND expire >= CURRENT_DATE ORDER BY brand_name";
    $result = mysql_query($sql);
    $row = mysql_fetch_array($result);
    $num = mysql_num_rows($result);

    echo '<h3>'.$num.' Brands</h3>';

    echo '<ul>';

    $i = 0;
    while ($i < $num) {
        $brand_id = mysql_result($result, $i, "brand_id");
        $brand_name = mysql_result($result, $i, "brand_name");
        countBrand($brand_id, $brand_name);
        $i++;
    }

    echo '</ul>';
}

它工作得很完美，给了我我想要的结果。我并不像我希望的那样善于使用sql语句。有没有一种方法可以让我做的更有效，它看起来很慢。

基本上，它计算有多少品牌有优惠券，然后计算每个品牌有多少优惠券。

在同一页面上，我也对类别执行此操作。有几千个类别，可能有两万张优惠券。

Answer 1

您可以进行一些php优化，但与在正确的列上为mysql添加索引相比，这些优化可能不会节省您太多时间。

我在下面评论了一些php的优化，也许你会感兴趣。

考虑到函数的简单性质，没有必要有两个函数，这将节省调用countBrand()的时间成本(尽管这是非常小的节省时间)

   function countBrand($brand_id, $brand_name) {
        $sql = "SELECT brand FROM coupons WHERE expire >= CURRENT_DATE AND brand='".$brand_id."'";
        $result = mysql_query($sql) || die (mysql_error()); // always check for errors

        list($brand) = mysql_fetch_row($result);

// fetch row, returns a more concise array then mysql_fetch_array
//        $row = mysql_fetch_array($result);

        // use commas rather then . when concatenating echo statements
        // dots force concatenation before output
        echo '<li>',$brand,'</li>'; 

    }

    function brandCount() {
        $sql = "SELECT DISTINCT brand,brand_id,brand_name FROM coupons,coupons_brand WHERE brand=brand_id AND expire >= CURRENT_DATE ORDER BY brand_name";
        $result = mysql_query($sql) || die(mysql_error()); // always check for errors

    //    $row = mysql_fetch_array($result); // not sure why this is needed
        $num = mysql_num_rows($result); 

        // use commas rather then . when concatenating echo statements
        // dots force concatenation before output
        echo '<h3>',$num,' Brands</h3>';

        echo '<ul>';

      // fetch all cols at once, rather then using lots of separate calls to mysql_result
      // use mysql_fetch_row as it returns just the ordered values (vs mysql_fetch_assoc, and mysql_fetch_array)
      // 
       while(list($brand, $brand_id, $brand_name) == mysql_fetch_row($result)) {
            countBrand($brand_id, $brand_name);
       }

      // replaced with while loop above
      //  $i = 0;
      //  while ($i < $num) {
      //      $brand_id = mysql_result($result, $i, "brand_id");
      //      $brand_name = mysql_result($result, $i, "brand_name");
     //       countBrand($brand_id, $brand_name);
      //      $i++;
        }

        echo '</ul>';
    }

这些增强只会给你带来很小的速度提升，如果你少调用数据库，你将获得最大的速度提升。

目前，您选择每个品牌，然后返回并逐个计算每个品牌，而不知道表的结构。这个sql对我来说很难编写，所以这只是一个猜测，但它应该会为您指明正确的方向

SELECT brand, brand_id, brand_name, COUNT(*) as brandcount
FROM coupons
JOIN coupons_brand ON brand=brand_id 
WHERE expire >= CURRENT_DATE 
GROUP BY brand, brand_id, brand_name
ORDER BY brand_name

Answer 2

mysql_fetch_array($result，MYSQL_ASSOC)；

SELECT SQL_CACHE brand FROM

Profiling query