Get records count within query

[eluser]nuwanda[/eluser]
If I create a query to return records can I also get a count of all records in the same query?

Yes, I know CI has functions to get a count of all records but that requires a new query.

[eluser]Bart v B[/eluser]
something like this?

Code:

<?php
$this->db->query("SELECT COUNT(*) AS numrows,
                         some_thing,
                         other_thing
                         FROM table
                         WHERE
                         foo = '".$this->input->post('something')."'");
?>

[eluser]Atharva[/eluser]
If you are talking about count query for pagination, then you will have to write a separate query.

[eluser]nuwanda[/eluser]
Yep, sure, sql will get records to the LIMIT clause then stop. That saves processing.

I suspect this is an old problem.

[eluser]nuwanda[/eluser]
For clarity, this returns two records:

Code:

$query = '';
    $query .= "SELECT links.*, users.user_name, tags.tag_id ";
    $query .= "FROM links ";
    $query .= "INNER JOIN tags ";
    $query .= "ON tags.tag_name = '" . $tag . "' ";
    $query .= "INNER JOIN terms ";
    $query .= "ON links.link_id = terms.terms_link_id AND terms.terms_tag_id = tags.tag_id ";
    $query .= "INNER JOIN users ";
    $query .= "ON links.link_user = users.user_id";

But if I add a count:

Code:

$query = '';
    $query .= "SELECT count(*) as tags_count, links.*, users.user_name, tags.tag_id ";
    $query .= "FROM links ";
    $query .= "INNER JOIN tags ";
    $query .= "ON tags.tag_name = '" . $tag . "' ";
    $query .= "INNER JOIN terms ";
    $query .= "ON links.link_id = terms.terms_link_id AND terms.terms_tag_id = tags.tag_id ";
    $query .= "INNER JOIN users ";
    $query .= "ON links.link_user = users.user_id";

tags_count returns 2 as expected but the query outputs only one record.

It's baffling. This is despite no LIMIT being involved.

[eluser]InsiteFX[/eluser]
And how are you returning the query?

You need to show the rest of the code so we can help you.

Also show your view code.

InsiteFX

[eluser]Madmartigan1[/eluser]
Not for nothin, but phps built-in count() function should be all you need.

Code:

$rows = $this->db->where('something', $something)->get('my_table');

$count = count($rows);

And as Atharva said:

If you are talking about count query for pagination, then you will have to write a separate query.

EDIT: Misread OP. No you can't count ALL records in the same query.

[eluser]InsiteFX[/eluser]
CodeIgniter User Guide - Generating Query Results

Code:

$count = $query->num_rows();

InsiteFX