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.
我有两个表,在年度选择中有两列是在那里,在testtable2中有三列是基于第一个表id在第二个表中使用的。我想使用php,这两个表显示json如下所示。
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:
我希望以json格式显示如下:
{
"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;
}
1 个解决方案
#1
3
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
现在,一旦您编写了上述查询,您将获得包含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).
现在你已经在变量$ arr []中完成了数组中的整个数据(不接触$ query varible来保存查询数据)。
Now you can do simply json_encode($arr);
现在你可以做简单的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.
请不要在循环中命中DB,就好像你的循环长度假设为100然后它将达到DB 100次。因此,您可以在这些情况下应用JOINS,以便它将基于单个参数从2个表中获取数据。
Hope this solves your query.
希望这能解决您的疑问。
#1
3
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
现在,一旦您编写了上述查询,您将获得包含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).
现在你已经在变量$ arr []中完成了数组中的整个数据(不接触$ query varible来保存查询数据)。
Now you can do simply json_encode($arr);
现在你可以做简单的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.
请不要在循环中命中DB,就好像你的循环长度假设为100然后它将达到DB 100次。因此,您可以在这些情况下应用JOINS,以便它将基于单个参数从2个表中获取数据。
Hope this solves your query.
希望这能解决您的疑问。