代码之家 › 专栏 › 技术社区 › Ibrahim Azhar Armar

如何改进此代码以使我的代码更少地访问数据库?

mysql php

1

Ibrahim Azhar Armar · 技术社区 · 15 年前

我已经编写了一个代码,在这个代码中我经常访问数据库,这有点尴尬,我使用该代码来计算评论表中批准、挂起和垃圾邮件的数量。

这是我的密码。

 $query_approved = "SELECT COUNT(*) as approved FROM comments WHERE approve = '1'";
 $result_approved = mysql_query($query_approved);
 $row_approved = mysql_fetch_array($result_approved);
 $query_unapproved = "SELECT COUNT(*) as unapproved FROM comments WHERE approve = '0'";
 $result_unapproved = mysql_query($query_unapproved);
 $row_unapproved = mysql_fetch_array($result_unapproved);
 $query_spam = "SELECT COUNT(*) as spam FROM comments WHERE spam = '1'";
 $result_spam = mysql_query($query_spam);
 $row_spam = mysql_fetch_array($result_spam);

虽然这个代码很好用,但看起来很难看。还有什么可以改进的吗?

4 回复 | 直到 15 年前

1

2

OMG Ponies 15 年前

用途:

SELECT SUM(CASE WHEN c.approved = '1' THEN 1 ELSE 0 END) AS cnt_approved,
       SUM(CASE WHEN c.approved = '0' THEN 1 ELSE 0 END) AS cnt_unapproved,
       SUM(CASE WHEN c.spam = '1' THEN 1 ELSE 0 END) AS cnt_spam
  FROM COMMENTS c

2

1

egrunin 15 年前

您可以组合:

SELECT SUM(approve) as approved, SUM(spam) AS spam, 
    SUM(approved) - COUNT(*) as unapproved 
FROM comments

看看前三个答案(包括这个),我倾向于凯尔西的方法作为最可行的方法。

3

0

Maulik Vora 15 年前

您可以在一个调用中优化前两个调用

$query_approved = "SELECT COUNT(*) as totalCount , approve as status FROM comments group by approve";
 $result_approved = mysql_query($query_approved);
 $rows = mysql_fetch_array($result_approved);
foreach($rows as $row)
{
     if($row['status'] == '1')
     {
           $row_approved = $row['totalCount'];
     }
     elseif ($row['status'] == '0')
     {
           $row_unapproved = $row['totalCount'];
     }
}

 $query_spam = "SELECT COUNT(*) as spam FROM comments WHERE spam = '1'";
 $result_spam = mysql_query($query_spam);
 $row_spam = mysql_fetch_array($result_spam);

4

0

Coach John 15 年前

更有效的单个查询可能是:

$SQL = "SELECT approve, spam, count(*) as cnt FROM comments GROUP BY approve, spam";
$result_approved = mysql_query($SQL );
$rows= mysql_fetch_row($result_approved);

然后,您可以在$rows之前…

foreach ( $rows as $row ) {
   $row[0] is the approved code (1 or 0)
   $row[1] is the spam flag (1 or 0)
   $row[2] is the count for this criteria
}

您将以4行结束,每行一个表示已批准,即垃圾邮件,而不是垃圾邮件,未批准即垃圾邮件,而不是垃圾邮件。