how do i get json responce from php below format?

Question

i have two tables, in yearselection two columns are there and in testtable2 three columns are there, based on first table id's using in 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 want display like 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 have write your query first like this

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,i.e.
      $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 once you write the above query you will get your data with columns year,year_id,test_id,name

Now you have take whole data in an array as you have done above in variable $arr[](Not touching the $query varible to preserve query data).

Now you can do simply json_encode($arr);

Note:-

Please don't hit DB in a loop as if your loop length is suppose 100 then it will hit DB 100 times. So you can apply JOINS in those cases so that it will take data from 2 tables based on a single parameter.

Hope this solves your query.