Get query's row count in a Codeigniter application [duplicate]

Question

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

Basti · Accepted Answer · 2012-03-18 17:28:34Z

3

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

answered Mar 18, 2012 at 17:28

Basti

4,0511 gold badge21 silver badges22 bronze badges

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

Comments

mickmackusa · Accepted Answer · 2024-04-13 10:50:59Z

0

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

edited Apr 13, 2024 at 10:50

answered Apr 6, 2023 at 3:23

mickmackusa♦

49.2k13 gold badges98 silver badges165 bronze badges

Collectives™ on Stack Overflow

Get query's row count in a Codeigniter application [duplicate]

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related