php - codeigniter 中的 jquery 数据表服务器端无法正常工作

Question

我尝试通过 ajax 在 codeigniter 中使用数据表服务器端合并数据，但我得到了错误结果，如下所示:

A Database Error Occurred

Error Number: 1096

No tables used

SELECT * ORDER BY id_judul ASC LIMIT 2

Filename: models/Judul_model.php

Line Number: 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();
    }
    ////////////////////////////////////

}

Controller :

<?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: ?>
                                &nbsp;
                            <?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>

请帮帮我...

Answer 1

$this->db->query() 不是事件记录函数，应该单独使用。
当您调用事件记录的一部分 $this->db->get() 时，您会收到“No Tables Found”错误，因为您尚未向该方法提供任何表名。

您可以使用事件记录来更改 $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');