how to encode multiple rows from mysql into json using php

Question

I am trying to encode all the rows of data i get from the DB into JSON and then process it at the client side but i can't seem to get it to work..My code is below

function getTopic($conn){
    $response = array("error" => 0);
    $qry = "SELECT original_title, content, time FROM topic WHERE vis = 1";
    $result = $conn->prepare($qry);
    $result->execute();
    if($result->rowCount() > 0){
        $output = $result->fetchall();
        $response['text'] = $output['original_title'];
        $response['test'] = $output['content'];
        return json_encode($response);
        //return $output;
    }

Then i try to print the var_dump($response) but i get null values.. Although if i var_dump($output) i get all the rows in an array..accessing the array is the problem here now..i think

NB: i am using PDO

fanfavorite · Accepted Answer · 2013-09-14 01:49:24Z

1

The problem is the $output is an array that you need to go through. Like:

function getTopic($conn){
    $response = array("error" => 0);
    $qry = "SELECT original_title, content, time FROM topic WHERE vis = 1";
    $result = $conn->prepare($qry);
    $result->execute();
    if($result->rowCount() > 0){
        $output = $result->fetchall();
        foreach ($output as $o){
           $response['text'] = $o['original_title'];
           $response['test'] = $o['content'];
        }
        return json_encode($response);
    }
}

This is for the last response, but if you want all, do:

function getTopic($conn){
    $response = array('error'=>0);
    $qry = "SELECT original_title, content, time FROM topic WHERE vis = 1";
    $result = $conn->prepare($qry);
    $result->execute();
    if($result->rowCount() > 0){
        $output = $result->fetchall();
        foreach ($output as $o){
           $response[] = array('text'=>$o['original_title'],'test'=>$o['content']);
        }
        return json_encode($response);
    }
}

If you are only want one row add a limit to your MySQL statement.

edited Sep 14, 2013 at 1:49

answered Sep 14, 2013 at 1:37

fanfavorite

5,2191 gold badge35 silver badges62 bronze badges

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

4 Comments

Amal Over a year ago

The OP does not mention what he uses - mySQLi or PDO.

1baga Over a year ago

thanks for responding...but it is still giving the same error..i.e Notice: Undefined index: original_title in C:\wamp\www\api\include\functions.php on line 58 Notice: Undefined index: original_title in C:\wamp\www\api\include\functions.php on line 59 the lines where '$response['text'] = $output['original_title']; $response['test'] = $output['content'];'

user1056677 Over a year ago

The above $response will just contain the last $output record. Might want to use something like $response[] = array('text'=>$output['original_title'],'test'=>$output['content']);

fanfavorite Over a year ago

Sorry I made a mistake in the code, try that now. This will only get the last row as the above user mentioned. You can make an array of responses if you want like above.

Shahid · Accepted Answer · 2013-09-14 01:44:09Z

1

$output is array of results. use a loop or if only one row u need do this:

function getTopic($conn){
$response = array("error" => 0);
$qry = "SELECT original_title, content, time FROM topic WHERE vis = 1";
$result = $conn->prepare($qry);
$result->execute();
if($result->rowCount() > 0){
    $output = $result->fetchall();
    $response['text'] = $output[0]['original_title'];
    $response['test'] = $output[0]['content'];
    return json_encode($response);
    //return $output;
}

edited Sep 14, 2013 at 1:44

answered Sep 14, 2013 at 1:32

Shahid

2656 silver badges14 bronze badges

2 Comments

fanfavorite Over a year ago

This would be if you only want the first row.

Shahid Over a year ago

true! if there is only one row.

Collectives™ on Stack Overflow

how to encode multiple rows from mysql into json using php

2 Answers 2

4 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related