CODEWITHSUNDEEP
phpmysqlcodeignitercountquery-builder
Jose
Jose

Reputation: 310

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?

Upvotes: 1

Views: 1831

Answers (3)

mickmackusa
mickmackusa

Reputation: 48100

You are returning the entire CI_DB_mysqli_result object because that is what get() returns ($q in your code).

Your object data indicates that one row was returned (as expected). To access and return the count value from your successful query, chain ->row()->count to get the count property from the first (only) row.

More ideally, you should use the following chained methods to return the row count as an int-type value.

public function get_rows(int $idCarga): int
{
    return $this->db
        ->join('intervalosHorarios ih', 'e.idCarga = ih.idIntervaloHorario')
        ->where('e.idIntervaloHorario', $idCarga);
        ->count_all_results('entregas e');
}

Upvotes: 0

Hossein Shahsahebi
Hossein Shahsahebi

Reputation: 7308

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

Upvotes: 2

Zartash Zulfiqar
Zartash Zulfiqar

Reputation: 143

Use below line to see information related to database:

$this->output->enable_profiler(TRUE);

It will show all the database query running in current call stack. Or you can use below line after the query run, to print only query.

$this->db->last_query();

Upvotes: 1

Related Questions