CODEWITHSUNDEEP
phpmysqlarraysjson
Hannibal Burr
Hannibal Burr

Reputation: 49

encode a perfect json

i have this code

$md_query = "SELECT * FROM table ORDER BY id ASC";
$md_result = mysql_query($md_query, $con);

header('Cache-Control: no-cache, must-revalidate');
header('Content-type: application/json');

while($md_row=mysql_fetch_array($md_result))
    $data_row = array(
        'id' => $md_row['id'],
        'type' => $md_row['type'],
        'title' => $md_row['title'],
        'content' => $md_row['content'],
        'author' => $md_row['postedby'],
        'post_date' => $md_row['posteddate'],
        'publish' => $md_row['publish']
    );

print json_encode($data_row); `

but I only show 1 record... does anyone how to fix this?

Upvotes: 0

Views: 364

Answers (5)

canon
canon

Reputation: 41675

Your while loop doesn't include the print statement... so, it loops through all the records, completely resetting $data_row each time, and then prints it once when it's done.

To include multiple statements you need to use { and } to encapsulate the block.

Upvotes: 5

Waschman
Waschman

Reputation: 11

Why make it so overly complicated to display all the rows, or wrap with {}? Just make $data_row a multi-dimensional array and json_encode() will do it for you with []:

while($md_row=mysql_fetch_array($md_result))
$data_row[] = array(   // please note I added [] !
    'id' => $md_row['id']
    ,'type' => $md_row['type']
);

print json_encode($data_row);

Prints e.g:

[{"id":"3","type":"One"},{"id":"8","type":"Two"},{"id":"9","type":"Three"},{"id":"10","type":"Four"}]

If you convert that JSON string back to array it looks like this:

Array(
[0] => Array
    (
        [id] => 3
        [type] => One
    )

[1] => Array
    (
        [id] => 8
        [type] => Two
    )

[2] => Array
    (
        [id] => 9
        [type] => Three
    )

[3] => Array
    (
        [id] => 10
        [type] => Four
    ))

Upvotes: 1

outis
outis

Reputation: 77400

The code sample only prints $data_row after the loop has finished, at which point $data_row holds the last row from the result. You need to either collect the rows into a data structure, JSON-encode and print it, or print each row with each iteration of the loop.

...
header('Cache-Control: no-cache, must-revalidate');
header('Content-type: application/json');

$md_query = $db->prepare(
    "SELECT id, type, title, content, postedby AS author, 
            posteddate AS post_date, publish
       FROM posts 
       ORDER BY id ASC");

try {
    $md_query->execute();
    echo json_encode($md_query->fetchAll(PDO::FETCH_ASSOC));
} catch (PDOException $exc) {
    ...
}

Note that as this fetches all rows from the table, it will likely exhaust either the maximum allowed memory or run time for the script. If the memory limit is a problem, change the line of code that's responsible for displaying the result so as to print the result a chunk at a time. For example:

    ...
    $md_query->setFetchMode(PDO::FETCH_ASSOC);

    echo '[';
    $last = $md_query->rowCount()-1;
    foreach ($md_query as $i => $post) {
        echo json_encode($post);
        if ($i < $last) {
            echo ", ";
        }
    }
    echo ']';
} catch (PDOException $exc) {
    ...

Upvotes: -1

kontur
kontur

Reputation: 5220

You need to encapsulate your row-records like {row1: {a:b, b:c}, row2:{e:f, g:h}} 

$json = '{';
while($md_row=mysql_fetch_array($md_result)) {
$data_row = array(
    'id' => $md_row['id'],
    'type' => $md_row['type'],
    'title' => $md_row['title'],
    'content' => $md_row['content'],
    'author' => $md_row['postedby'],
    'post_date' => $md_row['posteddate'],
    'publish' => $md_row['publish']
);
$json .= '"' . $id . '" :' . json_encode($data_row) . ','; 
// $id just as an example for the string-value pair
}
$json = substr($json, 0, -1); // remove comma after last row
$json .= '}';
echo $json;

For more examples also see:

http://json.org/

http://json.org/example.html

Upvotes: -2

Ted Hopp
Ted Hopp

Reputation: 234795

You are looping through your data and setting $data_row to a new array for each row, but you aren't doing anything with it until you exit the loop.

Upvotes: 1

Related Questions