How to format the retrieved MySQL data in JSON format using php

Question

I am trying to retrieve data from MySQL using php but I am a bit new to php. I have a table like so

    distance    height      time
         25      70       17:50:24
         12      69       17:30:24
         15      55       17:10:24

And I hope to retain a JSON like in this format:

{
    
      "data": true,
    
      "time":[
      "17:50:24",
      "17:30:24",
      "17:10:24"
    ],
    
      "distance":[
        25,
        12,
        15
      ],  
    
       "height":[
        70,
        69,
        55
      ]
    
    }

My php script is like this:

<?php  
$db = mysqli_connect("127.0.0.1", "root", "pw", "test1"); 
$row=$db->prepare('SELECT * FROM table');  
$row->execute();
$json_data=array();
$json_latdata=array();

$result = array();
$result1 = array();
foreach($row as $rec)//foreach loop  
{  
    $json_array['distance']=$rec['distance']; 
    $json_array['height']=$rec['height'];   
    $json_array['time']=$rec['time'];  
    array_push($json_data,$json_array); 
    array_push($json_latdata,$json_array1);   
}  
$result = $json_data;
$result1 = $json_latdata;
echo json_encode(array($result,$result1));
?>

But the output is row format (with repeated headers) and not exactly in the JSON format I desire and its reading null:

[[{"distance":null,"height":null,"time":null},
  {"distance":null,"height":null,"time":null},
  {"distance":null,"height":null,"time":null},
  {"distance":null,"height":null,"time":null},
  {"distance":null,"height":null,"time":null},
  {"distance":null,"height":null,"time":null},
  {"distance":null,"height":null,"time":null},
  {"distance":null,"height":null,"time":null},
  {"distance":null,"height":null,"time":null},
  {"distance":null,"height":null,"time":null}],
 [null,null,null,null,null,null,null,null,null,null]]

Thank you so much for the time!

You must be giving incorrect column names in your $rec[...] indexes. This is case sensitive, so check the actual names in the table. — Barmar
– Barmar, Commented Feb 14, 2021 at 17:58
Turn on full error reporting so you get warnings about all these undefined variables. — Barmar
– Barmar, Commented Feb 14, 2021 at 18:01
Thanks for the response! I double checked the column names. They are small case and matched to the table! — swiftlearneer
– swiftlearneer, Commented Feb 14, 2021 at 18:04
Best is to avoid using SELECT *. If you only care about specific columns, name them explicitly. — Barmar
– Barmar, Commented Feb 14, 2021 at 18:06

Barmar · Accepted Answer · 2021-02-14 18:28:21Z

1

You're creating a new object for each row, not adding them to arrays for each column.

Create a 2-dimensional array with elements for each column. Then when processing the query results, push each column onto the appropriate element array.

<?php  
$db = mysqli_connect("127.0.0.1", "root", "pw", "test1"); 
$stmt = $db->prepare('SELECT distance, height, time FROM table');  
$stmt->execute();
$rows = $stmt->get_result();

$result = ['data' => true, 'time' => [], 'distance' => [], 'height' => []];
foreach($rows as $rec)//foreach loop  
{  
    foreach ($rec as $col => $value) {
        $result[$col][] = $value;
    }
}  
echo json_encode($result);
?>

edited Feb 14, 2021 at 18:28

answered Feb 14, 2021 at 18:05

Barmar

789k57 gold badges554 silver badges668 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

swiftlearneer Over a year ago

Thanks for the response! I guess I was performing incorrectly in my attempt! I am just trying your suggestion but it appears with a warning: Warning: Invalid argument supplied for foreach() in xxx.php on line 8 {"data":true,"time":[],"distance":[],"height":[]}

Barmar Over a year ago

You need to call $row->get_result() to get a result object that you can iterate over.

swiftlearneer Over a year ago

I thought the order in MySQL table matters (I have distance, height, then time) and changed this line $result = ... order but it does not help

Barmar Over a year ago

Order doesn't matter if you're accessing it by column name, not index.

Barmar Over a year ago

Did you change foreach to use the new $rows variable?

|

Collectives™ on Stack Overflow

How to format the retrieved MySQL data in JSON format using php

1 Answer 1

9 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Related