Getting data from a single table into multiple json arrays

Question

I'm trying to get data from a single table using multiple queries. Suppose I have the following table:

MY TABLE:

I'm trying to get PARA_NUMBER 1 and PARA_NUMBER 2 and combining them using this query:

(SELECT * FROM `t_sen` WHERE `PARA_NUMBER` = 1) 
UNION 
(SELECT * FROM `t_sen` WHERE `PARA_NUMBER` = 2)

And then I get the results in JSON format which looks something like this:

[
    {
        "ID": "1",
        "PARA_NUMBER": "1",
        "TEXT": "Hello"
    },
    {
        "ID": "2",
        "PARA_NUMBER": "1",
        "TEXT": "how"
    },
    // rest of the rows

    {
        "ID": "6",
        "PARA_NUMBER": "2",
        "TEXT": "Hope"
    },
    {
        "ID": "7",
        "PARA_NUMBER": "2",
        "TEXT": "you're"
    },
    // rest of the rows
]

However, I want something like this:

[
    "1": {
            "ID": "1",
            "PARA_NUMBER": "1",
            "TEXT": "Hello"
         },
         {
            "ID": "2",
            "PARA_NUMBER": "1",
            "TEXT": "how"
         },
         // rest of the rows

    "2": {
            "ID": "6",
            "PARA_NUMBER": "2",
            "TEXT": "Hope"
         },
         {
            "ID": "7",
            "PARA_NUMBER": "2",
            "TEXT": "you're"
         },
         // rest of the rows
]

Where every PARA_NUMBER have their own branches. I'm using very simple PHP code to output the JSON data:

$myquery = "
        (SELECT * FROM t_sen WHERE PARA_NUMBER = 1)
        UNION
        (SELECT * FROM t_sen WHERE PARA_NUMBER = 2)
    ";

$query = mysqli_query($conn, $myquery);

    if (!$query) {
        echo mysqli_error($conn);  //You need to put $conn here to display error.
        die();
    }

    $data = array();

    while($row = mysqli_fetch_assoc($query)) {
        $data[] = $row;
    }

    echo json_encode($data, JSON_PRETTY_PRINT);

You will need to post process in your application framework. What language are you using? — Nick
– Nick, Commented Jan 16, 2019 at 7:33
I'm using PHP and MySQL. It's just simple getting the results from the table and printing it out in a JSON format. — Marwan Ansari
– Marwan Ansari, Commented Jan 16, 2019 at 7:41

Nick · Accepted Answer · 2019-01-16 07:48:59Z

2

If you're using the mysqli interface in PHP, you could do something like this (assuming your connection is called $conn):

$result = $conn->query('(SELECT * FROM `t_sen` WHERE `PARA_NUMBER` = 1) 
UNION 
(SELECT * FROM `t_sen` WHERE `PARA_NUMBER` = 2)');
$out = array();
while ($row = $result->fetch_assoc()) {
    $out[$row['PARA_NUMBER']][] = $row;
}
echo json_encode($out);

answered Jan 16, 2019 at 7:48

Nick

147k23 gold badges67 silver badges106 bronze badges

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

1 Comment

Marwan Ansari Over a year ago

Thanks a lot this worked perfectly! :) I really appreciate your help.

Zim84 · Accepted Answer · 2019-01-17 23:04:41Z

1

Even though the answer of Nick already works, I don't see the need to use UNION. Why don't you use IN instead?

$result = $conn->query('SELECT * FROM `t_sen` WHERE `PARA_NUMBER` IN (1,2)');
$out = array();
while ($row = $result->fetch_assoc()) {
    $out[$row['PARA_NUMBER']][] = $row;
}
echo json_encode($out);

answered Jan 17, 2019 at 23:04

Zim84

3,5672 gold badges39 silver badges42 bronze badges

1 Comment

Marwan Ansari Over a year ago

Wow I didn't knew I can do that. Thanks a lot for this! I really appreciate this :)

Collectives™ on Stack Overflow

Getting data from a single table into multiple json arrays

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related