有没有任何方法可以使用更少的"mysql_num_rows“函数来实现相同的结果？

Question

因此，我目前有一个MySQL表，其中有8列用于收集答复的问题，这些问题看起来有点像这样。每个问题的回答范围从1-5作为一个值。

id _

我现在正试图制作一份摘要表，对每个问题的答复进行细分。

我在count()和GROUP BY的MySQL上玩了一会儿，但没有取得真正的成功。所以我开始用手动的方式，看起来有点像这样：

<?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;