将mysql查询结果导入json

Question

i am trying to get data from remote mysql database and importing it to json the connection is ok, the problem is that the result that finally echoed from json is not normal, this is my php code

<?php

require("config.inc.php");
$query = "Select * FROM comments";
$res = mysql_query($query);
$rows = mysql_fetch_assoc($res);

if ($rows) {
    $response["success"] = 1;
    $response["message"] = "Post Available!";
    $response["posts"]   = array();
    foreach ($rows as $row) {
        $post             = array();
        $post["username"] = $row["username"];
        $post["title"]    = $row["title"];
        $post["message"]  = $row["message"];

        //update our repsonse JSON data
        array_push($response["posts"], $post);
    }

    // echoing JSON response
   echo json_encode($response);

and this is the result echoed to the page:

{"success":1,"message":"Post Available!","posts":[{"username":"1","title":"1","message":"1"},{"username":"r","title":"r","message":"r"},{"username":"t","title":"t","message":"t"},{"username":"t","title":"t","message":"t"}]} 

while my database table "comments" contain these data:

post_id     username    title           message
1           reda        test title 2    test message xxx
2           reda2       title 2         message 2

please help thanks.

Answer 1

You are only returning 1 row / array by using

$rows = mysql_fetch_assoc($res);

So when you do

foreach ($rows as $row) {

you are trying to get a multidimensional array loop, from a single array. That is why each posts value is the 1st character from each column from the 1 returned row/array.

You need to build your $rows array. Try changing

$rows = mysql_fetch_assoc($res);

to

$rows = array();
while($result = mysql_fetch_assoc($res)){
  $rows[] = $result;
}