CODEWITHSUNDEEP
phpmysqlmysqli
Madmenyo
Madmenyo

Reputation: 8584

combining rows in result

I have this data table:

+-ID-+-sensorID-+----date---+-value-+
| 1  | 1        |'some date'|  20   |
| 2  | 1        |'some date'|  15   |
| 3  | 1        |'some date'|  18   |
| 4  | 2        |'some date'|  40   |
| 5  | 2        |'some date'|  68   |
| 6  | 2        |'some date'|  55   |

Now I want to create an array like this in php:

date - value sensorID(1) - value sensorID(2)

I also need something to show whenever a date has a single sensor value:

[date] => 01-01-2014 [value] => 50 [value] => 60
[date] => 02-01-2014 [value] => 20 [value] => 30
[date] => 03-01-2014 [value] => null [value] => 55
[date] => 04-01-2014 [value] => 20 [value] => 33

How can I do this efficiently in some simple steps? Otherwise I would just create a result for ID 1 and ID 2 or run my function twice on the complete array.

Upvotes: 0

Views: 64

Answers (1)

Paul
Paul

Reputation: 9022

Just get all the data in one run and construct the array in PHP:

// do the mysql query stuff >> $result (array of all rows in db)
$data = array();
foreach ($result AS $row) {
  if (!isset($data[$row['date']])) {
    $data[$row['date']] = array('sensor1' => null, 'sensor2' => null);
  }
  $data[$row['date']]['sensor' . $row['sensorID']] = $row['value'];
}

Result would be something like

$data = array(
  '01-01-2014' => array('sensor1' => 50, 'sensor2' => 60),
  '02-01-2014' => array('sensor1' => 20, 'sensor2' => 30),
  '03-01-2014' => array('sensor1' => null, 'sensor2' => 55),
  '04-01-2014' => array('sensor1' => 20, 'sensor2' => 33)
);

Upvotes: 1

Related Questions