Multidimensional JSON-object through SQL

Question

My database looks like this:

I have to come up with a SQL-query which will do the following:

Make an object for every different value in the column "MONTH"
This data should be stored in an array (see the JSON-setup below)

However, I'm stuck at the beginning. Maybe I should do something with SELECT DISTINCT ... ?

My goal is the create the JSON-object as shown below.

var data = [
    "val1":[
        {"DATE":a1, "HEADER":b1, "MESSAGES":c1},
        {"DATE":a2, "HEADER":b2, "MESSAGES":c2},
        {"DATE":a6, "HEADER":b6, "MESSAGES":c6},
     ],
     "val2":[
        {"DATE":a5, "HEADER":b5, "MESSAGES":c5},
        {"DATE":a8, "HEADER":b8, "MESSAGES":c8},
     ],
     "val3":[
        {"DATE":a3, "HEADER":b3, "MESSAGES":c3},
        {"DATE":a4, "HEADER":b4, "MESSAGES":c4},
        {"DATE":a7, "HEADER":b7, "MESSAGES":c7},
     ],
];

So far I have tried this (but it's only connecting to the database):

$connect = mysqli_connect("localhost", "root", "root", "user1");

$sql = "SELECT * FROM Data";
$result = mysqli_query($connect, $sql);

$data = array();
while ($row = mysqli_fetch_array($result)) {
    $data[] =  $row; 
}

header('Content-Type: application/json');
echo json_encode($data);

Can you show us your php script, and what is exactly your question ? — Arno
– Arno, Commented Oct 13, 2017 at 15:40

bassxzero · Accepted Answer · 2017-10-13 16:01:23Z

2

You can use PHP to generate the result in the format you want instead of trying to make a complicated query.

Try this.

*note you need to replace $row['DATE'] and etc. with the correct column names.

<?php

$connect = mysqli_connect("localhost", "root", "root", "user1");

$sql = "SELECT * FROM Data";
$result = mysqli_query($connect, $sql);

$data = array();
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
    $data[$row['MONTH']][] = array(
    "DATE"=> $row['DATE'],
    "HEADER"=> $row['HEADER'],
    "MESSAGES" => $row['MESSAGES']
    ); 
}

header('Content-Type: application/json');
echo json_encode($data);

edited Oct 13, 2017 at 16:01

answered Oct 13, 2017 at 15:53

bassxzero

5,01924 silver badges36 bronze badges

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

7 Comments

Tony Chiboucas Over a year ago

One last adjustment, to meet the OP's requirement: each "month" containing an array of objects...

bassxzero Over a year ago

@TonyChiboucas pretty sure mine does that.

bassxzero Over a year ago

@TonyChiboucas 'object', in this context means JSON object. My code generates the correct JSON output without stdclass.

Tony Chiboucas Over a year ago

json_decode will turn it into: ["val1"][ ["DATE": date, "HEADER": header, "MESSAGES": messages] ]. OP asked for ["val1"] [{"DATE": date, "HEADER": header, "MESSAGES": messages} ]. Not a big deal, and just semantics. Your solution is perfect, I'm just being a stickler. ^_^

John Phelps Over a year ago

Sorry, I'm a bit new to this so I don't know what the correct names are. I hope it is clear.

|

Collectives™ on Stack Overflow

Multidimensional JSON-object through SQL

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related