php - 有没有办法使用更少的 "mysql_num_rows"函数并达到相同的结果？

Question

所以我目前有一个包含 8 列问题的 MySQL 表，看起来有点像用于收集响应的表。每个问题都可以有一个范围从 1 到 5 的响应值。:

id | q1-q8 | timestamp

我现在正在尝试创建一个摘要表，分割每​​个问题的回答。

我在 MySQL 中尝试了一些 count() 和 GROUP BY 但没有真正成功。所以我开始采用手动方式，看起来有点像这样:

<?php
//connect to db


$count1_1 = mysql_num_rows(mysql_query("SELECT q1 FROM table WHERE q1=1"));
$count1_2 = mysql_num_rows(mysql_query("SELECT q1 FROM table WHERE q1=2"));
$count1_3 = mysql_num_rows(mysql_query("SELECT q1 FROM table WHERE q1=3"));
$count1_4 = mysql_num_rows(mysql_query("SELECT q1 FROM table WHERE q1=4"));
$count1_5 = mysql_num_rows(mysql_query("SELECT q1 FROM table WHERE q1=5"));

$count2_1 = mysql_num_rows(mysql_query("SELECT q2 FROM table WHERE q2=1"));
$count2_2 = mysql_num_rows(mysql_query("SELECT q2 FROM table WHERE q2=2"));
$count2_3 = mysql_num_rows(mysql_query("SELECT q2 FROM table WHERE q2=3"));
$count2_4 = mysql_num_rows(mysql_query("SELECT q2 FROM table WHERE q2=4"));
$count2_5 = mysql_num_rows(mysql_query("SELECT q2 FROM table WHERE q2=5"));

$count3_1 = mysql_num_rows(mysql_query("SELECT q3 FROM table WHERE q3=1"));
$count3_2 = mysql_num_rows(mysql_query("SELECT q3 FROM table WHERE q3=2"));
$count3_3 = mysql_num_rows(mysql_query("SELECT q3 FROM table WHERE q3=3"));
$count3_4 = mysql_num_rows(mysql_query("SELECT q3 FROM table WHERE q3=4"));
$count3_5 = mysql_num_rows(mysql_query("SELECT q3 FROM table WHERE q3=5"));

$count4_1 = mysql_num_rows(mysql_query("SELECT q4 FROM table WHERE q4=1"));
$count4_2 = mysql_num_rows(mysql_query("SELECT q4 FROM table WHERE q4=2"));
$count4_3 = mysql_num_rows(mysql_query("SELECT q4 FROM table WHERE q4=3"));
$count4_4 = mysql_num_rows(mysql_query("SELECT q4 FROM table WHERE q4=4"));
$count4_5 = mysql_num_rows(mysql_query("SELECT q4 FROM table WHERE q4=5"));

$count5_1 = mysql_num_rows(mysql_query("SELECT q5 FROM table WHERE q5=1"));
$count5_2 = mysql_num_rows(mysql_query("SELECT q5 FROM table WHERE q5=2"));
$count5_3 = mysql_num_rows(mysql_query("SELECT q5 FROM table WHERE q5=3"));
$count5_4 = mysql_num_rows(mysql_query("SELECT q5 FROM table WHERE q5=4"));
$count5_5 = mysql_num_rows(mysql_query("SELECT q5 FROM table WHERE q5=5"));

$count6_1 = mysql_num_rows(mysql_query("SELECT q6 FROM table WHERE q6=1"));
$count6_2 = mysql_num_rows(mysql_query("SELECT q6 FROM table WHERE q6=2"));
$count6_3 = mysql_num_rows(mysql_query("SELECT q6 FROM table WHERE q6=3"));
$count6_4 = mysql_num_rows(mysql_query("SELECT q6 FROM table WHERE q6=4"));
$count6_5 = mysql_num_rows(mysql_query("SELECT q6 FROM table WHERE q6=5"));

$count7_1 = mysql_num_rows(mysql_query("SELECT q7 FROM table WHERE q7=1"));
$count7_2 = mysql_num_rows(mysql_query("SELECT q7 FROM table WHERE q7=2"));
$count7_3 = mysql_num_rows(mysql_query("SELECT q7 FROM table WHERE q7=3"));
$count7_4 = mysql_num_rows(mysql_query("SELECT q7 FROM table WHERE q7=4"));
$count7_5 = mysql_num_rows(mysql_query("SELECT q7 FROM table WHERE q7=5"));

$count8_1 = mysql_num_rows(mysql_query("SELECT q8 FROM table WHERE q8=1"));
$count8_2 = mysql_num_rows(mysql_query("SELECT q8 FROM table WHERE q8=2"));
$count8_3 = mysql_num_rows(mysql_query("SELECT q8 FROM table WHERE q8=3"));
$count8_4 = mysql_num_rows(mysql_query("SELECT q8 FROM table WHERE q8=4"));
$count8_5 = mysql_num_rows(mysql_query("SELECT q8 FROM table WHERE q8=5"));



echo "Q1_1 $count1_1 <br>";
echo "Q1_2 $count1_2 <br>";
echo "Q1_3 $count1_3 <br>";
echo "Q1_4 $count1_4 <br>";
echo "Q1_5 $count1_5 <br>";
echo "<hr />";
echo "Q2_1 $count2_1 <br>";
echo "Q2_2 $count2_2 <br>";
echo "Q2_3 $count2_3 <br>";
echo "Q2_4 $count2_4 <br>";
echo "Q2_5 $count2_5 <br>";
echo "<hr />";
echo "Q3_1 $count3_1 <br>";
echo "Q3_2 $count3_2 <br>";
echo "Q3_3 $count3_3 <br>";
echo "Q3_4 $count3_4 <br>";
echo "Q3_5 $count3_5 <br>";
echo "<hr />";
echo "Q4_1 $count4_1 <br>";
echo "Q4_2 $count4_2 <br>";
echo "Q4_3 $count4_3 <br>";
echo "Q4_4 $count4_4 <br>";
echo "Q4_5 $count4_5 <br>";
echo "<hr />";
echo "Q6_1 $count6_1 <br>";
echo "Q6_2 $count6_2 <br>";
echo "Q6_3 $count6_3 <br>";
echo "Q6_4 $count6_4 <br>";
echo "Q6_5 $count6_5 <br>";
echo "<hr />";
echo "Q7_1 $count7_1 <br>";
echo "Q7_2 $count7_2 <br>";
echo "Q7_3 $count7_3 <br>";
echo "Q7_4 $count7_4 <br>";
echo "Q7_5 $count7_5 <br>";
echo "<hr />";
echo "Q8_1 $count8_1 <br>";
echo "Q8_2 $count8_2 <br>";
echo "Q8_3 $count8_3 <br>";
echo "Q8_4 $count8_4 <br>";
echo "Q8_5 $count8_5 <br>";
echo "<hr />";
    ?>

正如您所看到的，这几乎是 100 行重复代码，很可能可以做得更好。然而，我对如何最好地做到这一点感到有点困惑，并且很想听听您的想法。

Answer 1

嗯？您应该使用数据库来实现此逻辑。在数据库中进行计数!

因此，您的前五个查询可以替换为:

SELECT SUM(q1 = 1) as q1, SUM(q1 = 2) as q2, SUM(q1 = 3) as q3,
       SUM(q1 = 4) as q4, SUM(q1 = 5) as q5
FROM table ;

或者，如果您愿意的话:

SELECT q1, COUNT(*) as cnt
FROM table
GROUP BY q1;