数组中的json数组sql选择

Question

i have a problem with seelct query .. i want to display it in json array in the array.. here is my code

i have query like this :

<?php 
$server_name="localhost";
$mysql_user="root";
$db_name ="db_mtma";
$mysql_pass ="";

mysql_connect($server_name, $mysql_user,"");
mysql_select_db($db_name);
// $con = mysqli_connect($server_name,$mysql_user,$mysql_pass,$db_name);



$result = array();
$query = "SELECT id_lw,gambar_lw,nama_p,COUNT(nama_p) AS jumlah_lw_by_nama_p,nama_k,COUNT(nama_k)
            from lokasi_wisata JOIN
            provinsi as provinsi ON provinsi.IDProvinsi = lokasi_wisata.IDProvinsi JOIN
            kabupaten as kabupaten ON kabupaten.IDKabupaten = lokasi_wisata.IDKabupaten WHERE status_lw=1 GROUP BY nama_p" ;
$res = mysql_query($query);

while ($fetch = mysql_fetch_assoc($res)) {
    $result[] = $fetch;
}
// print_r($result);
echo mysql_error();
echo json_encode(array('result'=>$result));

?>

the result of json like this :

{
  "result": [
    {
      "id_lw": "22",
      "gambar_lw": "1475251768.jpg",
      "nama_p": "ACEH",
      "jumlah_lw_by_nama_p": "1",
      "nama_k": "KOTA SABANG",
      "COUNT(nama_k)": "1"
    },
    {
      "id_lw": "9",
      "gambar_lw": "1475160516.jpg",
      "nama_p": "BALI",
      "jumlah_lw_by_nama_p": "3",
      "nama_k": "BULELENG",
      "COUNT(nama_k)": "3"
    },

etc..

i want my result data as this :

 "id_lw": "9",
              "gambar_lw": "1475160516.jpg",
              "nama_p": "BALI",
              "jumlah_lw_by_nama_p": "3",
              "nama_k": {"BULELENG" "1","TABANAN" "2"} 

i want to select all nama_k and i want to know how much data in every nama_k columns

thx stackoverflow

example image :

Answer 1

resolved:

<?php 
$server_name="localhost";
$mysql_user="root";
$db_name ="db_mtma";
$mysql_pass ="";

mysql_connect($server_name, $mysql_user,"");
mysql_select_db($db_name);
// $con = mysqli_connect($server_name,$mysql_user,$mysql_pass,$db_name);



$result = array();
$query = "SELECT id_lw,gambar_lw,left(deskripsi_lw,100)deskripsi_lw,latitude_lw,longitude_lw,nama_p,provinsi.IDProvinsi id_provinsi,kabupaten.IDKabupaten id_kabupaten,COUNT(nama_p) AS jumlah_lw_by_nama_p,nama_k,COUNT(nama_k), judul_lw
from lokasi_wisata JOIN
provinsi as provinsi ON provinsi.IDProvinsi = lokasi_wisata.IDProvinsi JOIN
kabupaten as kabupaten ON kabupaten.IDKabupaten = lokasi_wisata.IDKabupaten WHERE status_lw=1 GROUP BY nama_p, nama_k, id_lw";
$res = mysql_query($query);

$n = 0;
while ($fetch = mysql_fetch_array($res)) {
    $result[$n] = $fetch;
    $n++;
}
// print_r($result);

$data = array();
echo mysql_error();
foreach ($result as $r) {
    $data[$r['nama_p']]['nama_p'] = $r['nama_p'];
    $data[$r['nama_p']]['list_kota'] [$r['nama_k']] ['list_lw'][$r['id_lw']] ['nama_kabupaten'] = $r['nama_k'];
    $data[$r['nama_p']]['list_kota'] [$r['nama_k']] ['list_lw'][$r['id_lw']] ['judul_lw'] = $r['judul_lw'];
    $data[$r['nama_p']]['list_kota'] [$r['nama_k']] ['list_lw'][$r['id_lw']] ['longitude_lw'] = $r['longitude_lw'];
    $data[$r['nama_p']]['list_kota'] [$r['nama_k']] ['list_lw'][$r['id_lw']] ['latitude_lw'] = $r['latitude_lw'];
    $data[$r['nama_p']]['list_kota'] [$r['nama_k']] ['list_lw'][$r['id_lw']] ['gambar_kabupaten'] = $r['gambar_lw'];


    $data[$r['nama_p']]  ['gambar_lw'] = $r['gambar_lw'];
    $data[$r['nama_p']]  ['id_lw'] = $r['id_lw'];
    $data[$r['nama_p']]  ['nama_p'] =$r['nama_p'];
    // $data[$r['nama_p']]  ['jumlah_lw_by_nama_p'] = count($data[$r['nama_p']] [$r['nama_k']]);

}

$rs = array();

    foreach ($data as $d) {
    $n=0;


    $i = 0;
    $jumlah_lw = 0;
    foreach ($d['list_kota'] as $key => $r) {
        $u = 0;
        foreach ($r['list_lw'] as $l) {
            $rs[$d['nama_p']] ['kabupaten'] [$key][$u] ['judul_lw'] = $l['judul_lw'];
            $rs[$d['nama_p']] ['kabupaten'] [$key][$u] ['latitude_lw'] = $l['latitude_lw'];
            $rs[$d['nama_p']] ['kabupaten'] [$key][$u] ['longitude_lw'] = $l['longitude_lw'];
            $rs[$d['nama_p']] ['kabupaten'] [$key]['gambar_kabupaten'] = $l['gambar_kabupaten'];

            $u++;
            $jumlah_lw++;
        }
        $rs[$d['nama_p']] ['kabupaten'] [$key]['nama_kabupaten'] = $key;
        $rs[$d['nama_p']] ['kabupaten'] [$key]['jumlah_lw_k'] = $u;
        $i++;
    }
    $rs[$d['nama_p']] ['nama_p'] = $d['nama_p'];
    $rs[$d['nama_p']] ['id_lw'] = $d['id_lw'];
    $rs[$d['nama_p']] ['gambar_provinsi'] = $d['gambar_lw'];
    $rs[$d['nama_p']] ['jumlah_lw_p'] = $jumlah_lw;
    $n++;
}


echo json_encode(array('provinsi'=>$rs));
die();

// echo json_encode (array('provinsi'=>$data));die();