encode a perfect json

Question

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?

The mysql extension is outdated and on its way to deprecation. New code should use mysqli or PDO, both of which have important advantages, such as support for prepared statements. Don't use [`SELECT *`](http://stackoverflow.com/questions/321299); select only the columns you need. — outis, Dec 20 '11 at 19:21
You are resetting $data_row in every iteration. All you need to do is to store all the row to an array and `json_encode($final_array)` — Broncha, May 07 '12 at 18:06

canon · Answer 1 · 2016-02-18T14:54:05.313

5

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.

edited Feb 18 '16 at 14:54

answered Dec 11 '11 at 07:39

canon

40,609
10
73
97

Just add a comma on each pass. However, you'll need to ensure that you don't add a comma to the last record; trailing commas cause issues in some browsers. – canon Dec 11 '11 at 07:44
!yeahh the i mean each array `{ "id":"1", "name":"me" }, { "id":"2", "name":"you" }` i mine no comma show.. between `{},{}` – Hannibal Burr Dec 11 '11 at 07:49

score 1 · Answer 2 · answered May 07 '12 at 17:59

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
    ))

score 1 · Answer 3 · answered Dec 11 '11 at 07:41

1

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.

answered Dec 11 '11 at 07:41

Ted Hopp

232,168
48
399
521

@HannibalBurr - After the first time through the loop, just print a comma before printing the next array. You can set a flag before the loop to indicate first time through and clear it each time after the prints. – Ted Hopp Dec 11 '11 at 08:06

score -1 · Answer 4 · answered Dec 20 '11 at 19:42

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) {
    ...

score -2 · Accepted Answer · edited Dec 20 '11 at 19:40

-2

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

edited Dec 20 '11 at 19:40

outis

75,655
22
151
221

answered Dec 11 '11 at 14:07

kontur

4,934
2
36
62

an array of object should be encapsulated in []. As @user1083183 answered the final output should be `[{id:1,title:'title'},{id:2,title:'title2'}]` – Broncha May 07 '12 at 18:04
neither did the OP ask for an array of object nor do I see user1083183's answers... wrong topic? – kontur May 08 '12 at 18:36

encode a perfect json

5 Answers5