elektrobober elektrobober - 6 months ago 18
MySQL Question

Extraction data from MySQL table using PHP

i'm trying to extract data from mysql. I have some query looking like this

SELECT q1, COUNT(*) FROM results GROUP BY q1
and as result I'm expecting something like this:

q1 COUNT(*)
0 7
1 1
2 4
3 1


It works in phpMyAdmin workflow, but if I try to repeat this query from my pagename.php file I'm failed.

$q1_ans = mysqli_query($link, "SELECT q1, COUNT(*) FROM results GROUP BY q1");
$q1_arr = mysqli_fetch_assoc($q1_ans);
foreach($q1_arr as $key => $value) {
echo "<br>$key is at $value";
}


And as result I have

q1 is at 0
COUNT(*) is at 7

How can I get array looking like this??

0 is at 7
1 is at 1
2 is at 4
3 is at 1

Answer

mysqli_fetch_assoc() fetches ONE row from the database. You have to repeat it until there is no more data:

$q1_ans = mysqli_query($link, "SELECT q1, COUNT(*) FROM results GROUP BY q1");
while ($q1_arr = mysqli_fetch_assoc($q1_ans)) {
    foreach($q1_arr as $key => $value) {
        echo "<br>$key is at $value";
    }
}

This fixes the issue that you only get one result. To fix your output, see @sagi's answer.

Comments