Как написать запрос MySQL или обработать в PHP для получения массива нужного вида?

Question

[i_goodwin]

Доброго дня! К сути: есть база данных "mqtt", таблица "topics" в ней. В базу каждую минуту посредством MQTT прилетают данные от esp8266 с показаниями температуры (топик tempinroom) и влажности (топик huminroom) в комнате; и записываются в таблицу. Вид таблицы "topics" следующий:

+----+---------------------+------------+-------+
| id | time                | topic      | value |
+----+---------------------+------------+-------+
|  1 | 2018-09-02 12:27:13 | tempinroom |  24.2 |
|  2 | 2018-09-02 12:27:13 | huminroom  |  56.8 |
|  3 | 2018-09-02 12:28:13 | tempinroom |    24 |
|  4 | 2018-09-02 12:28:13 | huminroom  |  57.5 |
|  5 | 2018-09-02 12:29:13 | tempinroom |  23.9 |
+----+---------------------+------------+-------+

Сейчас озадачился построением графика изменений этих параметров с помощью HighCharts. График одной из величин (любой) построить удается. Для этого формируется файл json.php следующего содержания:

<?php 
date_default_timezone_set('UTC');
$link = mysqli_connect('localhost', "***", '***');
if (!$link)
    echo ("Ошибка соединения с базой данных");
mysqli_select_db($link, "mqtt") or die("Невозможно открыть базу данных"); 

$result = mysqli_query($link,
    'SELECT id,time,topic,value FROM `topics` WHERE topic = "tempinroom"');

while ($i = mysqli_fetch_row($result))
{
	$all[] = array(strtotime($i[1]), (float)$i[3]);
}
echo json_encode($all);
mysqli_close($link); 
?>

На выходе получаем массив вида

[[1535891233,24.2],[1535891293,24],[1535891353,23.9]]

т.е. в массиве есть только время в Unix-формате и значение (в данном случае - температуры).

Вопрос: каким образом получить массив вида

[[time1, tempinroom1, huminroom1],[time2, tempinroom2, huminroom2]]

Время записи пары значений - влажности и температуры - одинаково, как можно видеть по таблице. Быть может, решение где-то на поверхности, но опыта у меня пока мало. Ломаю голову уже 3 дня, гугл перегуглил)

Answer 1

[0xD34F]

Вариантов тут несколько:

• Так как значения у вас парные - измените структуру БД, пусть оба значения лежат в одной строке
  
• Путь дурака: два запроса в PHP и ручное объединение их результатов - цикл по первому массиву, вложенный цикл по второму, как встретился элемент с таким же значением time, его value добавляется в элемент из первого массива
  
• Получить данные одним запросом:
  

  SELECT t1.time, t1.value AS temp_val, t2.value AS hum_val
  FROM topics t1
  JOIN topics t2 ON t2.time = t1.time
  WHERE
    t1.topic = 'tempinroom' AND
    t2.topic = 'huminroom'

  
  

Comments

[alexalexes]

Путь дурака:
Раскидайте данные по ассоциативному ключу time:

$result = mysqli_query($link,  'SELECT id,time,topic,value FROM `topics` ORDER BY time'); // для предварительной выборки в pre_out лишним не будет, если изначальная выборка будет отсортирована по времени
$pre_out = [];  // предварительная выборка
$topic_types = [];
while ($row = mysqli_fetch_row($result))
{
  $topic_types[$row[2]] = $row[2];   // запоминаем, какие типы параметров нам встретились 
  $pre_out[$row[1]][$row[2]] = $row[3]; // закладываем значение параметра по ключу времени, по ключу типа параметра
}
$out = []; // окончательная выборка
foreach($pre_out as $time_key => $values) // перебираем время
{
   $out_row[0] = $time_key; // нуль-элемент - это время
   $i = 1;  // счетчик для раскидывания значений параметров дальше по ячейкам строки
   foreach($topic_types as $topic_type) // проходим по всем возможным типам параметрам, которые встречались в выборке
   {
      $out_row[$i] = array_key_exists($topic_type, $values) ? $values[$topic_type] : null;  //если по текущему отсчету времени не был собран данный тип параметра, то записываем null, а если зафиксирован, то его значение.
      $i++;
   }
  $out[] = $out_row;
}

PS: Один маленький плюс есть этого пути - окончательную выборку можно всегда сформировать, не зная заранее, какое количество типов параметров существует вообще.