doutui8842
doutui8842
2014-12-10 17:36

将mysql select结果转换为json(数组数组)

已采纳

I need to encode a table content to JSON in order to insert it into a file.

The output has to be as following :

{
"name1":[{"id":"11","name":"name1","k1":"foo","k2":"bar"}],
"name2":[{"id":"12","name":"name2","k1":"foo","k2":"bar"}],
}

Indeed, each JSON "line" corresponds to the content of the mysql row and the name of each JSON array is the name of the 'name' column.

The only thing I could manage for the moment is this :

      $return_arr = array();
$sql = "SELECT * FROM bo_appart"; 
$result = mysql_query($sql) or die(mysql_error());

$index = 0;
while ($row = mysql_fetch_assoc($result)) {
$return_arr[$index] = $row;
 $index++;
}

echo json_encode($return_arr);

And here is the output I get :

[
{"id":"11","name":"name1","k1":"foo","k2":"bar"},
{"id":"12","name":"name2","k1":"foo","k2":"bar"},
]

Thanks a lot !!!

UPDATED

Working code :

$return_arr = array();
$sql = "SELECT * FROM bo_appart"; 
$result = mysql_query($sql) or die(mysql_error());

while ($row = mysql_fetch_assoc($result)) {
$return_arr[ $row['nom_appart'] ][] = $row;
}

echo json_encode($return_arr);

}

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

4条回答

  • doujianwei8217 doujianwei8217 7年前

    This loop is enough, to create the desired JSON:

    $return_arr = array();
    while ($row = mysql_fetch_assoc($result)) {
        $return_arr[$row['name']][] = $row; #or $return_arr[$row['name']] = [$row];
    }
    echo json_encode($return_arr);
    
    点赞 评论 复制链接分享
  • doudao2407 doudao2407 2年前

    I was working on it a lot of time and Create mash/mysql-json-serializer package.

    https://github.com/AndreyMashukov/mysql-json-serializer

    You can select Json_array of json_objects and etc. It support ManyToMany, oneToMany, manyToOne relations

    SELECT JSON_ARRAYAGG(JSON_OBJECT('id',est_res.est_id,'name',est_res.est_name,'advert_groups',(SELECT JSON_ARRAYAGG(JSON_OBJECT('id',adg.adg_id,'name',adg.adg_name)) FROM advert_group adg INNER JOIN estate est_2 ON est_2.est_id = adg.adg_estate WHERE est_2.est_id = est_res.est_id))) FROM (SELECT * FROM estate est LIMIT 1 OFFSET 2) est_res
    
    点赞 评论 复制链接分享
  • douyin2962 douyin2962 7年前
    <?php
    
    $sql = "SELECT * FROM bo_appart"; 
    $result = mysql_query($sql) or die(mysql_error());
    
    while ($row = mysql_fetch_array($result)) {
    $return_arr[] = $row;
    }
    
    echo json_encode($return_arr);
    
    ?>
    
    点赞 评论 复制链接分享
  • douguwo2275 douguwo2275 7年前

    You were close. I noticed you want final output to be an object not an array, because the outer brackets are {} not []. So you need a different object type, and you need to use each row's name as the key for storing that row.

    $return_obj = new stdClass();
    $sql = "SELECT * FROM bo_appart"; 
    $result = mysql_query($sql) or die(mysql_error());
    
    while ($row = mysql_fetch_assoc($result)) {
        $name = $row['name'];
        $return_obj->$name = [$row]; // for PHP < 5.4 use array($row)
    }
    
    echo json_encode($return_obj);
    
    点赞 评论 复制链接分享