在mysql / php中编码多个计数查询

I am coding the following, Im still very new to php so my coding is a bit rubbish, is there a better way of doing the following, ideally putting into one query or something, as this is the only way I know how to do it :-S . Thanks :-)

我编写以下代码，我仍然是非常新的php所以我的编码有点垃圾，有更好的方法做以下，理想情况下放入一个查询或东西，因为这是我知道如何做的唯一方法：-S。谢谢：-）

<?php
$query = ("SELECT COUNT(receivegasoilmailinglist) FROM     hqfjt_chronoforms_data_addupdatelead WHERE      receivegasoilmailinglist='yes'"); 
$result = mysql_query($query) or die(mysql_error()); 

$row = mysql_fetch_row($result); 
echo '<h1> gasoil :';
echo $row[0];  
echo '</h1>';
 ?>

<?php
$query2 = ("SELECT COUNT(receivedervmailinglist) FROM hqfjt_chronoforms_data_addupdatelead WHERE receivedervmailinglist='yes'"); 
 $result2 = mysql_query($query2) or die(mysql_error()); 

 $row2 = mysql_fetch_row($result2); 
 echo '<h1> derv :';
 echo $row2[0];  
 echo '</h1>';
 ?>

<?php
 $query3 = ("SELECT COUNT(receivekeromailinglist) FROM hqfjt_chronoforms_data_addupdatelead WHERE receivekeromailinglist='yes'"); 
 $result3 = mysql_query($query3) or die(mysql_error()); 

$row3 = mysql_fetch_row($result3); 
echo '<h1> kero :';
echo $row3[0];  
echo '</h1>';
?>

3 个解决方案

#1

If (and only if) either of these is true:

如果（且仅当）其中任何一个为真：

most of the rows in hqfjt_chronoforms_data_addupdatelead want some sort of newsletter
hqfjt_chronoforms_data_addupdatelead中的大多数行都需要某种新闻通讯
there are no indices on the receive*mailinglist fields
receive * mailinglist字段中没有索引

you can save a lot of DB muscle by doing

你可以通过这样做来节省大量的DB肌肉

SELECT 
  sum(if(receivegasoilmailinglist='yes',1,0)) AS gasoil,
  sum(if(receivedervmailinglist='yes',1,0)) AS derv,
  sum(if(receivekeromailinglist='yes',1,0)) AS kero
FROM hqfjt_chronoforms_data_addupdatelead;

as this will traverse the table only once.

因为这只会遍历表格一次。

For the rest of the job you use ofcourse

对于剩下的工作，你使用的是课程

$sql="... as above ...";
$qry = mysql_query($sql) or die("Query failed: <br>SQL=$sql<br>Error=".mysql_error());
$qry = mysql_fetch_row($qry);
echo "<h1> gasoil: ".$qry[0]."</h1>";
echo "<h1> derv: ".$qry[1]."</h1>";
echo "<h1> kero: ".$qry[2]."</h1>";

#2

You could use a UNION and set a type in each query then loop the three records all at once. UNION collates results together but must feature the same exact output structure.

您可以使用UNION并在每个查询中设置一个类型，然后一次循环三个记录。 UNION将结果整理在一起，但必须具有相同的输出结构。

$query = 
"SELECT 'gasoil' AS counttype, COUNT(receivegasoilmailinglist) AS countdata FROM     hqfjt_chronoforms_data_addupdatelead WHERE      receivegasoilmailinglist='yes'
UNION
SELECT 'derv' AS counttype, COUNT(receivedervmailinglist) AS countdata FROM hqfjt_chronoforms_data_addupdatelead WHERE receivedervmailinglist='yes'
UNION
SELECT 'kero' AS counttype, COUNT(receivekeromailinglist) AS countdata FROM hqfjt_chronoforms_data_addupdatelead WHERE receivekeromailinglist='yes'";
$result = mysql_query($query) or die(mysql_error()); 

while($row = mysql_fetch_assoc($result)){
    echo '<h1> '.$row['counttype'].' :';
    echo $row['countdata'];  
    echo '</h1>';
}

And also, my code is much shorter for the exact same processing! :)

而且，对于完全相同的处理，我的代码要短得多！ :)

#3

I would do a union personally.

我会亲自去做一个工会。

$query = "(SELECT COUNT(receivegasoilmailinglist) AS gasoil FROM hqfjt_chronoforms_data_addupdatelead WHERE receivegasoilmailinglist='yes') UNION (SELECT COUNT(receivedervmailinglist) AS derv FROM hqfjt_chronoforms_data_addupdatelead WHERE receivedervmailinglist='yes') UNION (SELECT COUNT(receivekeromailinglist) AS kero FROM hqfjt_chronoforms_data_addupdatelead WHERE receivekeromailinglist='yes')";

and since others have answered I figure you can tell how to loop through the results.

而且由于其他人已经回答了我的数字，你可以告诉我们如何循环结果。

If you are unsure about unions, you can run queries like this so long as each expected result datatype is the same. In this case, they're all counts, so it should work well.

如果您不确定联合会，只要每个预期的结果数据类型相同，您就可以运行这样的查询。在这种情况下，它们都是重要的，所以它应该运作良好。

#1