creating a json API request response from data stored in MySql

Question

I have developed a function in my Codeigniter web site, to request data through an API call to retrieve timeline data stored in a mysql table. My json respond is not coming out correctly.

I need to create a json response to a $.getJSON, I done this in php . Below is the code that I wrote to get data from mysql and create a json response.

public function index_get() {

            $rs = mysql_query("SELECT headline, type, text, media, credit, caption FROM media"); 

            $timeline = '
                { 
                    "@headline": "' . $row[ 'headline' ] . '",
                    "@type": "' . $row[ 'type' ] . '",
                    "@text": "' . $row[ 'text' ] . '",            
                    "asset": {
                        "@media": "' . $row[ 'type' ] . '",
                        "@credit": "' . $row[ 'type' ] . '",
                        "@caption": "' . $row[ 'type' ] . '",            
             ';            

            $newsdate = mysql_query("SELECT startDate, endDate, headline, text, tag, media, thumbnail, credit, caption FROM news"); 

            while( $row = mysql_fetch_array( $newsdate ) ){ 
                    $newsitem[] = array( 
                        'startDate'=> $row[ 'startDate' ], 
                        'endDate' => $row[ 'endDate' ],             
                        'headline' => $row[ 'headline' ], 
                        'text' => $row[ 'text' ], 
                        'tag' => $row[ 'tag' ],
                        'asset' => array(
                            'media' => $row[ 'media' ],
                            'thumbnail' => $row[ 'thumbnail' ],
                            'credit' => $row[ 'credit' ],
                            'caption' => $row[ 'caption' ]                
                    )); 

            $row1 = mysql_query("SELECT startDate, endDate, headline, tag FROM era"); 

            $era = '
                "era": {
                    "@startDate": "' . $row1[ 'startDate' ] . '",
                    "@endDate": "' . $row1[ 'endDate' ] . '",
                    "@headline": "' . $row1[ 'headline' ] . '",            
                    "@tag": "' . $row1[ 'tag' ] . '"            
            ';            

            $row2 = mysql_query("SELECT startDate, endDate, headline, value FROM chart"); 

            $chart = '
                 "chart": {
                    "@startDate": "' . $row2[ 'startDate' ] . '",
                    "@endDate": "' . $row2[ 'endDate' ] . '",
                    "@headline": "' . $row2[ 'headline' ] . '",            
                    "@value": "' . $row2[ 'value' ] . '"            
                    }
                }       
              }
            }';            

    $this->response(array(
        'timeline' => 
                $newsitem,
                    'date' => $date,        
                    'era' => $era,        
                    'chart' => $chart ), 200);     
  }

The result I like to have is as show below, the problem that I have is that I do not get all the closing { and [ in my json respond, see bold code below. What is the right way to do a timeline as below in json from data stored in mysql?

     $jsonresponse = '
          { "timeline":
           {
    "headline":"The Main Timeline Headline Goes here",
    "type":"default",
    "text":"<p>Intro body text goes here, some HTML is ok</p>",
    "asset": {
        "media":"http://yourdomain_or_socialmedialink_goes_here.jpg",
        "credit":"Credit Name Goes Here",
        "caption":"Caption text goes here"
    },
    "date": **[**
        {
            "startDate":"2011,12,10",
            "endDate":"2011,12,11",
            "headline":"Headline Goes Here",
            "text":"<p>Body text goes here, some HTML is OK</p>",
            "tag":"This is Optional",
            "asset": {
                "media":"http://twitter.com",
                "thumbnail":"optional-32x32px.jpg",
                "credit":"Credit Name Goes Here",
                "caption":"Caption text goes here"
            }
                    {,
                    {
                            "startDate":"2012,1,26",
                        "endDate":"2012,1,27",
                            "headline":"Sh*t Politicians Say",
                            "text":"<p>In true political fashion",
                            "asset": {
                                    "media":"http://youtu.be/u4XpeU9erbg",
                                    "credit":"",
                                    "caption":""
                             }
                    },
                    {
                            "startDate":"2012,1,10",
                            "headline":"Sh*t Nobody Says",
                            "text":"<p>Have you ever heard someone say</p>",
                            "asset": {
                                    "media":"http://youtu.be/f-x8t0JOnVw",
                                    "credit":"",
                                    "caption":""
                             }
                    },
    **]**,
    "era": **[**
        {
            "startDate":"2011,12,10",
            "endDate":"2011,12,11",
            "headline":"Headline Goes Here",
            "tag":"This is Optional"
        }

    **]**,
    "chart": **[**
        {
            "startDate":"2011,12,10",
            "endDate":"2011,12,11",
            "headline":"Headline Goes Here",
            "value":"28"
        }

    **]**

     }
     }
    ';

Answer 1

Build an array. When you're done, just use json_encode($array) to output as correct valid json format.

Doing this will secure your code, you wont get unexpected results - as it's much easier to deal with arrays compared to json-formatted code.

Code example

<?php
$arr = array('a' => 1, 'b' => 2, 'c' => 3, 'd' => 4, 'e' => 5);

echo json_encode($arr);

//Output: {"a":1,"b":2,"c":3,"d":4,"e":5}
?>