Extraction data from MySQL table using PHP

Question

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

sagi · Accepted Answer · 2016-06-05 11:56:27Z

3

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.

answered Jun 5, 2016 at 11:56

sagi

40.6k7 gold badges66 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

elektrobober Over a year ago

Thank you for your contribution

Johannes H. · Accepted Answer · 2016-06-05 11:57:20Z

1

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.

answered Jun 5, 2016 at 11:57

Johannes H.

6,1671 gold badge23 silver badges44 bronze badges

1 Comment

elektrobober Over a year ago

thank you for your help, getting only first row was my big trouble

Collectives™ on Stack Overflow

Extraction data from MySQL table using PHP

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related