PDO fetchAll to array different output than SQL request

Question

i have MySQL DB, which stores data every minute (for date 1.7.2017 i have 1012 values of 1):

id| value |timeDate
1 | 1     |2017-07-01 ...
2 | 0     |2017-07-01 ...
3 | 1     |2017-07-01 ...
.....

I want to daily count values with value 1 (function):

$stmt = $db->prepare("SELECT day(dTimeDate), COUNT(*) FROM sensorData
        WHERE MONTH(dTimeDate) = :dateMonth AND YEAR(dTimeDate) = :dateYear AND _idSensor = :idSensor AND dValue = :valueSensor
        GROUP BY dTimeDate");
$stmt->execute(array(':dateMonth' => $m, ':dateYear' => $y, ':idSensor' => $idSensor, ':valueSensor' => $valueSensor));
$row2 =$stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($row2 as $row) {
    echo $row['day(dTimeDate)']. ' - ' .$row['COUNT(*)'].'<br/>';
}

I expect to get (like my SQL, example):

(day | Count)
1 - 1012 (count all values with value 1 in day 1)
2 - 105
...(31 lines, one for every day)

Instead i get:

(day | Count)
1 - 1
1 - 1
...(1012 lines of day 1 and value 1)
2 - 1

row2 returns array of all data instead of daily.

How can i get expected values?

I tried to reproduce your codes behaviour. I am not sure what you mean by your expected results. What are 1 and 2? The id or the value? — Yolo
– Yolo, Commented Jul 30, 2017 at 13:40
My select 'SELECT day(dTimeDate), COUNT(*) FROM sensorData' returns day and count values. That means 'day 1 - 1012(count values by day 1). 'row2' reurns array of all data (see update). — JanOlMajti
– JanOlMajti, Commented Jul 30, 2017 at 14:19

Ravinder Payal · Accepted Answer · 2017-07-31 15:08:06Z

1

There is a little mistake in code:-

Change group by dTimeDate to group by day(dTimeDate)

Corrected complete code

$stmt = $db->prepare("SELECT day(dTimeDate), COUNT(*) FROM sensorData
    WHERE MONTH(dTimeDate) = :dateMonth AND YEAR(dTimeDate) = :dateYear AND _idSensor = :idSensor AND dValue = :valueSensor
    GROUP BY day(dTimeDate)");

$stmt->execute(array(':dateMonth' => $m, ':dateYear' => $y, ':idSensor' => $idSensor, ':valueSensor' => $valueSensor));
$row2 =$stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($row2 as $row) {
    echo $row['day(dTimeDate)']. ' - ' .$row['COUNT(*)'].'<br/>';
}

answered Jul 31, 2017 at 15:08

Ravinder Payal

3,0291 gold badge34 silver badges43 bronze badges

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

2 Comments

JanOlMajti Over a year ago

Thank you for this obvious mistake, which take me allmost 2 days :)

Ravinder Payal Over a year ago

It's OK, n Welcome. Just a Suggestion. When you are not able to find bugs, either do debugging line by line or leave the thing for few hours and then just review it.

Collectives™ on Stack Overflow

PDO fetchAll to array different output than SQL request

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related