1#@!#!123s

: / var / www / ipwdn / add / php /
Filename : meteo_data.php
back
<?php

//include connection file
include_once("connection.php");

$params = $columns = array();

$params = $_REQUEST;

$columns = $params['columns'];

$where = $sqlTotal = $sqlLimit = "";

// check search value exist
if (!empty($params['search'])) {
   foreach ($columns as $key => $value) {
      if ($key === array_key_first($columns)) {
         $where .= " AND FROM_UNIXTIME(JSON_EXTRACT(data, '$.".$key."'), '%Y-%m-%d %H:%i:%s') LIKE '%".$params['search']."%' ";
      } else {
         $where .= " OR JSON_EXTRACT(data, '$.".$key."') LIKE '%".$params['search']."%' ";
      }
   }
}

// if dtimestamp is null - if null select timestamp ???
$where .= " AND JSON_EXTRACT(data, '$.dtimestamp') IS NOT NULL ";

// getting total number records without any search
$sql = "SELECT";

foreach ($columns as $key => $value) {
   if ($key === array_key_first($columns)) {
      $sql .= " FROM_UNIXTIME(JSON_VALUE(data, '$.".$key."'), '%Y-%m-%d %H:%i:%s') AS dtimestamp, ";
   } else if ($key !== array_key_last($columns)) {
      $sql .= " REPLACE(JSON_VALUE(data, '$.".$key."'), 'null', '-') as $key, ";
   } else if ($key === array_key_last($columns)) {
      $sql .= " REPLACE(JSON_EXTRACT(data, '$.".$key."'), 'null', '-') as $key ";
   }
}

$sql .= "FROM `ipwdn_meteo_data` WHERE device='".$params['device']."' ";

if (isset($params['startDate']) && isset($params['endDate'])) {
   $where .= " AND JSON_EXTRACT(data, '$.dtimestamp') BETWEEN '".$params['startDate']."' AND '".$params['endDate']."' ";
}

//concatenate search sql if value exist
if (isset($where) && $where != '') {
   $sql .= $where;
}

$sqlTotal .= $sql;
$sqlLimit .= $sql;

// if sort column
if ($params['sort']) {
   // $sqlLimit .= " ORDER BY ".$params['sort']."  ".$params['order']." ";
   if ($params['sort'] !== 'dtimestamp') {
      $sqlLimit .= " ORDER BY ".$params['sort']." + 0  ".$params['order']." ";
   } else {
      $sqlLimit .= " ORDER BY ".$params['sort']."  ".$params['order']." ";
   }   
} else {
   $sqlLimit .= " ORDER BY dtimestamp DESC ";
}

// add limit and offset
if (isset($params['offset']) && isset($params['limit'])) {
   $sqlLimit .= " LIMIT ".$params['offset']." ,".$params['limit']." " ;
}

$queryTotal = $conn->query($sqlTotal);
$totalRecords = mysqli_num_rows($queryTotal);

$queryLimit = $conn->query($sqlLimit);
$rows = $queryLimit->fetch_all(MYSQLI_ASSOC);

$json_data = array(
      "rows"            => $rows,   // total data array
      "total"           => $totalRecords,
      );

header("Content-Type: application/json", true);

echo json_encode($json_data);

exit;

?>