CODEWITHSUNDEEP
phpmysqlmysqli
elektrobober
elektrobober

Reputation: 83

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

Upvotes: 2

Views: 48

Answers (2)

Johannes H.
Johannes H.

Reputation: 6167

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.

Upvotes: 1

sagi
sagi

Reputation: 40481

You can do it directly in the query :

"SELECT CONCAT(q1,' is at ',COUNT(*)) as YourName
 FROM results GROUP BY q1"

This will return them as one field.

Upvotes: 3

Related Questions