CODEWITHSUNDEEP
phparraysdatabaseassociative
user2688737
user2688737

Reputation: 51

associative array with php

I need to create an associative array with: date and number of events for that date.

Imagine this table (XPTO) in the database:

timestamp            id  parameter  sensor_id
2013-09-10 12:43:54   1   34           3
2013-07-23 10:32:31   2   54          65
2013-07-23 10:32:31   3   23          45
2013-07-23 10:32:31   4   12          1
2013-09-10 12:43:54   5   1           43

and now, the result that I want is the following: [09.10.2013 12:43:54, 2], [23.07.2013 10:32:31,3]. Any idea to help me?

Upvotes: 1

Views: 197

Answers (1)

Malkocoglu
Malkocoglu

Reputation: 477

SQL does the trick.

$myarray = array();
$result = mysql_query("SELECT timestamp, count(id) FROM xpto GROUP BY timestamp");
while ($row = mysql_fetch_array($result))
    $myarray[$row[0]]=$row[1];

If you want full information instead of counting:

$myarray = array();
$result = mysql_query("SELECT * FROM xpto ORDER BY timestamp");
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    $timestamp=$row['timestamp'];
    unset($row['timestamp']);
    $myarray[$timestamp][]=$row;
}

Now you have this array as a result:

[2013-07-23 10:32:31] => array (
                          array (
                              'id'=>2,
                              'parameter'=>54,
                              'sensor_id'=>65,
                          ),
                          array (
                              'id'=>3,
                              'parameter'=>23,
                              'sensor_id'=>45,
                          ),
...

Please modify following code based on your database connection type (PDO, etc)

Upvotes: 1

Related Questions