Export MySQL Database with 3 tables to json format via php

Question

I would like to export the data of my database to json format below, had already tried but i can only get to show 1 table and not the whole with reference.

CREATE TABLE generic (
  id INTEGER  NOT NULL PRIMARY KEY,
  errorCode INT,
  errorMsg TEXT,
  PRIMARY KEY (id)
);

CREATE TABLE generic_data (
  generic_id TEXT,
  id INT,
  name TEXT,
  ovpn TEXT,
  category TEXT,
  PRIMARY KEY (id),
  FOREIGN KEY (generic_id) REFERENCES generic(id)
);

CREATE TABLE generic_data_children (
  generic_data_id INT,
  id INT,
  name TEXT,
  config TEXT,
  PRIMARY KEY (id),
  FOREIGN KEY (generic_data_id) REFERENCES generic_data(id)
);

Here is my database info with the table columns for reference

Expected Json Format:

{
    "data": [{
        "children": [{
            "id": 1,
            "name": "Default",
            "config": "config1"
        }, {
            "id": 2,
            "name": "sample",
            "config": "config2"
        }, {
            "id": 3,
            "name": "sample2",
            "config": "config3"
        }, {
            "id": 4,
            "name": "testnetwork",
            "config": "config4"
        }, {
            "id": 5,
            "name": "sample5",
            "config": "config5"
        }],
        "id": 1,
        "name": "PH-Philippines 1",
        "ovpn": "testovpn",
        "category": "Private"
    }],
    "errorCode": 0,
    "errorMsg": ""
}

Show us your code

Chilarai
– Chilarai

2020-07-26 07:21:25 +00:00
Commented Jul 26, 2020 at 7:21 — Chilarai
– Chilarai, Commented Jul 26, 2020 at 7:21
here sir @Chilarai pastebin.com/raw/RWQrx9fa

Dibon John Seron
– Dibon John Seron

2020-07-26 10:59:44 +00:00
Commented Jul 26, 2020 at 10:59 — Dibon John Seron
– Dibon John Seron, Commented Jul 26, 2020 at 10:59

Barbaros Özhan · Accepted Answer · 2020-07-26 12:08:39Z

1

You can use JSON_ARRAY(), GROUP_CONCAT() and JSON_OBJECT() functions together as

SELECT JSON_PRETTY(
       REPLACE( 
       REPLACE(
       REPLACE(
               JSON_OBJECT(
               'errorCode', g.errorCode, 
               'errorMsg', COALESCE(g.errorMsg,""),
               'data',JSON_ARRAY(
                                 (SELECT 
                                         JSON_OBJECT('id',gd.id,
                                                     'name',gd.name,
                                                     'ovpn',gd.ovpn,
                                                     'children',
                                         JSON_ARRAY(            
                                         GROUP_CONCAT(
                                                      JSON_OBJECT('name',gdc.name,
                                                                  'id',gdc.id,
                                                                  'config',gdc.config)
                                                      ) )
                                         ) 
                                    FROM generic_data_children gdc
                                    LEFT JOIN generic_data AS gd 
                                      ON gd.id = gdc.generic_data_id
                                   WHERE generic_data_id = g.id
                                )
                       )
               )
          ,'\\"','"') 
        ,'"{','{') 
         ,'}"','}')  
        ) AS 'New JSON'
  FROM generic g

Demo

Using REPLACE() functions are needed at the end in order to format related to double-quotes.

Btw, get rid of PRIMARY KEY repetition for the first table, and convert data type of generic_id column to INT within the second table.

answered Jul 26, 2020 at 12:08

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

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

2 Comments

Dibon John Seron Over a year ago

Amazing sir had tried it in mysql but how could i use it in php to get values from database and output to json heres my test php code i hope you can help me sir. pastebin.com/raw/RWQrx9fa

Barbaros Özhan Over a year ago

thank you @DibonJohnSeron , but excuse me I don't know php, and cannot see the image from that site either.

Collectives™ on Stack Overflow

Export MySQL Database with 3 tables to json format via php

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related