Proper way to create JSON data with PHP/MySQL

Question

Updated based on answers below:

Based on the answers below, I now have the following PHP script:

header('Content-type:application/json');

function getdata($the_query)
{
    $connection = mysql_connect('server', 'user', 'pass') or die (mysql_error());
    $db = mysql_select_db('db_name', $connection) or die (mysql_error());

    $results = mysql_query($the_query) or die(mysql_error());

    header('Content-type:application/json');

    $the_data['rss']['channels']['title'] = $title;
    $the_data['rss']['channels']['link'] = $link;
    $the_data['rss']['channels']['description'] = $description;

    while($row = mysql_fetch_array($result))
    {
        extract($row);

        $the_data['rss']['channels']['items']['title'] = $item_title;
        $the_data['rss']['channels']['items']['link'] = "$item_link;
        $the_data['rss']['channels']['items']['date'] = $item_date;
        $the_data['rss']['channels']['items']['description'] = $item_description;
    }   

    mysql_close($connection);

    return json_encode($the_data);
}

Which returns the following:

{
    "rss":
    {
        "channels":
        {
            "title":"title goes here",
            "link":"link goes here",
            "description":"description goes here",
            "items":
            {
                "title":"'title goes here",
                "link":"link goes here",
                "date":"date goes here",
                "description":"description goes here"
            }
        }
    }
}

It should be returning many items based on the number of rows returned from the database, why am I only getting 1 items?

You fetch the data from MySQL into an array and then you run json_encode($your_array) and you're done. — Michael J.V.
– Michael J.V., Commented Jun 2, 2011 at 10:03
Your output will not work btw. You have several items elements. I assume when this JSON is parsed, only the last one will "survive". You cannot have multiple entries with the same key. It seems you wanted to create one items entry with each of them being an element of the array... — Felix Kling
– Felix Kling, Commented Jun 2, 2011 at 10:07

jerjer · Accepted Answer · 2011-06-02 10:22:32Z

10

try this one:

<?php
$channel = array(
     'title' => 'title goes here',
     'link' => 'link here',
     'description' => 'description',
     'items' => array()
);
while($row = mysql_fetch_array($results))
{
    extract($row);
    $channel['items'][] = array(
        'title' => $title,
        'link' => $link,
        'guid' => $guid,
        'pubDate' => $date,
        'description' => $description
    );
}   
$channels = array($channel);
$rss = (object) array('rss'=> array('channels'=>$channels));
$json = json_encode($rss);
echo $json;

?>

answered Jun 2, 2011 at 10:22

jerjer

8,77634 silver badges36 bronze badges

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

Comments

Ben · Accepted Answer · 2011-06-02 10:09:12Z

1

Yes it should be fairly simple, something along the lines of

$the_data['rss']['channels']['title'] = $title;
$the_data['rss']['channels']['link'] = $link;
$the_data['rss']['channels']['description'] = $desc;

and then inside your while loop you can have,

$the_data['rss']['channels']['items'][] = $row;

and finally encode the array,

json_encode($the_data);

answered Jun 2, 2011 at 10:09

Ben

1,53511 silver badges19 bronze badges

1 Comment

Ben Over a year ago

@oshirowanen Obviously you are only getting one item since you are overwriting items each iteration of the loop. Why not use the middle line of code I posted?

Collectives™ on Stack Overflow

Proper way to create JSON data with PHP/MySQL

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related