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

Question

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

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?

instead $this->db->get() use $this->db->count_all_results() if you want to have count, else if you want records use $this->db->get()->result() and if you want just one row, use $this->db->get()->row() — Hossein Shahsahebi
– Hossein Shahsahebi, Commented May 10, 2017 at 11:54
change $this->db->select('COUNT(idCarga)'); to $this->db->select('COUNT(idCarga) as total_records'); then print this $q = $this->db->get()->row()->total_records; — Ranjeet Singh
– Ranjeet Singh, Commented May 10, 2017 at 11:54
@HosseinShahsahebi Jose is already couting the result in " COUNT(idCarga) " no need of $this->db->count_all_results() — Ranjeet Singh
– Ranjeet Singh, Commented May 10, 2017 at 11:55
@RanjeetSingh So he should remove the select part in order to simplicity — Hossein Shahsahebi
– Hossein Shahsahebi, Commented May 10, 2017 at 11:56

Hossein Shahsahebi · Accepted Answer · 2017-05-10 12:21:20Z

2

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

answered May 10, 2017 at 12:21

Hossein Shahsahebi

7,3885 gold badges26 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Jose Over a year ago

Come back to the trouble... after adding new items the query always return 1, this is crazy, i have tried all :(

Jose Over a year ago

Solved! in order to make a better query:

public function get_rows($idCarga) {          $query = $this->db->query('SELECT * FROM entregas LEFT JOIN intervaloshorarios on entregas.idCarga = intervaloshorarios.idIntervaloHorario         WHERE entregas.idIntervaloHorario = ' . $idCarga . ';');          echo $query->num_rows();           return $query;     }

Hossein Shahsahebi Over a year ago

That's not a good idea, because you get all rows and count them cause your server to be involved a lot

Jose Over a year ago

Better with (left join added)?

$this->db->from('entregas'); $this->db->join(/*Join Parameters with LEFT JOIN added*/); $this->db->where('entregas.idIntervaloHorario', $idCarga); $q = $this->db->count_all_results();

Hossein Shahsahebi Over a year ago

Reason could be a problem or other type of issue in your join, take a look and test it without join

Zartash Zulfiqar · Accepted Answer · 2017-05-10 12:09:09Z

1

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();

answered May 10, 2017 at 12:09

Zartash Zulfiqar

1437 bronze badges

Comments

mickmackusa · Accepted Answer · 2024-09-02 03:16:12Z

0

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');
}

answered Sep 2, 2024 at 3:16

mickmackusa♦

49.2k13 gold badges98 silver badges165 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related