up vote 0 down vote favorite

Hello i want to save one row which comes out of a MySQL database with "count('row1'') AS sum" but inside my array i only got empty fields!

Database query:

"SELECT id, value, COUNT('value') AS sum FROM answer_user GROUP BY id,value"

php code:

$result = mysql_query($query) or die(mysql_error());
while($data= mysql_fetch_assoc($result)) {
   echo"<b>{$data['id']}#{$data['value']}#{$data['sum']}#<br>";
}

but $data[sum] is just empty and no numbers are inside.

And a small but minor annoiance is that i can't oder by id descending and value ascending in my query, like value is descending with my query phpMyAdmin says:

   id value     sum
    1   0   1
    1   1   3
    1   2   1
    1   6   1
    2   2   4
    2   5   1
    2   6   1
    3   2   1
    3   3   4
    3   6   1

my main aim would be to store it in one two dimensional array like $data[id][value]="[sum]"

that if i do echo"$data[1][1]"; the result should be plain 3

var_dump($data)
array(3) { ["id"]=> string(1) "1" ["value"]=> string(1) "0" ["sum"]=> string(1) "1" } array(3) { ["id"]=> string(1) "1" ["value"]=> string(1) "1" ["sum"]=> string(1) "3" } array(3) { ["id"]=> string(1) "1" ["value"]=> string(1) "2" ["sum"]=> string(1) "1" } array(3) { ["id"]=> string(1) "1" ["value"]=> string(1) "6" ["sum"]=> string(1) "1" } array(3) { ["id"]=> string(1) "2" ["value"]=> string(1) "2" ["sum"]=> string(1) "4" } array(3) { ["id"]=> string(1) "2" ["value"]=> string(1) "5" ["sum"]=> string(1) "1" } array(3) { ["id"]=> string(1) "2" ["value"]=> string(1) "6" ["sum"]=> string(1) "1" } array(3) { ["id"]=> string(1) "3" ["value"]=> string(1) "2" ["sum"]=> string(1) "1" } array(3) { ["id"]=> string(1) "3" ["value"]=> string(1) "3" ["sum"]=> string(1) "4" } array(3) { ["id"]=> string(1) "3" ["value"]=> string(1) "6" ["sum"]=> string(1) "1" } array(3) { ["id"]=> string(1) "4" ["value"]=> string(1) "2" ["sum"]=> string(1) "2" } array(3) { ["id"]=> string(1) "4" ["value"]=> string(1) "4" ["sum"]=> string(1) "4" }

share|improve this question
Please post the output of var_dump($data); and I would recommend you to enclose the array indeces in qoutes. – ComFreek Nov 5 '11 at 14:15
why would you want all the columns stored in the keys of a multidimensional array? that means you would need all the answers first, and no response data – cgoddard Nov 5 '11 at 14:21
Do you want to get the sum of values of the field "value"? – lvil Nov 5 '11 at 15:34
the field values only reflects my database values like school notes. – user997942 Nov 6 '11 at 9:18
@ComFreek var_dump($data) – user997942 Nov 6 '11 at 10:49
feedback

3 Answers

up vote 1 down vote

The problem in your query is that you are usign count('value') as sum. Mysql is getting value as string you should use it like this.

SELECT id, value, COUNT(value) AS sum FROM answer_user GROUP BY id,value

And add order by 

SELECT id, value, COUNT(value) AS sum FROM answer_user GROUP BY id,value order by id asc , value asc

Its bringing perfect result. The order by will first sort with id then against each idwith value. And the result you have shown here doesn't display there is any 0 returned in sum.

share|improve this answer
feedback
up vote 0 down vote

You mean this?

while($data= mysql_fetch_assoc($result)) {
    $array[$data['id']][$data['value']] = $data['sum'];
}
share|improve this answer
yes i meant that :-) – user997942 Nov 6 '11 at 9:16
yours is close to my solution: $array[$data['id']][$data['value']] = $data['sum']; should give me an array with $array[1][1]= 3 but it does not?? – user997942 Nov 6 '11 at 10:41
that's weird, what do you get? which variable makes an error? – Shomz Nov 6 '11 at 13:12
feedback
up vote -1 down vote

try putting { & } as well as ' around the keys around the arrays like in the edit

share|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.