if I have this SQL query:
select substring(id for 2) as key,
yw, count(*)
from pref_money group by yw, key
returning number of users per week and per key:
key | yw | count
-----+---------+-------
VK | 2010-45 | 144
VK | 2010-44 | 79
MR | 2010-46 | 72
OK | 2010-48 | 415
FB | 2010-45 | 11
FB | 2010-44 | 8
MR | 2010-47 | 55
VK | 2010-47 | 136
DE | 2010-48 | 35
VK | 2010-46 | 124
MR | 2010-44 | 40
MR | 2010-45 | 58
FB | 2010-47 | 13
FB | 2010-46 | 13
OK | 2010-47 | 1834
MR | 2010-48 | 13
OK | 2010-46 | 1787
DE | 2010-44 | 83
DE | 2010-45 | 128
FB | 2010-48 | 4
OK | 2010-44 | 1099
OK | 2010-45 | 1684
DE | 2010-46 | 118
VK | 2010-48 | 29
DE | 2010-47 | 148
Then how can I please count those users? I'm trying:
$sth = $db->prepare('select substring(id for 2) as key, yw, count(*)
from pref_money group by yw, key');
$sth->execute();
while ($row = $sth->fetch(PDO::FETCH_ASSOC))
++$users[$row['yw']][$row['key']];
print_r($users);
but get numerous errors.
I'm trying to get the data for a stacked bars diagram. The x-axis will show the week numbers and the y-axis will show number of users, grouped by the key strings.
Thank you! Alex
Are you trying to total the count column or just get number of rows returned? If the latter, php has a method for that. if the former, you need to make it $users[$row['yw']][$row['key'] += $row['count'] instead (assuming the array has already been created).