Getting data from database showing it as json response

Question

In mysql table I have set of record. I want to fetch them wanna showlike below json response.

"results":[

    {
        "timestamp":"2014-03-04 17:26:14",
        "id":"440736785698521089",
        "category":"sports",
        "username":"chetan_bhagat",
        "displayname":"Chetan Bhagat"
     }

I am getting above values i.e. timestamp,id,category,username from database. How can I show the result in form of json response like above?

UPDATE:

I fetch data in this way:

$con = mysqli_connect('127.0.0.1', 'root', '', 'mysql');
            if (mysqli_connect_errno())
            {
                echo "Failed to connect to MySQL: " . mysqli_connect_error();
                return;
            }   
            $today = date("Ymd");           

            $result = mysqli_query($con,"SELECT url,img_url,sentiment,title,category from frrole_cateogry_article where category='".$category."' AND today <= '".$today."' AND title != '' AND img_url != '' order by url desc limit 3 ");
            while ($row = @mysqli_fetch_array($result))
            {
                $url = $row['url'];
                $img_url = $row['img_url'];
                $screen_name = $row['screen_name'];
            }

Check this - http://stackoverflow.com/questions/6818441/sql-server-select-to-json-function — TechMaze, Mar 04 '14 at 18:02
@TechMaze, that question is about Microsoft SQL Server, not MySQL. — Bill Karwin, Mar 04 '14 at 18:07

Bill Karwin · Answer 1 · 2014-03-04T18:48:49.863

1

Fetch your results in the traditional way:

$data['results'] = $stmt->fetchAll(PDO::FETCH_ASSOC);

Then convert it all to JSON. Bam!

$results = json_encode($data);

This is far easier than trying to format JSON in your SQL query.

See for more details:

Since you're use mysqli instead of PDO, and using it in a procedural fashion, you'd fetch rows a different way:

while ($data['results'][] = mysql_fetch_assoc($result));

Then you can json_encode() as I showed above.

edited Mar 04 '14 at 18:48

answered Mar 04 '14 at 18:04

Bill Karwin

538,548
86
673
828

Thanks Bill, do you have any edit after my UPDATE in question? Your answer would be marked correct – user2129623 Mar 04 '14 at 18:13

score 1 · Accepted Answer · answered Mar 04 '14 at 18:16

1

try this...

        $con = mysqli_connect('127.0.0.1', 'root', '', 'mysql');
        if (mysqli_connect_errno())
        {
            echo "Failed to connect to MySQL: " . mysqli_connect_error();
            return;
        }   
        $today = date("Ymd");           

        $result = mysqli_query($con,"SELECT url,img_url,sentiment,title,category from frrole_cateogry_article where category='".$category."' AND today <= '".$today."' AND title != '' AND img_url != '' order by url desc limit 3 ");
        while ($row = @mysqli_fetch_array($result))
        {
            json_encode($row);             
        }

answered Mar 04 '14 at 18:16

Nishant Solanki

2,119
3
19
32

thanks, can you please consider case where some of the value s coming from different table and which needs to be encoded in json. like `screen_name` and `id` are from `frrole_cateogry_user` table and they also be included in same json response. Your help would be appreciated – user2129623 Mar 04 '14 at 18:29
ohh yes sure.. you can use `mysqli Join queries` to join these both tables, so you will get the another table fields in `json response` as well... Take an example like `select * from table A join table B on (A.id = B.fk_id)......` – Nishant Solanki Mar 05 '14 at 05:07

Getting data from database showing it as json response

2 Answers2