我试图通过ajax在codeigniter中使用datatable服务器端组合数据，但得到了错误结果，如下所示：
发生数据库错误错误
编号：1096未使用表select*ORDER BYid_judul
ASC LIMIT 2文件名：models/Judul_model.phpLine编号：
88个
型号：
<?php defined('BASEPATH') OR exit('No direct script access allowed');
class Judul_model extends MY_Model
{
//Server side
var $column_order = array(null,'isbn','judul_buku','penulis','penerbit',null,null,'letak'); //set column field database for datatable orderable
var $column_search = array('isbn','judul_buku','penulis','penerbit','letak'); //set column field database for datatable searchable just firstname , lastname , address are searchable
var $order = array('id_judul' => 'asc'); // default order
private function _get_datatables_query()
{
$sql = "SELECT judul.id_judul,
judul.judul_buku,
judul.isbn,
judul.penulis,
judul.penerbit,
judul.klasifikasi,
judul.cover,
judul.letak,
/*jumlah total*/
IFNULL((SELECT COUNT(buku.id_buku)
FROM buku
WHERE buku.id_judul = judul.id_judul
GROUP BY buku.id_judul),0) AS jumlah_total,
/*jumlah ada*/
IFNULL((SELECT COUNT(buku.id_buku)
FROM buku
WHERE buku.id_judul = judul.id_judul
AND buku.is_ada = 'y'
GROUP BY buku.id_judul),0) AS jumlah_ada,
/*jumlah keluar*/
IFNULL((SELECT COUNT(buku.id_buku)
FROM buku
WHERE buku.id_judul = judul.id_judul
AND buku.is_ada = 'n'
GROUP BY buku.id_judul),0) AS jumlah_dipinjam
FROM judul
GROUP BY judul.id_judul
ORDER BY judul.id_judul DESC";
$this->db->query($sql);
$i = 0;
foreach ($this->column_search as $item) // loop column
{
if($_POST['search']['value']) // if datatable send POST for search
{
if($i===0) // first loop
{
$this->db->group_start(); // open bracket. query Where with OR clause better with bracket. because maybe can combine with other WHERE with AND.
$this->db->like($item, $_POST['search']['value']);
}
else
{
$this->db->or_like($item, $_POST['search']['value']);
}
if(count($this->column_search) - 1 == $i) //last loop
$this->db->group_end(); //close bracket
}
$i++;
}
if(isset($_POST['order'])) // here order processing
{
$this->db->order_by($this->column_order[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
}
else if(isset($this->order))
{
$order = $this->order;
$this->db->order_by(key($order), $order[key($order)]);
}
}
function get_datatables()
{
$this->_get_datatables_query();
if($_POST['length'] != -1)
$this->db->limit($_POST['length'], $_POST['start']);
$query = $this->db->get();
return $query->result();
}
function count_filtered()
{
$this->_get_datatables_query();
$query = $this->db->get();
return $query->num_rows();
}
public function count_all()
{
$this->db->from($this->table);
return $this->db->count_all_results();
}
////////////////////////////////////
}
控制器：
<?php defined('BASEPATH') OR exit('No direct script access allowed');
class Judul extends MY_Controller
{
//////////////////////////////CRUD//////////////////////////////
//Menampilkan Data Judul Buku
public function index()
{
$main_view = 'judul/index';
$this->load->view('template',compact( 'main_view'));
}
//Harus Login terlebih dahulu
protected function isLogin()
{
$isLogin = $this->session->userdata('is_login');
if(!$isLogin) {
redirect(base_url());
}
}
//Datatable serverside
public function ajax_list()
{
$level = $this->session->userdata('level');
$list = $this->judul->get_datatables();
$data = array();
$no = $_POST['start'];
foreach ($list as $judul) {
$no++;
$row = array();
$row[] = $no;
$row[] = $judul->isbn;
$row[] = $judul->judul_buku;
$row[] = $judul->penulis;
$row[] = $judul->penerbit;
$row[] = 'Total: '. $judul->jumlah_total != 0 ? anchor("buku/total/$judul->id_judul",$judul->jumlah_total) : $judul->jumlah_total.
'<br>'.
'Ada : '. $judul->jumlah_ada != 0 ? anchor("buku/ada/$judul->id_judul",$judul->jumlah_ada) : $judul->jumlah_ada.
'<br>'.
'Dipinjam : ' . $judul->jumlah_dipinjam != 0 ? anchor("buku/dipinjam/$judul->id_judul",$judul->jumlah_dipinjam) : $judul->jumlah_dipinjam;
if($judul->cover)
$row[] = '<a href="'.base_url('cover/'.$judul->cover).'" target="_blank"><img src="'.base_url('cover/'.$judul->cover).'" style="border: 1px solid #aaaaaa; padding: 2px; width: 100px;" class="cover img-responsive" /></a>';
else
$row[] = '<img src="'.base_url('cover/no_cover.jpg').'" style="border: 1px solid #aaaaaa; padding: 2px; width: 100px;" class="cover img-responsive" />';
$row[] = $judul->letak ;
if ($level === 'admin'):
$row[] = form_open("buku/create").form_hidden('id_judul',$judul->id_judul).form_hidden('first_load',true).form_button(['type' => 'submit','content' => '<i class="material-icons">add</i>', 'class' => 'btn btn-success waves-effect','data-toggle' => 'tooltip', 'data-placement' => 'right' ,'title' => 'Tambah Copy Buku']).form_close();
$row[] = anchor("judul/edit/$judul->id_judul",'<i class="material-icons">edit</i>', ['class' => 'btn btn-warning waves-effect','data-toggle' => 'tooltip', 'data-placement' => 'right' ,'title' => 'Edit']);
$row[] = form_open("judul/delete/$judul->id_judul").form_hidden('id_judul',"$judul->id_judul").form_button(['type' => 'submit','content' => '<i class="material-icons">delete</i>', 'class' => 'btn btn-danger waves-effect','data-toggle' => 'tooltip', 'data-placement' => 'right' ,'title' => 'Delete','onclick' => "return confirm('Anda yakin akan menghapus judul ini?')"]).form_close();
endif;
$data[] = $row;
}
$output = array(
"draw" => $_POST['draw'],
"recordsTotal" => $this->judul->count_all(),
"recordsFiltered" => $this->judul->count_filtered(),
"data" => $data,
);
//output to json format
echo json_encode($output);
}
///////////////////////////////////////////////////////////
}
视图：
<?php
//Login?
$is_login = $this->session->userdata('is_login');
$level = $this->session->userdata('level');
?>
<!--flash message-->
<?php $this->load->view('_partial/flash_message') ?>
<div class="row clearfix">
<div class="col-xs-12 col-sm-12 col-md-12 col-lg-12">
<div class="card">
<div class="header">
<div class="row clearfix">
<div class="col-xs-12 col-sm-6">
<h2>BUKU</h2>
</div>
</div>
</div>
<div class="body">
<div class="body table-responsive">
<table id="serverside" class="table table-bordered table-striped table-hover">
<thead>
<tr><th>No</th>
<th>ISBN</th>
<th>Judul</th>
<th>Penulis</th>
<th>Penerbit</th>
<th>Jumlah Copy</th>
<th>Cover</th>
<th>Letak</th>
<?php if ($level === 'admin'): ?>
<th>Add</th>
<th>Edit</th>
<th>Delete</th>
<?php endif ?>
</tr>
</thead>
<tbody>
</tbody>
</table>
<div class="row">
<!-- Button Create-->
<div class="col-xs-12">
<?php if ($level === 'admin'): ?>
<?= anchor("judul/create",'Tambah Judul',['class' => 'btn btn-primary waves-effect','data-toggle' => 'tooltip', 'data-placement' => 'right' ,'title' => 'Tambah Judul']) ?>
<?php else: ?>
<?php endif ?>
</div>
</div>
</div>
</div>
</div>
</div>
<!-- Konfigurasi serverside-->
<?php echo server_side() ?>
<script type="text/javascript">
$(document).ready(function() {
$('#serverside').DataTable({
"processing" : true,
"serverSide" : true,
"language": {
"url": "adminbsb/plugins/jquery-datatable/Indonesian.json",
searchPlaceholder: "No Anggota, No Induk, Nama"
},
"lengthMenu": [ [5, 10, 25, -1], [5, 10, 25, "All"] ],"pageLength": 2,
"order" : [],
"ajax": {
"url" : "<?= site_url('judul/ajax_list'); ?>",
"type" : "POST"
},
"columnDefs" : [
{
"targets" : [0],
"orderable":false,
},
],
});
});
</script>
求你了，帮帮我。。。
最佳答案
$this->db->query()不是活动的记录函数，应该单独使用。
调用活动记录的一部分$this->db->get()时，会出现“找不到表”错误，因为您尚未为此方法提供任何表名。
您可以改为使用活动记录来更改$this->db->query($sql)：
$this->db->select('judul.id_judul, judul.judul_buku, judul.isbn, judul.penulis, judul.penerbit, judul.klasifikasi, judul.cover, judul.letak')
->select("IFNULL((SELECT COUNT(buku.id_buku) FROM buku WHERE buku.id_judul = judul.id_judul GROUP BY buku.id_judul),0) AS jumlah_total", false)
->select("IFNULL((SELECT COUNT(buku.id_buku) FROM buku WHERE buku.id_judul = judul.id_judul AND buku.is_ada = 'y' GROUP BY buku.id_judul),0) AS jumlah_ada", false)
->select("IFNULL((SELECT COUNT(buku.id_buku) FROM buku WHERE buku.id_judul = judul.id_judul AND AND buku.is_ada = 'n' GROUP BY buku.id_judul),0) AS jumlah_dipinjam", false)
->from('judul')
->group_by('judul.id_judul')
->order_by('judul.id_judul', 'DESC');
关于php - jQuery DataTable服务器端在Codeigniter中不起作用，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/48127927/