Fix JSON Format from MySQL/PHP

Question

In the two versions of code below, the first produces results but note that there is no end bracket at the end of the "text" array as there should be. The second output which comes from the other code example SEEMS like it should work but it fails completely. Where did I go wrong?

when I do this;

foreach($db_found->query($sql) as $row) {
        $json_array[] = 

            array('start_date' => 
                array('minute' => $row[min], 'hour' => $row[hour], 
                    'month' => $row[mo], 'day' => $row[day], 'year' => $row[yr]),
              'text' => 
                array('text'  => $row[text],
              'group' =>
                array('group' => $row[callsign])))
        ; 
    }
$data = array("events" =>  $json_array);
  echo json_encode($data);

I get this:

    {
   "events":[
      {
         "start_date":{
            "minute":"42",
            "hour":"18",
            "month":"11",
            "day":"11",
            "year":"2019"
         },
         "text":{
            "text":"BILL SWEENEY Opened the net from 65.255.143.178 on 146.655MHz, PL94.8Hz",
            "group":{
               "group":"W0WTS"
            }
         }
      },
      {
         "start_date":{
            "minute":"42",
            "hour":"18",
            "month":"11",
            "day":"11",
            "year":"2019"
         },
         "text":{
            "text":"Peculiar: Clear, 19.9F, wind: N @ 15, humidity: 54%",
            "group":{
               "group":"GENCOMM"
            }
         }
      }
   ]
}

But what I need is this:

{
   "events":[
      {
         "start_date":{
            "minute":"42",
            "hour":"18",
            "month":"11",
            "day":"11",
            "year":"2019"
         },
         "text":{
            "text":"BILL SWEENEY Opened the net from 65.255.143.178 on 146.655MHz, PL94.8Hz"
         },
         "group":{
            "group":"W0WTS"
         }
      },
      {
         "start_date":{
            "minute":"42",
            "hour":"18",
            "month":"11",
            "day":"11",
            "year":"2019"
         },
         "text":{
            "text":"Peculiar: Clear, 19.9F, wind: N @ 15, humidity: 54%"
         },
         "group":{
            "group":"GENCOMM"
         }
      }
   ]
}

I have also tried:

 foreach($db_found->query($sql) as $row) {
        $json_array[] = 

            array('start_date' => 
                array('minute' => $row[min], 'hour' => $row[hour], 
                    'month' => $row[mo], 'day' => $row[day], 'year' => $row[yr]),
            array('text' => 
                array('text'  => $row[text]),
            array('group' =>
                array('group' => $row[callsign]))
        ; 
    }
$data = array("events" =>  $json_array);
  echo json_encode($data);

But that doesn't work at all. What am I doing wrong.

For the sake of asking the question, it can be helpful if you prettify the json output you're looking for. ecosia.org/search?q=json+pretty+print — Reed
– Reed, Commented Nov 28, 2019 at 17:55
"that doesn't work at all" - What happens? Do you get any errors? — Reed
– Reed, Commented Nov 28, 2019 at 17:56
'text' => array('text' => $row[text], here you are doing the mistake, just close the parenthesis before the comma and you are good to go, like 'text' => array('text' => $row[text]), — Neel Debnath
– Neel Debnath, Commented Nov 28, 2019 at 18:37

Reed · Accepted Answer · 2019-11-28 18:23:44Z

So, I started by prettifying your JSON & PHP, then reformatting your code, to make it easier to understand the desired data hierarchy as well as the coded data hierarchy. Here's the reformat:

<?php
foreach ($db_found->query($sql) as $row) {
    $json_array[] = 
    [
        'start_date' => 
        [
            'minute' => $row['min'],
            'hour' => $row['hour'],
            'month' => $row['mo'],
            'day' => $row['day'],
            'year' => $row['yr']
        ],
        'text' => 
        [
            'text' => $row['text'],
            'group' => 
            [
                'group' => $row['callsign']
            ]
        ]
    ];
}
$data = ["events" => $json_array];
echo json_encode($data);

?>

And you can see that the 'group' array is inside the 'text' array. And after reformatting your desired JSON, I think this is what you're looking for:

And the code that produces the output I think you're looking for:

<?php
foreach ($db_found->query($sql) as $row) {
    $data["events"][] = 
    [
        'start_date' => 
        [
            'minute' => $row['min'],
            'hour' => $row['hour'],
            'month' => $row['mo'],
            'day' => $row['day'],
            'year' => $row['yr']
        ],
        'text' => 
        [
            'text' => $row['text']
        ],
        'group' => 
        [
            'group' => $row['callsign']
        ]
    ];
}

echo json_encode($data);

?>

Notes:

I chose this particular formatting because it was the easiest way to understand the hierarchy. Usually I don't put the [ on it's own line like that, but it made it easier to process in this case.
I chose [] for array definitions over array() because it's much less visual noise, thus easier to understand
I used $data["events"][] = in the loop because I think it makes it clearer what data you're defining. Alternatively, I might do $events[] = or $eventsJson[] = so the variable makes it clear what information it should be holding, then do the $data=['events'=>$eventsJson];
$row[string] is not forward compatible. See https://www.php.net/manual/en/language.types.array.php#language.types.array.foo-bar

This is exactly what I needed, not just the code but the explanation of what was done. That combined with the example taught me how to do it next time.

Er. Amit Joshi · Accepted Answer · 2019-11-28 18:07:49Z

try this code block

     $json_array[] = 

            array(
                  'start_date' => 
                               array(
                                    'minute' => $row[min], 
                                    'hour' => $row[hour], 
                                    'month' => $row[mo], 
                                    'day' => $row[day], 
                                    'year' => $row[yr]
                                  ),
                   'text' => 
                           array('text'  => $row[text]), // this is the change
                   'group' =>
                           array('group' => $row[callsign]) // so here matching bracket must be maintain 
               );

OUTP Structure is your desired one with null data because i don't have looping data

{
  "events": [
    {
      "start_date": {
        "minute": null,
        "hour": null,
        "month": null,
        "day": null,
        "year": null
      },
      "text": {
        "text": null
      },
      "group": {
        "group": null
      }
    }
  ]
}

Collectives™ on Stack Overflow

Fix JSON Format from MySQL/PHP

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related