PHP SQL: GROUP BY as array index

Question

I have this query for mysql:

SELECT HOUR(time),COUNT(*) FROM pageview WHERE time >= DATE_SUB(NOW(),INTERVAL 12 HOUR) GROUP BY HOUR(time)

For example, this is the output:

Array
(
    [0] => Array
        (
            [HOUR(time)] => 1
            [COUNT(*)] => 1
        )

    [1] => Array
        (
            [HOUR(time)] => 10
            [COUNT(*)] => 4
        )

    [2] => Array
        (
            [HOUR(time)] => 11
            [COUNT(*)] => 5
        )

)

However I want the output like this

Array
(
    [1] => 1
    [10] => 4
    [11] => 5
)

The array index should be the value of [HOUR(time)]. I would prefer directly by changing the query.

To fetch the data I use this:

$stmt = $db->prepare($query);
$result = $stmt->execute();
$views = $stmt->fetchAll();

How do you fetch the data? Given an array, you may be able to convert to your desired mode. Just with the MySQL you cannot. — fedorqui
– fedorqui, Commented Mar 22, 2014 at 11:20
@fedorqui I added how I fetch data to the question. @ haseeb this does not work — user3449482
– user3449482, Commented Mar 22, 2014 at 11:25

Farnabaz · Accepted Answer · 2014-03-22 11:34:41Z

1

indexes in mysql results create automatically , to achieve your goal loop through your first array and create new one as you want

$result = array();
foreach($views as $row) {
    $result[$row['HOUR(time)']] =  array( 'COUNT(*)' =>  $row['COUNT(*)']);
}
print_r($result); // check output

or simpler form

$result = array();
foreach($views as $row) {
    $result[$row['HOUR(time)']] =  $row['COUNT(*)'];
}
print_r($result); // check output

edited Mar 22, 2014 at 11:34

answered Mar 22, 2014 at 11:23

Farnabaz

4,0662 gold badges25 silver badges42 bronze badges

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

6 Comments

Farnabaz Over a year ago

so consider accept answer or vote up :) @user3449482

user3449482 Over a year ago

Ok, I will. Another question: I want to display the value of the arrays (from 1 to 12) seperated with a comma. How can I make this simpler? Instead of: echo $result[0].', '.$result[1].', '.$result[2].', '.$result[3].', '.$result[4].', '.$result[5] ? Can I convert it somehow to a string like that instead of an array?

Farnabaz Over a year ago

use implode() like this echo implode(', ', $result); look docs on php.net

user3449482 Over a year ago

This skips empty arrays. I want to display the results $result[0] to $result[12] and display a "0" if it's not defined.

Farnabaz Over a year ago

so write foreach and check items and if empty print 0

|

Sadikhasan · Accepted Answer · 2014-03-22 11:28:19Z

0

Try with this

$res = array();
foreach($views as $data)
{
 $res[$data['HOUR(time)']] =  array( 'COUNT(*)' =>  $data['COUNT(*)']);
}
print_r($res);

answered Mar 22, 2014 at 11:28

Sadikhasan

18.6k23 gold badges84 silver badges126 bronze badges

Collectives™ on Stack Overflow

PHP SQL: GROUP BY as array index

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related