How to create a JSON arrray inside array in PHP using MySQL Database

Question

I need PHP code to save data to a JSON file.

Needed result : "calendar":[ { "Pariharam":"pariharam", "Palamozhi":"palamozhi", "horoscopes":[ { "HoroscopeID":"daily_horoscope_id", "Rasiname":"Rasi", "ShortDescription":"shot_desc", "LongDescription":"long_desc" }, } ]

My Code :

$sql=mysql_query("select * from daily_calendar_tbl"); 
$response = array();
$posts = array();
//$result=mysql_query($sql);
while($row=mysql_fetch_array($sql)) 
{ 
$caldate=$row['cal_date']; 
$tamilyear=$row['tamil_year']; 
$tamilmonth=$row['tamil_month']; 
$tamildate=$row['tamil_date']; 
$gtms=$row['gt_mrg_str']; 
$gtme=$row['gt_mrg_end']; 
$gtes=$row['gt_eve_str']; 
$gtee=$row['gt_eve_end']; 
$ggtms=$row['gow_mrg_str']; 
$ggtme=$row['gow_mrg_end']; 
$ggtes=$row['gow_eve_str']; 
$ggtee=$row['gow_eve_end']; 
$rst=$row['ragu_str']; 
$ret=$row['ragu_end']; 
$gst=$row['guli_str']; 
$get=$row['guli_end']; 
$yst=$row['yema_str']; 
$yet=$row['yema_end']; 
$sut=$row['sur_udha']; 
$suras=$row['sur_astha']; 
$yogam=$row['yogam']; 
$chantrs=$row['chandrashtama']; 
$soolam=$row['soolam']; 
$phariharam=$row['pariharam']; 
$palamozhi=$row['palamozhi']; 


$selrasi=mysql_query("select * from daily_horoscope_tbl where horoscope_date='$caldate'");

while($rasidata=mysql_fetch_array($selrasi)){
    $horoid=$rasidata["daily_horoscope_id"];
    $rasiname=$rasidata["rasi"];
    $shortdesc=$rasidata["shot_desc "];
    $long_desc=$rasidata["long_desc"];  
    $horoscopedata[]=array('HoroscopeID'=> $horoid, 'Rasiname'=> $rasiname, 'ShortDescription'=>$shortdesc,'LongDescription'=> $long_desc);
}

$horoscopesres=$horoscopedata;

$posts[] = array('CalendarDate'=> $caldate, 'TamilYear'=> $tamilyear, 'TamilMonth'=>$tamilmonth,'TamilDate'=> $tamildate, 'GoodTimeMorningStart'=> $gtms, 
'GoodTimeMorningEnd'=>$gtme,'GoodTimeEveningStart'=> $gtes, 'GoodTimeEveningEnd'=> $gtee, 'GowriGoodTimeMorningStart'=>$ggtms,'GowriGoodTimeMorningEnd'=> $ggtme, 'GowriGoodTimeEveningStart'=> $ggtes,
 'GowriGoodTimeEveningEnd'=>$ggtee,'RahuStartTime'=> $rst, 'RahuEndTime'=> $ret, 'GulikaiStartTime'=>$gst,'GulikaiEndTime'=> $get, 'YamagandamStartTime'=> $yst, 
 'YamagandamEndTime'=>$yet,'SuryaUdhayamTime'=> $sut, 'SuryaAsthamanam'=> $suras, 'Yogam'=>$yogam,'Chandrashtama'=> $chantrs, 'Soolam'=> $soolam, 'Pariharam'=>$phariharam,'Palamozhi'=>$palamozhi,'horoscopes'=>$horoscopesres);

} 

$response['calendardata'] = $posts;

$fp = fopen('articles.json', 'w');
fwrite($fp, json_encode($response));
fclose($fp);

you can convert an array to json by passing the array variable in `json_encode()` function — Vivek Singh, Mar 19 '16 at 09:27
Please provide the code you have tried out so that we can help you better. — gmuraleekrishna, Mar 19 '16 at 09:27

score 0 · Answer 1 · answered Mar 19 '16 at 09:33

your json is not even a valid json try this

{
    "calendar": {
        "Pariharam": "pariharam",
        "Palamozhi": "palamozhi",
        "horoscopes": [{
            "HoroscopeID": "daily_horoscope_id",
            "Rasiname": "Rasi",
            "ShortDescription": "shot_desc",
            "LongDescription": "long_desc"
        }]
    }
}

secondly where do you want to pass array if you want horoscopes to be an array than it would look like

{
    "calendar": {
        "Pariharam": "pariharam",
        "Palamozhi": "palamozhi",
        "horoscopes": [{
            "HoroscopeID": "daily_horoscope_id",
            "Rasiname": "Rasi",
            "ShortDescription": "shot_desc",
            "LongDescription": "long_desc"
        }, {
            "HoroscopeID": "daily_horoscope_id",
            "Rasiname": "Another",
            "ShortDescription": "shot_desc",
            "LongDescription": "long_desc"
        }]
    }
}

score 0 · Answer 2 · answered Mar 19 '16 at 09:46

Something like this will work for you .. you may have to do foreach loop for other dynamic elements.. You can have an idea from here. If you post your other codes i can update my answer.

 $Pariharam = "pariharam";
    $Palamozhi="palamozhi";
    $horoscope= array('HoroscopeID'=>'daily_horoscope_id','Rasiname'=>'Rasi','ShortDescription'=>'shot_desc','LongDescription'=>'long_desc');

    $calender=array();


      $calender['calender']['Pariharam']=$Pariharam;
      $calender['calender']['Palamozhi']=$Pariharam;
      $calender['calender']['horoscope']=$horoscope;



    print_r(json_encode($calender));exit;

Output

{  
   "calender":{  
      "Pariharam":"pariharam",
      "Palamozhi":"pariharam",
      "horoscope":{  
         "HoroscopeID":"daily_horoscope_id",
         "Rasiname":"Rasi",
         "ShortDescription":"shot_desc",
         "LongDescription":"long_desc"
      }
   }
}

How to create a JSON arrray inside array in PHP using MySQL Database

2 Answers2