Search code examples
phpmysqlcodeignitercountquery-builder

Return count of qualifying rows from a SELECT query with a JOIN using CodeIgniter

I have implemented a query to show the number of rows of my table, the query has been implemented in the model and the function has been called in controller class, but it throws the next text:

CI_DB_mysqli_result Object ( [conn_id] => mysqli Object ( [affected_rows] => 1 [client_info] => mysqlnd 5.0.11-dev - 20120503 - $Id: 76b08b24596e12d4553bd41fc93cccd5bac2fe7a $ [client_version] => 50011 [connect_errno] => 0 [connect_error] => [errno] => 0 [error] => [error_list] => Array ( ) [field_count] => 1 [host_info] => localhost via TCP/IP [info] => [insert_id] => 0 [server_info] => 5.5.5-10.1.21-MariaDB [server_version] => 50505 [stat] => Uptime: 8603 Threads: 1 Questions: 1350 Slow queries: 0 Opens: 38 Flush tables: 1 Open tables: 32 Queries per second avg: 0.156 [sqlstate] => 00000 [protocol_version] => 10 [thread_id] => 61 [warning_count] => 0 ) [result_id] => mysqli_result Object ( [current_field] => 0 [field_count] => 1 [lengths] => [num_rows] => 1 [type] => 0 ) [result_array] => Array ( ) [result_object] => Array ( ) [custom_result_object] => Array ( ) [current_row] => 0 [num_rows] => [row_data] => )

Database

enter image description here

Model

<?php

class Entregas_Model extends CI_Model {

    public function __construct() {
        parent::__construct();
        // Your own constructor code
        $this->load->database();
    }

    //Para obtener el número de filas 
    //y así determinar el número de plazas
    public function get_rows($idCarga) {
        $this->db->select('COUNT(idCarga)');
        $this->db->from('entregas');
        $this->db->join('intervalosHorarios', 'entregas.idCarga = intervalosHorarios.idIntervaloHorario');
        //$this->db->on('entregas.idCarga = intervalosHorarios.idCarga');
        $this->db->where('entregas.idIntervaloHorario', $idCarga);

        $q = $this->db->get();
        //$q = $q->result_array();
        print_r($q);
        return $q;
    }
    
}

Controller (short version)

public function entregas_lista($idCarga) {
    $crud = new grocery_CRUD();
    $this->Entregas_Model->get_rows($idCarga);
}

What am I doing wrong?

Solution

  • In order to simplicity you should change your Query Builder a little bit as below: 

    $this->db->from('entregas');
$this->db->join(/*Join Parameters*/);
$this->db->where('entregas.idIntervaloHorario', $idCarga);
$q = $this->db->count_all_results(); //q should contain integer that represent your records count

    In other situation that you want to get one record or all records you should add another function to get as below: 

    $this->db->get()->row(); //return 1 row
$this->db->get()->result(); //return all matched rows