CodeIgniter中的GROUP_CONCAT [英] GROUP_CONCAT in CodeIgniter
本文介绍了CodeIgniter中的GROUP_CONCAT的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我只是想阻止如何使用CodeIgniter中的sql请求创建group_concat.我的所有查询都使用Jtable库列在表中.
一切正常，除非我尝试插入GROUP_CONCAT.
这是我的模特页面:
功能list_all(){$ login_id = $ this->会话-> userdata('User_id');$ this-> db-> select('p.project_id，p.Project，p.Description，p.Status，p.Thumbnail，t.Template');$ this-> db-> from('assigned_projects_ppeople a');$ this-> db-> where('people_id'，$ login_id);$ this-> db-> join('projects p'，'p.project_id = a.project_id');$ this-> db-> join('project_templates t'，'t.template_id = p.template_id');$ this-> db-> select('GROUP_CONCAT(u.Asset SEPARATOR，")as assetlist'，FALSE);$ this-> db-> from('assigned_assets_pproject b');$ this-> db-> join('assets u'，'u.asset_id = b.asset_id');$ query = $ this-> db-> get();$ rows = $ query-> result_array();//返回结果到jTable$ jTableResult = array();$ jTableResult ['Result'] =确定";$ jTableResult ['Records'] = $ rows;返回$ jTableResult;} 我的控制器页面:
函数listRecord(){$ this-> load-> model('project_model');$ result = $ this-> project_model-> list_all();打印json_encode($ result);} 然后完成我的查看页面:
< table id ="listtable"></table>< script type ="text/javascript">$(document).ready(function(){$('#listtable').jtable({标题:表格测试"，动作:{listAction:'<?php echo base_url().'project/listRecord';?>',,createAction:'/GettingStarted/CreatePerson'，updateAction:'/GettingStarted/UpdatePerson'，deleteAction:'/GettingStarted/DeletePerson'}，栏位:{project_id:{关键:对，清单:假}，项目: {标题:项目名称"}，描述: {标题描述'}，地位: {标题:状态"，宽度:"20px"}，缩略图:{标题:缩略图"，显示:功能(数据){返回'< a href =<?php echo base_url('project');?>/'+ data.record.project_id +'">< img class ="thumbnail" width ="50px" height="50px" src ='+ data.record.Thumbnail +'" alt ='+ data.record.Thumbnail +'"></a>';}}，模板: {标题:模板"}，资产: {标题:资产"}，记录日期: {标题:记录日期"，类型:日期"，创建:假，假}}});//从服务器加载人列表$('#listtable').jtable('load');});</script> 我读过很多有关这方面的文章，例如用'，'替换'，'分隔符，或在连接中使用OUTER，或在使用get方法之前使用group_by('p.project_id')，这是行不通的./p>
这是json中查询的输出:
{结果":确定"，记录":[{"project_id":"1"，项目":亚当和夏娃:一个著名的故事"，描述":"3D动画电影中有关亚当和夏娃的故事！，"状态:" wip，"缩略图:" http:\/\/localhost \/assets \/images \/thumb \/projectAdamAndEve.png，模板":动画电影"，资产清单":苹果，亚当，夏娃，伊甸园"}]} 我们可以看到GROUP_CONCAT在这里(在"assetslist"之后)，但是该列仍然为空.
如果询问，我可以发布数据库SQL文件.
解决方案
很简单，在我的鼻子底下，但我没看到...GROUP_CONCAT对新的coresponding列使用别名(作为"assetslist").我必须使用此名称，而不是Jtable配置中未连接的表列资产"的默认名称:
< table id ="listtable"></table>< script type ="text/javascript">$(document).ready(function(){$('#listtable').jtable({标题:表格测试"，动作:{listAction:'<?php echo base_url().'project/listRecord';?>',,createAction:'/GettingStarted/CreatePerson'，updateAction:'/GettingStarted/UpdatePerson'，deleteAction:'/GettingStarted/DeletePerson'}，栏位:{project_id:{关键:对，清单:假}，项目: {标题:项目名称"}，描述: {标题描述'}，地位: {标题:状态"，宽度:"20px"}，缩略图:{标题:缩略图"，显示:功能(数据){返回'< a href =<?php echo base_url('project');?>/'+ data.record.project_id +'">< img class ="thumbnail" width ="50px" height="50px" src ='+ data.record.Thumbnail +'" alt ='+ data.record.Thumbnail +'"></a>';}}，模板: {标题:模板"}，资产清单:{标题:资产"}，记录日期: {标题:记录日期"，类型:日期"，创建:假，假}}});//从服务器加载人列表$('#listtable').jtable('load');});</script> 谢谢！
I'm just blocking to how create my group_concat with my sql request in CodeIgniter. All my queries are listed in a table, using Jtable library.
All work fine, except when I try to insert GROUP_CONCAT.
Here's my model page :
function list_all()
{
$login_id = $this->session->userdata('User_id');
$this->db->select('p.project_id, p.Project, p.Description, p.Status, p.Thumbnail, t.Template');
$this->db->from('assigned_projects_ppeople a');
$this->db->where('people_id', $login_id);
$this->db->join('projects p', 'p.project_id = a.project_id');
$this->db->join('project_templates t', 't.template_id = p.template_id');
$this->db->select('GROUP_CONCAT(u.Asset SEPARATOR ",") as assetslist', FALSE);
$this->db->from('assigned_assets_pproject b');
$this->db->join('assets u', 'u.asset_id = b.asset_id');
$query = $this->db->get();
$rows = $query->result_array();
//Return result to jTable
$jTableResult = array();
$jTableResult['Result'] = "OK";
$jTableResult['Records'] = $rows;
return $jTableResult;
}
My controller page :
function listRecord(){
$this->load->model('project_model');
$result = $this->project_model->list_all();
print json_encode($result);
}
And to finish my view page :
<table id="listtable"></table>
<script type="text/javascript">
$(document).ready(function () {
$('#listtable').jtable({
title: 'Table test',
actions: {
listAction: '<?php echo base_url().'project/listRecord';?>',
createAction: '/GettingStarted/CreatePerson',
updateAction: '/GettingStarted/UpdatePerson',
deleteAction: '/GettingStarted/DeletePerson'
},
fields: {
project_id: {
key: true,
list: false
},
Project: {
title: 'Project Name'
},
Description: {
title: 'Description'
},
Status: {
title: 'Status',
width: '20px'
},
Thumbnail: {
title: 'Thumbnail',
display: function (data) {
return '<a href="<?php echo base_url('project');?>/' + data.record.project_id + '"><img class="thumbnail" width="50px" height="50px" src="' + data.record.Thumbnail + '" alt="' + data.record.Thumbnail + '" ></a>';
}
},
Template: {
title: 'Template'
},
Asset: {
title: 'Assets'
},
RecordDate: {
title: 'Record date',
type: 'date',
create: false,
edit: false
}
}
});
//Load person list from server
$('#listtable').jtable('load');
});
</script>
I read lot of posts talking about that, like replace ',' separator by ",", or use OUTER to the join, or group_by('p.project_id') before using get method, don't work.
Here is a the output of the query in json :
{"Result":"OK","Records":[{"project_id":"1","Project":"Adam & Eve : A Famous Story","Description":"The story about Adam & Eve reviewed in 3D Animation movie !","Status":"wip","Thumbnail":"http:\/\/localhost\/assets\/images\/thumb\/projectAdamAndEve.png","Template":"Animation Movie","assetslist":"Apple, Adam, Eve, Garden of Eden"}]}
We can see the GROUP_CONCAT is here (after "assetslist"), but the column stills empty.
If asked, I can post the database SQL file.
解决方案
So simple, under my nose but I didn't see it... GROUP_CONCAT uses an alias name for the new coresponding column (as "assetslist"). I had to use this name, and not the default name of my non concatened table column "Asset" in the Jtable config :
<table id="listtable"></table>
<script type="text/javascript">
$(document).ready(function () {
$('#listtable').jtable({
title: 'Table test',
actions: {
listAction: '<?php echo base_url().'project/listRecord';?>',
createAction: '/GettingStarted/CreatePerson',
updateAction: '/GettingStarted/UpdatePerson',
deleteAction: '/GettingStarted/DeletePerson'
},
fields: {
project_id: {
key: true,
list: false
},
Project: {
title: 'Project Name'
},
Description: {
title: 'Description'
},
Status: {
title: 'Status',
width: '20px'
},
Thumbnail: {
title: 'Thumbnail',
display: function (data) {
return '<a href="<?php echo base_url('project');?>/' + data.record.project_id + '"><img class="thumbnail" width="50px" height="50px" src="' + data.record.Thumbnail + '" alt="' + data.record.Thumbnail + '" ></a>';
}
},
Template: {
title: 'Template'
},
assetslist: {
title: 'Assets'
},
RecordDate: {
title: 'Record date',
type: 'date',
create: false,
edit: false
}
}
});
//Load person list from server
$('#listtable').jtable('load');
});
</script>
thank you !
这篇关于CodeIgniter中的GROUP_CONCAT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
