CODEWITHSUNDEEP
phpandroidmysqljsoncordova
Moeez
Moeez

Reputation: 478

jSon object from PHP MySQL result

I am working on cordova to make an android app just for my practice. The app is showing data on a chart. The x axis have dateTime and y axis have Energy values in it. I know this must be a duplicate question. But as a newbie in php and mysql i want to create a jSON object and in that object i want to create an array.

For now i have made a hard-coded jSON object as per my requirements and performed my task. Below is my json object 

var jsonData = {
"11111111": [
    { "x": "2016-01-25 12:58:52", "y": 25.22 },
    { "x": "2016-01-26 13:33:23", "y": 30.14 },
    { "x": "2016-01-27 13:49:18", "y": 35.58 },
    { "x": "2016-01-29 13:55:01", "y": 40.25 },
    { "x": "2016-01-30 14:00:15", "y": 47.25 },
    { "x": "2016-01-31 15:50:15", "y": 14.25 },
    { "x": "2016-02-01 16:16:15", "y": 20.25 },
    { "x": "2016-02-02 17:49:15", "y": 5.25 },
    { "x": "2016-02-03 18:20:15", "y": 77.25 },
    { "x": "2016-02-04 19:30:15", "y": 65.25 }
],
"22222222": [
     { "x": "2016-02-25 14:23:31", "y": 17.99 },
     { "x": "2016-02-26 14:30:36", "y": 27.78 },
     { "x": "2016-02-27 14:34:23", "y": 37.45 },
     { "x": "2016-02-28 14:42:47", "y": 47.73 },
     { "x": "2016-02-29 15:07:26", "y": 57.58 },
     { "x": "2016-03-01 16:17:26", "y": 32.58 },
     { "x": "2016-03-02 17:27:26", "y": 42.58 },
     { "x": "2016-03-03 18:40:26", "y": 62.58 },
     { "x": "2016-03-04 19:47:26", "y": 77.58 },
     { "x": "2016-03-05 20:37:26", "y": 67.58 }
],
"33333333": [
    { "x": "2016-03-25 15:14:49", "y": 15.66 },
    { "x": "2016-03-26 15:32:51", "y": 25.68 },
    { "x": "2016-03-27 15:40:32", "y": 35.73 },
    { "x": "2016-03-28 15:58:07", "y": 45.46 },
    { "x": "2016-03-29 16:21:25", "y": 46.79 },
    { "x": "2016-03-30 17:21:25", "y": 26.79 },
    { "x": "2016-04-01 18:21:25", "y": 56.79 },
    { "x": "2016-04-02 19:21:25", "y": 46.79 },
    { "x": "2016-04-03 20:22:25", "y": 60.79 },
    { "x": "2016-04-04 21:52:25", "y": 77.79 }
],
"44444444": [
    { "x": "2016-04-25 12:58:52", "y": 20.22 },
    { "x": "2016-04-26 13:33:23", "y": 30.14 },
    { "x": "2016-04-27 13:55:01", "y": 40.25 },
    { "x": "2016-04-28 14:23:31", "y": 50.99 },
    { "x": "2016-04-29 14:30:36", "y": 60.78 },
    { "x": "2016-04-30 15:35:36", "y": 45.78 },
    { "x": "2016-05-01 16:48:36", "y": 63.78 },
    { "x": "2016-05-02 17:58:36", "y": 77.78 },
    { "x": "2016-05-03 18:30:36", "y": 37.78 },
    { "x": "2016-05-04 19:30:36", "y": 49.78 }
],
"55555555": [
    { "x": "2016-05-25 14:42:47", "y": 55.73 },
    { "x": "2016-05-26 15:07:26", "y": 65.58 },
    { "x": "2016-05-27 15:14:49", "y": 75.66 },
    { "x": "2016-05-28 15:32:51", "y": 85.68 },
    { "x": "2016-05-29 16:10:07", "y": 95.46 },
    { "x": "2016-05-30 18:48:07", "y": 55.46 },
    { "x": "2016-06-01 19:18:07", "y": 48.46 },
    { "x": "2016-06-02 20:58:07", "y": 68.46 },
    { "x": "2016-06-03 21:28:07", "y": 105.46 },
    { "x": "2016-06-04 24:18:07", "y": 45.46 }
]
}

I want to create the exact same copy of that by using MySQL result. In above json the 11111111, 22222222, 33333333, 44444444, 55555555 are my serial numbers of a meter, as stated above x and y have date time and energy values. Serial number, date time and energy are in the same table and serial number is unique for each meter. Also these serial numbers have unique ID but i don't want to use it because my search is based on serial numbers not on ID.

Also my database is live i.e. it's on a server. Below is my php code in which i have created a simple table and showed all the data in it.

<?php

  require_once('config.php');
  $sql = "SELECT Device_ID, Energy_kwh, Power_kW, Voltage_Phase_1,Voltage_Phase_2,Voltage_Phase_3, Current_Phase_1,Current_Phase_2,Current_Phase_3, Data_Datetime, Device_Serial_Number FROM ads_device_data where Device_ID= '2'";
  //$result = $con->query($sql);
  if($result = mysqli_query($con,$sql))
  {
    if(mysqli_num_rows($result)>0)
  {
    echo"<table id='myTable'>";
    echo"<tr>";
     echo"<th>ID</th>";
     echo"<th>Energy</th>";
     echo"<th>Power</th>";
     echo"<th>Voltage (all phases)</th>";
     echo"<th>Current (all phases)</th>";
     echo"<th>Date Time</th>";
     echo"<th>Serial #</th>";
    echo"</tr>";
    while($row = mysqli_fetch_array($result))
    {
        echo"<tr  style='text-align:center'>";
        echo"<td>" . $row['Device_ID'] . "</td>";
        echo"<td>" . $row['Energy_kwh'] . "</td>";
        echo"<td>" . $row['Power_kW'] . "</td>";
        echo"<td>" . $row['Voltage_Phase_1'] . " : " . $row['Voltage_Phase_2'] . " : " . $row['Voltage_Phase_3'] . "</td>";
        echo"<td>" . $row['Current_Phase_1'] . " : " . $row['Current_Phase_2'] . " : " . $row['Current_Phase_3'] . "</td>";
        echo"<td>" . $row['Data_Datetime'] . "</td>";
        echo"<td>" . $row['Device_Serial_Number'] . "</td>";
        echo"</tr>";
        //echo "id: ".$row["Device_ID"]." <br> "." -Energy ".$row["Energy_kwh"]." <br> "." -Power " .$row["Power_kW"]." <br> "." -Voltage(all phases) ".$row["Voltage_Phase_1"]. " " .$row["Voltage_Phase_2"]. " " .$row["Voltage_Phase_3"]." <br> ". "-Current(all phases)" .$row["Current_Phase_1"]. " " .$row["Current_Phase_2"]. " " .$row["Current_Phase_3"]." <br> ". " -Date Time " .$row["Data_Datetime"]." <br> "." Serial# ".$row["Device_Serial_Number"]. " <br> ";
    }
    echo"</table>";
     mysqli_free_result($result);
}
else
{
    echo "No records matching your query were found.";
}
}else
{
//$result->close();
//mysqli_free_result($result);
    echo "ERROR: Could not able to execute the query. " .   mysqli_error($con);
 }
  mysqli_close($con);
 //$con->close();
 ?>

Any help would be highly appreciated.

Upvotes: 1

Views: 48

Answers (1)

rNix
rNix

Reputation: 2557

Try this

$sql = "SELECT Device_ID, Energy_kwh, Power_kW, Voltage_Phase_1,Voltage_Phase_2,Voltage_Phase_3, Current_Phase_1,Current_Phase_2,Current_Phase_3, Data_Datetime, Device_Serial_Number FROM ads_device_data where Device_ID= '2'";
$result = mysqli_query($con, $sql);
$data = [];
if ($result) {
    while ($row = mysqli_fetch_array($result)) {
        $serial = $row['Device_Serial_Number'];
        $x = $row['Data_Datetime'];
        $y = $row['Energy_kwh'];

        if (!isset($data[$serial])) {
            $data[$serial] = [];
        }
        $data[$serial][] = ['x' => $x, 'y' => $y];
    }
    mysqli_free_result($result);
}
$json = json_encode($data, JSON_PRETTY_PRINT);
echo "var jsonData = " . $json;

Upvotes: 2

Related Questions