How can I get json response from php below format?

Question

I have two tables, in yearselection there are two columns, and in testtable2 there are three columns based on the first table identifier used in the second table. I want to display json responce as below using php, these two tables.

yearselection:

  id year 6 2014-2015 2 2010-2011 3 2011-2012 4 2012-2013 5 2013-2014 1 2009-2010 7 2015-2016 

testtable2:

 id name yearselection 1 test1 2 2 test2 1 3 test3 1 4 test4 1 5 test5 2 6 test6 3 

I need to display this in json format:

  { "2009-2010": [ { "id": "2", "name": "test2" }, { "id": "3", "name": "test3" }, { "id": "4", "name": "test4" } ], "2010-2011": [ { "id": "1", "name": "test1" }, { "id": "5", "name": "test5" } ], "2011-2012": [ { "id": "6", "name": "test6" } ] } 

mycode

 public function actionArchives() { //echo $keyword=$_POST['keyword']; $query= Yii::app()->db->createCommand("select * from yearselection ORDER BY id ASC")->queryAll(); $arr = array(); if(count($query) > 0) { foreach ($query as $queryElement) { $query2= Yii::app()->db->createCommand("select * from testtable2 where yearselection='".$queryElement['id']."' ORDER BY id ASC")->queryAll(); $arr[] = $queryElement; } } # JSON-encode the response $json_response = json_encode($arr); // # Return the response echo $json_response; //exit; } 

Answer 1

you first write your request as follows

 public function actionArchives() { //echo $keyword=$_POST['keyword']; $query= Yii::app()->db->createCommand("SELECT y.year,y.id as year_id,t.id as test_id,t.name FROM yearselection as y JOIN testtable2 as t ON y.id=t.yearselection")->queryAll(); $arr=array(); if(count($query) > 0) { $arr = $query; // Now you run loop to change indexes of array,ie $count=count($arr); for($i=0;$i<$count;$i++) { $year=$arr[$i]['year']; $arr[$year]=$arr[$i]; unset($arr[$i]); } // Now your array has index as your year column, } # JSON-encode the response $json_response = json_encode($arr); // # Return the response echo $json_response; //exit; } 

Now, as soon as you write the above request, you will receive your data with the columns year, year_id, test_id, name

Now you take the whole data in the array, as you did above, in the $arr[] variable (without touching $query varible to save the query data).

Now you can just do json_encode($arr);

Note : -

Please do not hit the DB in the loop, as if the length of your loop was 100, then it will hit the DB 100 times. Therefore, you can use JOINS in cases where it will receive data from 2 tables based on one parameter.

Hope this solves your request.