CODEWITHSUNDEEP
phpcodeigniteractiverecordrowcount
Nyxynyx
Nyxynyx

Reputation: 63619

Get query's row count in a Codeigniter application

I'm using $this->db->get()->row_array() in Codeigniter to get a single row of results from the database. I want to merge the results from all the different queries into a single array $results; I don't want to have to type in the column name to get the result of a single row.

PHP Code

// Query 1
$results = array();
$this->db->select('COUNT(listing_id) as num')
         ->from('listings')
         ->where('city', $city);
$result = $this->db->get()->row_array();
$results['num'] = $result['num'];

Is there something more succinct?

Maybe a two-liner?

$result = $this->db->get()->row_array();
$results['num'] = $result['num'];

Ideally, a one-liner would be great!

$results['num'] = first_element_of($this->db->get()->row_array());

Upvotes: 2

Views: 42742

Answers (2)

mickmackusa
mickmackusa

Reputation: 47894

Codeigniter has a dedicated helper method for counting rows in a result set.

count_all_results() will unconditionally return a non-negative integer.

All querying should be done in the model, so your model method can be:

public function countCityListings(int $city): int
{
    return $this->db->where('city', $city)->count_all_results('listings');
}

Adding that return value to your controller's payload variable to be passed to the view can be:

$result['num'] = $this->your_model->countCityListings($city);

Upvotes: 0

Basti
Basti

Reputation: 4042

Don't know codeigniter and have never worked with it but this might work

// Query 1
$results = array();
$this->db->select('COUNT(listing_id) as num')
        ->from('listings')
        ->where('city', $city);

$results['num'] = $this->db->get()->row()->num;

The trick is that you can chain object member access. You cannot do this with arrays ($foo->row_array()['num']), so that's the problem here. If you were using good old mysql you should have a look at mysql_result. There is no mysqli equivalent for this.

Depending on the return value of where() you can try and shorten it further to 

$results = array('num' => 
    $this->db->select('COUNT(listing_id) as num')
        ->from('listings')
        ->where('city', $city)
        ->get()
        ->row()
        ->num
);

Upvotes: 3

Related Questions