CODEWITHSUNDEEP
phpmysqljson
John Phelps
John Phelps

Reputation: 95

Multidimensional JSON-object through SQL

My database looks like this:

enter image description here

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

  1. Make an object for every different value in the column "MONTH"
  2. 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);

Upvotes: 1

Views: 183

Answers (1)

bassxzero
bassxzero

Reputation: 5041

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);

Upvotes: 2

Related Questions