Codeigniter 3 "No Table Used" when i tried to reuse database intstance

owf 0
Viewed 104

I'm trying to make reusable datatable instance

My Datatable Class :

class Datatables extends CI_Model {
    protected $columnOrder;
    protected $columnSearch;
    protected $query;

    public function __construct($columnOrder,$columnSearch,$query)
    {
        parent::__construct();
        $this->columnOrder = $columnOrder;
        $this->columnSearch = $columnSearch;
        $this->query = $query;
    }

    /**
     * Generate db query
     *
     * @return object
     */
    private function getDatatablesQuery()
    {
        $i = 0;
        foreach ($this->columnSearch as $item) {
            if(@$_POST['search']['value']) {
                if($i===0) {
                    $this->query->group_start();
                    $this->query->like($item, $_POST['search']['value']);
                } else {
                    $this->query->or_like($item, $_POST['search']['value']);
                }
                if(count($this->columnSearch) - 1 == $i)
                    $this->query->group_end();
            }
            $i++;
        }
        
        if(isset($_POST['order'])) {
            $this->query->order_by($this->columnOrder[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
        }  else if(isset($this->order)) {
            $order = $this->order;
            $$this->query->order_by(key($order), $order[key($order)]);
        }
    }

    /**
     * Generate db result
     *
     * @return integer
     */
    public function getDatatables() 
    {
        $this->getDatatablesQuery();
        
        if(@$_POST['length'] != -1) $this->query->limit(@$_POST['length'], @$_POST['start']);
        
        $query = $this->query->get();
        
        return $query->result();
    }

    /**
     * Count filtered rows
     *
     * @return integer
     */
    public function countFiltered() 
    {
        $query = $this->query->get();
        
        return $query->num_rows;
    }

    /**
     * Count all rows
     *
     * @return integer
     */
    public function countAll() 
    {
        return $this->query->count_all_results();
    }
}

My FmrTable Class

<?php defined('BASEPATH') OR exit('No direct script access alowed');

require 'application/libraries/Datatables/Datatables.php';

class FmrTable {
    protected $select;
    protected $columnOrder;
    protected $columnSearch;
    protected $ci;

    public function __construct() 
    {
        $this->select = 'fmrs.id as id,sections.name as section,users.username as user,fmr_no,fmrs.status';
        $this->columnOrder = ['id','section','user','fmr_no','status'];
        $this->columnSearch = ['section','user','fmr_no','status'];

        $this->ci = get_instance();
    }

    public function get()
    {
        $query = $this->ci->db
            ->select($this->select)
            ->from('fmrs')
            ->join('sections as sections', 'fmrs.section_id = sections.id', 'LEFT')
            ->join('users as users', 'fmrs.user_id = users.id', 'LEFT');

            $query->where('section_id',$this->ci->session->userdata('section-fmr'));
        }
        
        $datatable = new Datatables($this->columnOrder,$this->columnSearch,$query);

        return [
            'list' => $datatable->getDatatables(),
            'countAll' => $datatable->countAll(),
            'countFiltered' => $datatable->countFiltered()
        ];
    }
}

This always throw a database error that says Error Number: 1096 No tables used

This came from the countFiltered() method, when i tried to dump the $query without get(), it returned the correct object instance but if i do this then the num_rows property will never available, but when i add the get() method, it will return the 1096 error number

How to solve this ?

codeigniter-3 mysql php
1 Answers

A call to ->get() resets the query builder. So when you call ->get() for the second time (in countFiltered) the table name and the remainder of the query have been cleared and that's why you get the error.

Solution is to use query builder caching. This allows you to cache part of the query (between start_cache and stop_cache) and execute it multiple times: https://www.codeigniter.com/userguide3/database/query_builder.html?highlight=start_cache#query-builder-caching

Use flush_cache to clear the cache afterwards, so the cached query part does not interfere with subsequent queries in the same request:

FmrTable

public function get()
{
    $this->ci->db->start_cache();

    $query = $this->ci->db
        ->select($this->select)
        ->from('fmrs')
        ->join('sections as sections', 'fmrs.section_id = sections.id', 'LEFT')
        ->join('users as users', 'fmrs.user_id = users.id', 'LEFT');

        $query->where('section_id',$this->ci->session->userdata('section-fmr'));
    //}

    $this->ci->db->stop_cache();
    
    $datatable = new Datatables($this->columnOrder,$this->columnSearch,$query);

    $result = [
        'list' => $datatable->getDatatables(),
        'countAll' => $datatable->countAll(),
        'countFiltered' => $datatable->countFiltered()
    ];

    $this->ci->db->flush_cache();

    return $result;
}

And probably use num_rows() instead of num_rows here, num_rows gave me a NULL instead of a count:

Datatables

/**
 * Count filtered rows
 *
 * @return integer
 */
public function countFiltered() 
{
    $query = $this->query->get();
    
    return $query->num_rows();
}
Marleen0
Related