CODEWITHSUNDEEP
phpmysqljson
Ehsanul Karim
Ehsanul Karim

Reputation: 1

PHP MYSQL to JSON dynamic object

enter image description here

This is my table and I would like to PHP to generate json like this

{
  "1":
  {
    "start_time":"03:27:43","end_time":"06:27:43"
  },
  "2":
  {
    "start_time":"03:27:43","end_time":"06:27:43"
  },
  "3":
  {
    "start_time":"03:27:43","end_time":"06:27:43"
  },
  "4":
  {
    "start_time":"03:27:43","end_time":"06:27:43"
  },
  "5":
  {
    "start_time":"03:27:43","end_time":"06:27:43"
  },
  "6":
  {
    "start_time":"03:27:43","end_time":"06:27:43"
  }
}

Not being able to put the subtopics in PHP Array, can you please help

Upvotes: 0

Views: 202

Answers (2)

Tangentially Perpendicular
Tangentially Perpendicular

Reputation: 5358

If you have MySQL 5.7.22 or later, or MariaDB 10.5 or later, you can do the whole job in a single query, using the JSON_OBJECTAGG() and JSON_OBJECT() functions.

For example, using your table above:

select json_objectagg(week_day, json_object('start_time',start_time, 'end_time',end_time)) as json from jsonTest;

will return this string:

{
"0": 
  {"end_time": "06:27:43.000000", "start_time": "03:27:43.000000"}, 
"1": 
  {"end_time": "06:27:43.000000", "start_time": "03:27:43.000000"}, 
"2": 
  {"end_time": "06:27:43.000000", "start_time": "03:27:43.000000"}, 
"3": 
  {"end_time": "06:27:43.000000", "start_time": "03:27:43.000000"}, 
"4": 
  {"end_time": "06:27:43.000000", "start_time": "03:27:43.000000"}, 
"5": 
  {"end_time": "06:27:43.000000", "start_time": "03:27:43.000000"}, 
"6": 
  {"end_time": "06:27:43.000000", "start_time": "03:27:43.000000"}
}

One query, one fetch, one value returned in the form you want. There's no need to loop over the result set to assemble the data in memory with PHP and then json_encode it - it's all done.

Edit If the time format is important, TIME_FORMAT() is your friend:

select json_objectagg(week_day, json_object('start_time',time_format(start_time, '%H:%i:%s'), 'end_time',time_format(end_time,'%H:%i:%s'))) as json from jsonTest;

Upvotes: 2

georgewoofbates
georgewoofbates

Reputation: 330

It's hard to answer without seeing your current code but the basic steps are:

  1. Pull the data from your MYSQL table - recommended to use MySql PDO as per here
  2. Use json_encode() PHP function to convert the result into JSON as per here
  3. Output the encoded JSON to wherever you need it

Example Code

$db = new PDO('mysql:host='. DB_HOST .';dbname='.DB_NAME.';charset=utf8', DB_USER, DB_PASS);

$stmt = $db->prepare("SELECT start_time, end_time FROM table");
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

echo json_encode($result);

Upvotes: 1

Related Questions