Muhammad Muqorrobin Muhammad Muqorrobin - 1 month ago 5
JSON Question

json array in the array sql select

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 :
enter image description here

Answer

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