Combining mysql columns to get nested json result

Question

I have a sql table like this:

id  a  b  c1  c2  c3  d
 1  x  y  z1  z2  z3  w
 ....

I want the following json output

{
"data": {
    "id": "1",
    "a": "x",
    "b": "y",
    "c": {
        "c1": "z1",
        "c2": "z2",
        "c3": "z4"
        },
    "d": "w"
    }
}

How should the mysqli query be for this case? What should be the array format and looping be to convert the query result into the required json?

For a new row, it would be added to data. So it would become "data": {data_of_row1, data_of_row2}

I have tried a few things but they were no where near the expected output. — suku
– suku, Commented Mar 30, 2016 at 12:02
what about if you have more than one row into the table? how the JSON should looks like? — mitkosoft
– mitkosoft, Commented Mar 30, 2016 at 12:11
@mitkosoft: I have added the added how the json should look for multiple rows — suku
– suku, Commented Mar 30, 2016 at 12:24
@Strawberry, I want the sql query and looping to get an array which on json_encode would give the following result — suku
– suku, Commented Mar 30, 2016 at 12:31

marc_s · Accepted Answer · 2017-08-26 13:46:08Z

1

Simple preg_match with foreach can do that:

    <?php
        $link = mysqli_connect('host', 'user', 'pass', 'db') or die(mysqli_error($link));
        $query = "SELECT * FROM sql_table ORDER BY id";
        $result = mysqli_query($link, $query) or die(mysqli_error($link));
        $json = array();
        $rc = 0;
        while($row = mysqli_fetch_assoc($result)){
            foreach($row as $rowName => $rowValue){
                if(preg_match_all('/\d+/', $rowName) !== 0){
                    $index = substr($rowName, 0,1);
                    $json[$rc]['data'][$index][$rowName] = $rowValue;
                }else{
                    $json[$rc]['data'][$rowName] = $rowValue;
                }
            }
            $rc++;
        }
        print_r(json_encode($json));
    ?>

Output:

[{
    "data": {
        "id": "1",
        "a": "x",
        "b": "y",
        "c": {
            "c1": "z1",
            "c2": "z2",
            "c3": "z3"
        },
        "d": "w"
    }
}, {
    "data": {
        "id": "2",
        "a": "xx",
        "b": "yy",
        "c": {
            "c1": "zz1",
            "c2": "zz2",
            "c3": "zz3"
        },
        "d": "ww"
    }
}]

This will work with any columns similar to yours (f4, f5, r, t etc).

edited Aug 26, 2017 at 13:46

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Mar 30, 2016 at 12:31

mitkosoft

5,3361 gold badge15 silver badges32 bronze badges

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

4 Comments

Strawberry Over a year ago

This looks more like it

Mawg Over a year ago

Does that code need to be expanded if the data are more deeply nested? Is there a generic version?

mitkosoft Over a year ago

@MawgsaysreinstateMonica, most probably it should be expanded, as it depends on the name of the table's columns. So we might need an example of how your data is nested and what you want as output, best in a new SO post.

Mawg Over a year ago

For the time being, I am examining MySql v8's new JSON functionality. E.g mysqlserverteam.com/json_table-the-best-of-both-worlds

Daan · Accepted Answer · 2016-03-30 12:32:56Z

0

You can make use of CONCAT and explode();

$q = $db->prepare("SELECT id, a, b, CONCAT(c1, "|", c2, "|", c3) AS c, d FROM mytable WHERE id = 1");
$rows = $q->fetchAll();

$data = [];

foreach($rows as $row){
    $explC = explode("|", $row['c']);

    $cValues = [];
    foreach($explC as $key => $c){
       $cValues["c".($key+1)] = $c;
    } 

    $data['data'][] = [
      "id" => $row['id'],
      "c" => $cValues
    }
}

echo json_encode($data);

edited Mar 30, 2016 at 12:32

answered Mar 30, 2016 at 11:58

Daan

12.3k6 gold badges36 silver badges51 bronze badges

1 Comment

Daan Over a year ago

Could you be more specific?

Collectives™ on Stack Overflow

Combining mysql columns to get nested json result

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related