Return SQL query in one row, fetch with PHP [duplicate]

Question

With my SQL query, I want to counter up all the distinct values in a certain column. This works fine:

SELECT ans, COUNT(*) AS ans_num
FROM table_123
GROUP BY ans;

This returns (depending on how many distinct values) something like this, if there were three distinct values in the table:

+------+---------+
| ans  | ans_num |
+------+---------+
| 0    |      15 |
| 1    |       2 |
| 3    |      11 |
+------+---------+

I'm using mysqli_fetch_array to return the query values in PHP, but I would have to loop through each row in order to get all the results.

QUESTION:

How can I write an SQL query that would return all the distinct values in one array (associative), so I would only have to mysqli_fetch_array only once?

CLARIFICATION:

I was wondering if there's a way in the query to return the results of the query as a single row, not fetching all rows with PHP

you alredy get an associative array but it is inside a normal array [[["ans"=>0], ["ans_num" => 15]], [["ans"=>1], ["ans_num" => 2]], [["ans"=>3], ["ans_num" => 11]]]... — sailingthoms
– sailingthoms, Commented Jul 1, 2014 at 19:22
Why don't you use distinct in mysql query SELECT DISTINCT ans, COUNT(*) AS ans_num FROM table_123 GROUP BY category; USING mysqli_fetch_all — Abhineet Verma
– Abhineet Verma, Commented Jul 1, 2014 at 19:24
I was wondering if there's a way in the query to return the results of the query as a single row, not fetching all rows with PHP. — fhbi
– fhbi, Commented Jul 1, 2014 at 20:47

Angus Atkins-Trimnell · Accepted Answer · 2014-07-01 21:44:18Z

0

I think this is only possible if you have a determinate number of values for "ans". Something like this should work in that case.

    SELECT SUM(Zero) "0", Sum(One) "1", Sum(Two) "2", Sum(Three) "3" FROM
        (SELECT IF(ans=0, 1, 0) Zero, IF(ans=1, 1, 0) One, IF(ans=2, 1, 0) Two, IF(ans=3, 1, 0) Three
        FROM table_123) TEMP;

This should get you a result like this:

    +---+----+----+----+
    | 0 |  1 |  2 |  3 |
    +---+----+----+----+
    | 15|   2|   0|  11|
    +---+----+----+----+

If the number of possible values is too large for this approach, you may want to look into pivot tables. If the number of possible values is indeterminate, I'm not sure how you could effect what you are requesting.

answered Jul 1, 2014 at 21:44

Angus Atkins-Trimnell

1214 bronze badges

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

1 Comment

fhbi Over a year ago

This is what I was looking for, but I've since learned this is just a huge mistake to try and get my data this way. Good for future reference, nevertheless.

deniskoronets · Accepted Answer · 2014-07-01 21:39:05Z

0

Do you need this? http://www.php.net/manual/en/mysqli-result.fetch-all.php Or I understand you wrong?

answered Jul 1, 2014 at 21:39

deniskoronets

5503 silver badges17 bronze badges

Collectives™ on Stack Overflow

Return SQL query in one row, fetch with PHP [duplicate]

QUESTION:

CLARIFICATION:

2 Answers 2

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

QUESTION:

CLARIFICATION:

2 Answers 2

1 Comment

Comments

Linked

Related