0

Hi,

I have been searching and trying for days now but still cant get this to work.
Id like to group my result from a SQL call into a multidimensional array.
The columns that are extracted are Timestamp, room, device and value and gets sorted accordingly.

As long as the Timestamp is the same id like it to be grouped in an array
Hi,

I have been searching and trying for days now but still cant get this to work.
Id like to group my result from a SQL call into a multidimensional array.
The columns that are extracted are Timestamp, room, device and value and gets sorted accordingly.

As long as the Timestamp is the same id like it to be grouped in an array

"2015-04-22 21:30:00, Room1, Device1, 5
"2015-04-22 21:30:00, Room1, Device2, 50
"2015-04-23 21:30:00, Room1, Device1, 6"

Here i would like the array to be grouped containing line 1 and 2 in a multidimensional array with the key for the timestamp.

Edited by Kristiano

5
Contributors
13
Replies
63
Views
2 Years
Discussion Span
Last Post by diafol
Featured Replies
  • $sql = select Timestamp, room, device, value... $data = $stmt->fetchAll(PDO::FETCH_GROUP | PDO::FETCH_ASSOC); $data should now be an array [ timestamp1 => [ 0 => room0 device0 value0, 1 => room1 device1 value1 ], timestamp2 => [ 0 => room0 device0 value0, 1 => room1 device1 value1 ] ] Read More

  • 3
    diafol 3,669   2 Years Ago

    Well [] is shorthand for array(). In the loop you access: $output['some_specific_timestamp'] when you write: $output[$row['timestamp']][] If this key doesn't exist, then it is created. As this is an array, you can just add an item to the next index with the empty []. The item in this case is … Read More

0

Sort result rows by timestamp. See comments in the code for the rest:

// initialize current timestamp to 0;
$currentTimestamp = 0;

// initialize current array that will hold the result
$resultArr = array();

// loop through the result 
// (this example is for PDO, change to whatever driver you use)
while($row = $stm->fetch()) {

    // save current row in a temp array
    $currentRow = array($row['room'], $row['device'], $row['value']);

    // check if you have new timestamp
    // if yes,initialize second dimension array for that TS
    // and save the current TS
    if($row['Timestamp'] != $currentTimestamp) {
        $currentTimestamp = $row['Timestamp'];
        $resultArr[$currentTimestamp] = array();
    }

    // add a temp row to the array
    $resultArr[$currentTimestamp][] = $currentRow;
}

The code is not tested, it is just a concept. You can also add some error checking.

Edited by broj1

0

The code above is very similar to what I use for this. As a generic, I would do this:

// Get empty array to hold data
$output = [];

//Assume using PDO
$data = $stmt->fetchAll(PDO::FETCH_ASSOC); 

//Run loop
foreach($data as $d)
    $output[$d['field3']][] = [$d['field2'],$d['field4'],$d['field5']]; 

Or for a row by row loop:

// Get empty array to hold data
$output = [];

//Assume using PDO
while($d = $stmt->fetch(PDO::FETCH_ASSOC)) 
{
    $output[$d['field3']][] = [$d['field2'],$d['field4'],$d['field5']]; 
}

Edited by diafol

2
$sql = select Timestamp, room, device, value...
$data = $stmt->fetchAll(PDO::FETCH_GROUP | PDO::FETCH_ASSOC); 

$data should now be an array
[
    timestamp1 => [
        0 => room0 device0 value0,
        1 => room1 device1 value1
    ],
    timestamp2 => [
        0 => room0 device0 value0,
        1 => room1 device1 value1
    ]
]
Votes + Comments
This is better If you can use pdo
0

Thanks!
Since i use sqli, what function to be used instead of fetch?
Im currently getting an error in the while statment trying to fetch or
as below fetch the row.

This is my code so far, but still not working.

<?php

// Date selection
    $datefr = $_GET['datefr'];

// Query
    $sql = "select a.timestamp, b.room_name, a.device_name, a.value, a.uom 
                               from fibaro_data as a left outer join fibaro_room as b
                               on a.device_id = b.device_id
                                where a.timestamp >= '$datefr'
                                order by a.timestamp, b.room_name, a.device_name";


    $conn = new mysqli("127.0.0.1:3306","root","xxxxx", "Fibaro");
    $currentTimestamp = 0;
// Check connection
if ($conn->connect_error) {
     die("Connection failed: " . $conn->connect_error);
} 

    $result = $conn->query($sql);

    $data = array();

    while($row = $result->mysqli_fetch_row()) {
        // save current row in a temp array
        $currentRow = array($row['room_name'], $row['device_name'], $row['value'], $row['uom']);
        // check if you have new timestamp
        // if yes,initialize second dimension array for that TS
        // and save the current TS
        if($row['Timestamp'] != $currentTimestamp) {
            $currentTimestamp = $row['Timestamp'];
            $data[$currentTimestamp] = array();
        }
        // add a temp row to the array
        $data[$currentTimestamp][] = $currentRow;
    }

print_r($data);


 $conn->close();
?>

Edited by Kristiano

0

Thanks! Almost there, but its still not working.
I get the result array printed but its still not grouped on timestamp.
SQL table:
1'2015-04-21 11:42:43','Bathroom','Floor Sensor','26.42','C'
2'2015-04-21 11:42:43','Childroom','Childroom Temp','24.70','C'
3'2015-04-21 11:42:43','Hallway','Bike Temp','24.68','C'
4'2015-04-21 11:42:43','Hallway','Temp Sensor','25.30','C'
5'2015-04-21 11:42:43','Kitchen','Freezer Temp','27.31','C'
6'2015-04-21 11:42:43','Kitchen','Refrigerator Temp','8.87','C'
7'2015-04-21 11:42:43','Kitchen','Temp Sensor','24.43','C'
8'2015-04-21 11:42:43','Livingroom','Temp Sensor','26.10','C'
9'2015-04-21 11:42:43','Livingroom','Temp Sensor','25.10','C'
10'2015-04-21 12:00:36','Bathroom','Bathroom Temp','24.90','C'
11'2015-04-21 12:00:36','Bathroom','Floor Sensor','26.42','C'
12'2015-04-21 12:00:36','Childroom','Childroom Temp','24.70','C'

The array:

      [9] => Array
                (
                    [0] => Livingroom
                    [1] => Temp Sensor
                    [2] => 25.10
                    [3] => C
                )

            [10] => Array
                (
                    [0] => Bathroom
                    [1] => Bathroom Temp
                    [2] => 24.90
                    [3] => C
                )

I was expecting that from line 9 to 10 a now array group were created.

Also how do i get the array group key to be the timestamp? So i know to what timestamp it belongs.

0
 // add a temp row to the array
 // $data[$currentTimestamp][] = $currentRow;

Let see I think array_push might do the work

array_push($data[$currentTimestamp],$currentRow);

sorry for my mistake i need to comment the other one

Edited by joshuajames.delacruz

0

Adding the line like this makes no difference:

  // add a temp row to the array
    $data[$currentTimestamp][] = $currentRow;
    array_push($data[$currentTimestamp],$currentRow);

and if i replace the first only keeping the second it gives error
array_push() expects parameter 1 to be array, null given in

0

how about this one I have re-arrange some of your code so that it can be in one group thou I have not tested it I would like to know the results

<?php

// Date selection
    $datefr = $_GET['datefr'];

// Query
    $sql = "select a.timestamp, b.room_name, a.device_name, a.value, a.uom 
                               from fibaro_data as a left outer join fibaro_room as b
                               on a.device_id = b.device_id
                                where a.timestamp >= '$datefr'
                                order by a.timestamp, b.room_name, a.device_name";


    $conn = new mysqli("127.0.0.1:3306","root","xxxxx", "Fibaro");
    $currentTimestamp = 0;
// Check connection
if ($conn->connect_error) {
     die("Connection failed: " . $conn->connect_error);
} 

    $result = $conn->query($sql);

    $data = array();

    while($row = $result->mysqli_fetch_row()) {
        // save current row in a temp array
        // check if you have new timestamp
        // if yes,initialize second dimension array for that TS
        // and save the current TS
        if($row['Timestamp'] != $currentTimestamp) {
            $currentTimestamp = $row['Timestamp'];
            $data[$currentTimestamp] = array();
        }
        // add a temp row to the array
        $data[$currentTimestamp][] = $currentRow;

 $currentRow = array($row['room_name'], $row['device_name'], $row['value'], $row['uom'] , $data[$currentTimestamp]);

    }

print_r($data);

 $conn->close();
?>

Edited by joshuajames.delacruz

0

Now its building a multi dimensional, good! But array is built very strange using wrong value?...And still the timestamp is not set as key for the array.

result:

Array
(
    [0] => Array
        (
            [0] => 
            [1] => Array
                (
                    [0] => Bathroom
                    [1] => Bathroom Temp
                    [2] => 24.90
                    [3] => C
                    [4] => Array
                        (
                            [0] => 
                        )

                )

            [2] => Array
                (
                    [0] => Bathroom
                    [1] => Floor Sensor
                    [2] => 26.42
                    [3] => C
                    [4] => Array
                        (
                            [0] => 
                            [1] => Array
                                (
                                    [0] => Bathroom
                                    [1] => Bathroom Temp
                                    [2] => 24.90
                                    [3] => C
                                    [4] => Array
                                        (
                                            [0] => 
                                        )

                                )

                        )

                )

            [3] => Array
0

Doesn't this work?

$output = [];
while($row = $result->mysqli_fetch_row()) {
{
    $output[$row['Timestamp']][] = [$row['room_name'],$row['device_name'],$row['value'],$row['uom']]; 
}

print_r($output);
0

Yes, that seems to work. I just had a misspell using "Timestamp" instead of "timestamp".

But how does it work?

Edited by Kristiano

3

Well [] is shorthand for array().

In the loop you access:

$output['some_specific_timestamp']

when you write:

$output[$row['timestamp']][]

If this key doesn't exist, then it is created. As this is an array, you can just add an item to the next index with the empty [].

The item in this case is an array of the other fields:

[$row['room_name'],$row['device_name'],$row['value'],$row['uom']]

Remember that [...] is the same as array(...), so you could rewrite the whole thing as:

$output = array();
while($row = $result->mysqli_fetch_row()) {
{
    $output[$row['Timestamp']][] = array($row['room_name'],$row['device_name'],$row['value'],$row['uom']); 
}

It's the same thing. Hope that helps.

Votes + Comments
Very nice explanation
Love the way you always explain piece by piece ill remember that too
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.