Sql to multidimensional array

Question

Kristiano 0 Newbie Poster

9 Years Ago

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.

php

Edited 9 Years Ago by Kristiano

5 Contributors
13 Replies
2K Views
18 Hours Discussion Span
Latest Post 9 Years Ago Latest Post by diafol

broj1 356 Humble servant

9 Years Ago

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 9 Years Ago by broj1

diafol

9 Years Ago

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 9 Years Ago by diafol

jstfsklh211 79 Light Poster

9 Years Ago

$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
    ]
]

diafol commented: This is better If you can use pdo +15

diafol

9 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 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.

jj.dcruz commented: Love the way you always explain piece by piece ill remember that too +3

broj1 commented: Very nice explanation +11

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Love the way you always explain piece by piece ill remember that too

Kristiano 0 Newbie Poster · Answer 1 · 2015-04-23T07:43:55+00:00

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();
?>

jj.dcruz 0 Junior Poster · Answer 2 · 2015-04-23T07:54:09+00:00

you need to change line 25

to this format

$result->fetch_assoc()

Kristiano 0 Newbie Poster · Answer 3 · 2015-04-23T09:00:34+00:00

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.

jj.dcruz 0 Junior Poster · Answer 4 · 2015-04-23T09:30:43+00:00

 // 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

Kristiano 0 Newbie Poster · Answer 5 · 2015-04-23T09:41:48+00:00

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

jj.dcruz 0 Junior Poster · Answer 6 · 2015-04-23T09:54:53+00:00

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();
?>

Kristiano 0 Newbie Poster · Answer 7 · 2015-04-23T10:07:14+00:00

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

diafol · Answer 8 · 2015-04-23T10:48:10+00:00

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);

Kristiano 0 Newbie Poster · Answer 9 · 2015-04-23T10:59:49+00:00

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

But how does it work?