Codeigniter - 如何从 ajax 获取数据表数据?
我正在开发一个基于 CodeIgniter 的应用程序.代码如下:
控制器:
公共函数索引(){$数据=数组('记录' =>$this->Parameter_model->get_parameter('tbl_parameter'));$this->site->view('参数', $data);}模型:
公共函数get_parameter($table){$query = $this->db->query("select * from $table order by 'parameter_ID' ASC");如果($查询-> num_rows()> 0){foreach($query->result_array() as $row){$data[] = $row;}$query->free_result();}别的{$数据=空；}返回 $data;}查看:
<头><tr><th class="text-nowrap">参数</th><th class="text-nowrap">方法</th><th class="text-nowrap">Type</th><th class="text-nowrap">Action</th></tr></thead><?php if(!empty($record)):?><?php foreach($record as $row):?><tr align="center"><td class="text-nowrap"><a href="<?=set_url('parameter/parameter_view/'.$row['parameter_ID']);?>"><strong><?php echo $row['parameter_name'];?></strong></a></td><td class="text-nowrap"><?php echo $row['parameter_method'];?></td><td class="text-nowrap"><?phpif($row['parameter_type'] == "1"){echo "一般";}别的{echo "仅限 COA";}?></td><td class="text-nowrap"><div><a href="<?=set_url('parameter#edit?parameter_ID='.$row['parameter_ID']);?>"class="btn btn-warning btn-xs">编辑</a><a href="<?=set_url('parameter/parameter_view/'.$row['parameter_ID']);?>"class="btn btn-success btn-xs">Lihat</a><a href="<?=set_url('parameter#hapus?parameter_ID='.$row['parameter_ID']);?>"class="btn btn-danger btn-xs">Hapus</a></td></tr><?php endforeach;?><?php endif;?></tbody><脚><tr><th class="text-nowrap">参数</th><th class="text-nowrap">方法</th><th class="text-nowrap">Type</th><th class="text-nowrap">Action</th></tr></tfoot>
Javascript:
//参数//设置 - 向每个页脚单元格添加文本输入$('#parameter tfoot th').each( function () {var title = $(this).text();$(this).html('<input type="text" style="width:100%;" title="搜索'+title+'" placeholder="搜索'+title+'"/>' );});//数据表var table = $('#parameter').DataTable({分页:真的，搜索:真实，订购:真实，"顺序": [[ 0, "asc" ]],滚动X:真，滚动条:是的，});//应用搜索table.columns().every( 函数 () {var that = this;$( 'input', this.footer() ).on( 'keyup change', function () {if ( that.search() !== this.value ) {那.search( this.value ).画();}});});以上代码运行良好.
现在，我想通过 ajax 请求将数据提取到 table id="parameter" 中.我已经从 url 创建了一个 ajax 请求，让我们从这里说 http://'+host+path+'/action/ambil，其中 var path = window.location.pathname; 和 var host = window.location.hostname;.
ajax 响应产生:
{"record":[{"parameter_ID":"1","parameter_name":"pH","parameter_method":"(pH计)","parameter_type":"1",{"parameter_ID":"2","parameter_name":"Viscosity","parameter_method":"(Brookfield-Viscometer)","parameter_type":"1"}]}问题
如何使用 Ajax 数据源配置数据表，以及如何将数据显示到表中，以便我可以使用数据创建类似代码的链接<a href="<?=set_url('parameter/parameter_view/'.$row['parameter_ID']);?>">
你可以通过做dataTable服务器端脚本如下.
- 更改您的控制器，以便它处理来自数据表的服务器端调用，并且您只能在控制器中创建动态链接.我在控制器中添加了评论以获取更多详细信息.
- 更改您的脚本以使用 ajax 调用它.
- 加载页面时不要在视图 tbody 中加载任何内容.
- 注意:我跳过了使用直接查询的模型部分.希望你能改变它.
控制器
公共函数索引(){$data = 数组();如果 ($this->input->is_ajax_request()) {/** 这将处理数据表 js ajax 调用 * *//** 获取所有数据表参数 * */$search = $this->input->get('search');/** 数据表的搜索值 * */$offset = $this->input->get('start');/** 偏移值 * */$limit = $this->input->get('length');/** 数据表的限制(显示条目)* */$order = $this->input->get('order');/** order by (column sorted) * */$column = array('parameter', 'method', 'type');/** 在这里设置你的数据库列名以便排序* */$orderColumn = isset($order[0]['column']) ?$column[$order[0]['column']] : '参数';$orderDirection = isset($order[0]['dir']) ?$order[0]['dir'] : 'asc';$ordrBy = $orderColumn ."".$订单方向；如果 (isset($search['value']) && !empty($search['value'])) {/** 创建 sql 或调用模型 * *//** $this->load->model('Parameter_model');$this->Parameter_model->get_parameter('tbl_parameter');* *//** 我直接在控制器中调用 sql 以获得您的答案* 请根据你的表名更改你的sql* */$sql = "SELECT * FROM TABLE_NAME WHERE column_name '%like%'" .$search['value'] .按顺序".$orderBy ." 限制 $offset,$limit";$sql = "SELECT count(*) FROM TABLE_NAME WHERE column_name '%like%'" .$search['value'] .按顺序".$orderBy;$result = $this->db->query($sql);$result2 = $this->db->query($sql2);$count = $result2->num_rows();} 别的 {/*** 如果数据表中没有可用的搜索值*/$sql = "SELECT * FROM TABLE_NAME order by " .$orderBy ." 限制 $offset,$limit";$sql2 = "SELECT * FROM TABLE_NAME order by " .$orderBy;$result = $this->db->query($sql);$result2 = $this->db->query($sql2);$count = $result2->num_rows();}/** 根据需要创建要在数据表中显示的数据 **/$data = 数组();如果 (!empty($result->result())) {foreach ($result->result() as $k => $v) {$数据[] = 数组(/** 你可以在这里添加任何锚链接或动态数据 **/'参数' =>"<a href=".set_url('parameter/parameter_view/'.$v['parameter_ID'])."><strong>".$v['parameter_name']."</strong><;/a>",'方法' =>"<a href=".set_url('parameter/parameter_view/'.$v['parameter_ID'])."><strong>".$v['parameter_name']."</strong><;/a>",'参数类型' =>"<a href=".set_url('parameter/parameter_view/'.$v['parameter_ID'])."><strong>".$v['parameter_name']."</strong><;/a>",'动作' =>"<a href=".set_url('parameter/parameter_view/'.$v['parameter_ID'])."><strong>".$v['parameter_name']."</strong><;/a>");}}/*** 分页和信息需要 draw,recordTotal,recordsFiltered.*/$结果=数组(画"=>$this->input->get('draw'),记录总数"=>计数($数据)，记录过滤" =>$计数，数据" =>$数据);回声 json_encode($results);} 别的 {/** 这将默认加载，没有数据表的数据* 我们将通过datatable ajax调用加载表中的数据*/$this->site->view('参数', $data);}}查看
<头><tr><th class="text-nowrap">参数</th><th class="text-nowrap">方法</th><th class="text-nowrap">Type</th><th class="text-nowrap">Action</th></tr></thead>/** tbody 默认为空.**/</tbody><脚><tr><th class="text-nowrap">参数</th><th class="text-nowrap">方法</th><th class="text-nowrap">Type</th><th class="text-nowrap">Action</th></tr></tfoot>
脚本
<脚本>$(document).ready(function() {$('#example').DataTable({url: '<?php base_url();?>控制器名称/索引',处理:真实，服务器端:真，分页:真的，搜索:真实，订购:真实，顺序:[[0, "asc"]],滚动X:真，滚动条:是的，列:[{数据:参数"}，{数据:方法"}，{数据:参数类型"}，{数据:动作"}]/** 这将创建具有上述列数据的数据表 **/});});如果你想使用一些第三方库检查这个.对于您的模型查询，您可以按照 这篇文章.
I'm working an application based on CodeIgniter. Here the code:
Controller:
public function index(){
$data = array(
'record' => $this->Parameter_model->get_parameter('tbl_parameter')
);
$this->site->view('parameter', $data);
}
Model:
public function get_parameter($table){
$query = $this->db->query("select * from $table order by 'parameter_ID' ASC");
if($query->num_rows() > 0){
foreach($query->result_array() as $row){
$data[] = $row;
}
$query->free_result();
}
else{
$data = NULL;
}
return $data;
}
View:
<table id="parameter" class="listdata table table-bordered table-striped table-hover">
<thead>
<tr>
<th class="text-nowrap">Parameter</th>
<th class="text-nowrap">Method</th>
<th class="text-nowrap">Type</th>
<th class="text-nowrap">Action</th>
</tr>
</thead>
<tbody>
<?php if(!empty($record)):?>
<?php foreach($record as $row):?>
<tr align="center">
<td class="text-nowrap"><a href="<?=set_url('parameter/parameter_view/'.$row['parameter_ID']);?>"><strong><?php echo $row['parameter_name'];?></strong></a></td>
<td class="text-nowrap"><?php echo $row['parameter_method'];?></td>
<td class="text-nowrap">
<?php
if($row['parameter_type'] == "1"){
echo "General";
}
else{
echo "COA Only";
}
?>
</td>
<td class="text-nowrap">
<div>
<a href="<?=set_url('parameter#edit?parameter_ID='.$row['parameter_ID']);?>" class="btn btn-warning btn-xs">Edit</a>
<a href="<?=set_url('parameter/parameter_view/'.$row['parameter_ID']);?>" class="btn btn-success btn-xs">Lihat</a>
<a href="<?=set_url('parameter#hapus?parameter_ID='.$row['parameter_ID']);?>" class="btn btn-danger btn-xs">Hapus</a>
</div>
</td>
</tr>
<?php endforeach;?>
<?php endif;?>
</tbody>
<tfoot>
<tr>
<th class="text-nowrap">Parameter</th>
<th class="text-nowrap">Method</th>
<th class="text-nowrap">Type</th>
<th class="text-nowrap">Action</th>
</tr>
</tfoot>
</table>
Javascript:
// parameter
// Setup - add a text input to each footer cell
$('#parameter tfoot th').each( function () {
var title = $(this).text();
$(this).html( '<input type="text" style="width:100%;" title="Search '+title+'" placeholder="Search '+title+'" />' );
} );
// DataTable
var table = $('#parameter').DataTable({
paging: true,
searching: true,
ordering: true,
"order": [[ 0, "asc" ]],
scrollX: true,
scroller: true,
});
// Apply the search
table.columns().every( function () {
var that = this;
$( 'input', this.footer() ).on( 'keyup change', function () {
if ( that.search() !== this.value ) {
that
.search( this.value )
.draw();
}
} );
} );
Above code work well.
Now, I want to fetch data into the table id="parameter" via ajax request. I've create an ajax request from url, lets say from here http://'+host+path+'/action/ambil, where var path = window.location.pathname; and var host = window.location.hostname;.
The ajax response produce:
{"record":[{"parameter_ID":"1","parameter_name":"pH","parameter_method":"(pH meter)","parameter_type":"1",{"parameter_ID":"2","parameter_name":"Viscosity","parameter_method":"(Brookfield-Viscometer)","parameter_type":"1"}]}
Question
How to configure datatable with Ajax data source, and how to display the data into the table, so I can use the data for example to create a link like code
<a href="<?=set_url('parameter/parameter_view/'.$row['parameter_ID']);?>">
You can do dataTable by server side script as follow.
- Change your controller so that It will handle the server side call from datatable and you can create dynamic links in controller only. I have added comment in controller for more details.
- Change your script to call it with ajax.
- Don't load any thing in view tbody while loading page.
- Note : I have skipped the model part I used direct query. Hope you can change it.
Contorller
public function index() {
$data = array();
if ($this->input->is_ajax_request()) {
/** this will handle datatable js ajax call * */
/** get all datatable parameters * */
$search = $this->input->get('search');/** search value for datatable * */
$offset = $this->input->get('start');/** offset value * */
$limit = $this->input->get('length');/** limits for datatable (show entries) * */
$order = $this->input->get('order');/** order by (column sorted ) * */
$column = array('parameter', 'method', 'type');/** set your data base column name here for sorting* */
$orderColumn = isset($order[0]['column']) ? $column[$order[0]['column']] : 'parameter';
$orderDirection = isset($order[0]['dir']) ? $order[0]['dir'] : 'asc';
$ordrBy = $orderColumn . " " . $orderDirection;
if (isset($search['value']) && !empty($search['value'])) {
/** creat sql or call Model * */
/** $this->load->model('Parameter_model');
$this->Parameter_model->get_parameter('tbl_parameter'); * */
/** I am calling sql directly in controller for your answer
* Please change your sql according to your table name
* */
$sql = "SELECT * FROM TABLE_NAME WHERE column_name '%like%'" . $search['value'] . " order by " . $ordrBy . " limit $offset,$limit";
$sql = "SELECT count(*) FROM TABLE_NAME WHERE column_name '%like%'" . $search['value'] . " order by " . $ordrBy;
$result = $this->db->query($sql);
$result2 = $this->db->query($sql2);
$count = $result2->num_rows();
} else {
/**
* If no seach value avaible in datatable
*/
$sql = "SELECT * FROM TABLE_NAME order by " . $ordrBy . " limit $offset,$limit";
$sql2 = "SELECT * FROM TABLE_NAME order by " . $ordrBy;
$result = $this->db->query($sql);
$result2 = $this->db->query($sql2);
$count = $result2->num_rows();
}
/** create data to display in dataTable as you want **/
$data = array();
if (!empty($result->result())) {
foreach ($result->result() as $k => $v) {
$data[] = array(
/** you can add what ever anchor link or dynamic data here **/
'parameter' => "<a href=".set_url('parameter/parameter_view/'.$v['parameter_ID'])."><strong>".$v['parameter_name']."</strong></a>",
'method' => "<a href=".set_url('parameter/parameter_view/'.$v['parameter_ID'])."><strong>".$v['parameter_name']."</strong></a>",
'parameter_type' => "<a href=".set_url('parameter/parameter_view/'.$v['parameter_ID'])."><strong>".$v['parameter_name']."</strong></a>",
'actions' => "<a href=".set_url('parameter/parameter_view/'.$v['parameter_ID'])."><strong>".$v['parameter_name']."</strong></a>"
);
}
}
/**
* draw,recordTotal,recordsFiltered is required for pagination and info.
*/
$results = array(
"draw" => $this->input->get('draw'),
"recordsTotal" => count($data),
"recordsFiltered" => $count,
"data" => $data
);
echo json_encode($results);
} else {
/** this will load by default with no data for datatable
* we will load data in table through datatable ajax call
*/
$this->site->view('parameter', $data);
}
}
View
<table id="parameter" class="listdata table table-bordered table-striped table-hover">
<thead>
<tr>
<th class="text-nowrap">Parameter</th>
<th class="text-nowrap">Method</th>
<th class="text-nowrap">Type</th>
<th class="text-nowrap">Action</th>
</tr>
</thead>
<tbody>
/** tbody will be empty by default. **/
</tbody>
<tfoot>
<tr>
<th class="text-nowrap">Parameter</th>
<th class="text-nowrap">Method</th>
<th class="text-nowrap">Type</th>
<th class="text-nowrap">Action</th>
</tr>
</tfoot>
</table>
Script
<script>
$(document).ready(function() {
$('#example').DataTable({
url: '<?php base_url(); ?>controllerName/index',
processing: true,
serverSide: true,
paging: true,
searching: true,
ordering: true,
order: [[0, "asc"]],
scrollX: true,
scroller: true,
columns: [{data: "parameter"}, {data: "method"}, {data: "parameter_type"}, {data: "action"}]
/** this will create datatable with above column data **/
});
});
</script>
If you wish to use some third party library check this. For your model query you can customize it as mention in this post.
相关文章