Formatting JSON data from MySQL table

[burnoutbob]

Hi there.

 

I'm hoping someone can help me with this.

 

I'm a complete novice with regard to PHP and even more so when it comes to Javascript/JSON/AJAX etc. I'm a designer, I make things pretty for a living But I've recently had to do some custom development work for a client, and I've got so far, and now I'm stuck.

 

I have created a simple admin interface from which it is possible to add, edit and delete records in a table in MySQL. The table contains the fields 'headline', 'type', 'startDate', 'text', 'media', 'credit' and 'caption'.

 

I'm trying to pull this data out of MySQL and into JSON to be saved as a file for use in other applications/scripts. However, one of the scripts is VERY picky about formatting, and I'm not really able to do anything about that. So my only choice is to try and shoehorn this data into the correct format.

 

So far, I've had no joy.

 

Here's my code - note, this is being built within Joomla, but aside from the db connection, it's not using any of the Joomla framework.

 

$table = JRequest::getString('table');

$db = JFactory::getDBO();

$query = 'SELECT  type, headline, startDate, text, media, caption, credits FROM #__timeline_entries'.$table.'';

$db->setQuery($query);

$list = $db->loadObjectList();

$jsondata = json_encode($list);

$datafile="components/com_timeline/data/data.json";
  if(!file_exists($datafile))

  {
    fopen($datafile,"w");
  } 

  $fp = fopen($datafile, 'w');

  fwrite($fp, $jsondata);

  fclose($fp);

 

So far, this does half the job. It pulls the data out, encodes it to JSON and saves it to disk, but I really need to format the data as follows (where "value" is the data from the db);

 

Note, there will be potentially unlimited records within the "date" subset.

{
"timeline":
{
    "headline":"value",
    "type":"default",
    "startDate":"value",
    "text":"value",
    "asset":
               {
                   "media":"value",
                   "credit":"value",
                   "caption":"value"
               },
    "date": [
        {
            "startDate":"value",
            "type":"",
            "headline":"value",
            "text":"value",
            "asset":
               {
                   "media":"value",
                   "credit":"value",
                   "caption":"value"
               }
        },
        {
            "startDate":"value",
            "type":"",
            "headline":"value",
            "text":"value",
            "asset":
               {
                   "media":"value",
                   "credit":"value",
                   "caption":"value"
               }
        },
        {
            "startDate":"value",
            "type":"",
            "headline":"value",
            "text":"value",
            "asset":
            {
                "media":"value",
                "credit":"value",
                "caption":"value"
            }
        },
    ]
}
    }

 

Each set of data is a single row from the data base. The single set outside of the "data" subset is defined by having the 'type' field equal to 'default'. Only one row will have type set to default.

 

As I said, the above code is halfway there, but the JSON it creates is unusable by the third party script it's being fed to.

 

Can anyone help me out? As I mentioned, I'm still very much learning as I go. Any help will be very much appreciated.

 

Thank you

[requinix]

So what does it output?

[smoseley]

Something like this should work:

...

$list = $db->loadObjectList();

$len = count($list);

$data = array();

for ($i = 0; $i < $len; $i++) {
    $temp = (array) $list[$i];

    $temp["asset"] = array(
        "media" => $temp["media"],
        "credit" => $temp["credits"],
        "caption" => $temp["caption"]
    );
    unset($temp["media"]);
    unset($temp["credits"]);
    unset($temp["caption"]);

    if ($temp["type"] == "default") {
        $data = $list[$i];
        unset($list[$i]);
    } else {
        $list[$i] = $temp;
    }
}
$data["data"] = &$list;

$jsondata = json_encode($data);

[burnoutbob]

@smoseley,

 

That is perfect, thank you!

 

It needs a few minor adjustments, but you've just made my weekend!

 

Thank you very much sir!

[smoseley]

Cheers, bud, glad I could help!!!