CodeIgniter Forums
return num_rows and query results - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: return num_rows and query results (/thread-19150.html)



return num_rows and query results - El Forum - 05-29-2009

[eluser]Samuurai[/eluser]
Hi everyone!

From the documentation, it says to do this to return the number of rows:
Code:
$query = $this->db->query('SELECT * FROM my_table');

echo $query->num_rows();

I want to also run this query

Code:
$result = $this->db->like('error_string', 'match');

How can I make it so my function only hits the database once in my model?

Thanks everyone.


return num_rows and query results - El Forum - 05-29-2009

[eluser]TheFuzzy0ne[/eluser]
I know that if you're using MySQL, in which case you're query will be something like this:
Code:
$query = $this->db->query('SELECT SQL_CALC_FOUND_ROWS * FROM my_table');

But you'll still have to run a:
Code:
$query = $this->db->query('SELECT FOUND_ROWS()');

It's certainly more efficient than running the query twice. This might work on other database platforms, but I'm not sure.


return num_rows and query results - El Forum - 05-29-2009

[eluser]Samuurai[/eluser]
[quote author="TheFuzzy0ne" date="1243635521"]
Code:
$query = $this->db->query('SELECT SQL_CALC_FOUND_ROWS * FROM my_table');
[/quote]

Wow - advanced stuff! I was more hoping to sort it out at the PHP level..

For instance, just using regular PHP, you can do this:
Code:
$result = mysql_query("SELECT * FROM table1", $link);
$num_rows = mysql_num_rows($result);
// Do foreach() with $result

Is this possible with the active record functions? - I'm still trying to figure out how to transport the data around the MVC architecture .


return num_rows and query results - El Forum - 05-29-2009

[eluser]TheFuzzy0ne[/eluser]
No, it's not possible to do this natively with the AR class, as SQL_CALC_FOUND_ROWS is not supported by all database platforms natively.

The same thing using the Active Record class might go something like this:
Code:
$this->db->select('SQL_CALC_FOUND_ROWS *', FALSE);
$res = $this->db->get('my_table');
$res_count = $this->db->query('SELECT FOUND_ROWS()');



return num_rows and query results - El Forum - 05-29-2009

[eluser]Samuurai[/eluser]
Thanks for that...

I'm a bit confused though... why can't I just count the array somehow at the PHP level, like the standard mysql_num_rows..?

I'm happy to just use the PHP functions rather than AR.. I think this will have to be done in the controller though..unless it's possible for a function in the model to return two values, one for the array containing the results and one for the number of rows..?


return num_rows and query results - El Forum - 05-29-2009

[eluser]TheFuzzy0ne[/eluser]
[quote author="Samuurai" date="1243637809"]Thanks for that...[/quote]

You're welcome...

[quote author="Samuurai" date="1243637809"]I'm a bit confused though... why can't I just count the array somehow at the PHP level, like the standard mysql_num_rows..?[/quote]

Scalability. If you have 10,000 rows in your database, you don't want to get all 10,000 rows, do you? If the table is going to be small, and stay small, and you know this for a fact, then yes, you can count the results, but that's what num_rows() is for.

[quote author="Samuurai" date="1243637809"]I'm happy to just use the PHP functions rather than AR.. I think this will have to be done in the controller though..unless it's possible for a function in the model to return two values, one for the array containing the results and one for the number of rows..?[/quote]

You can only return a single result, but there's nothing stopping you returning whatever you want in an object or an array.
Code:
return array(
        'total_rows' => $total_rows,
        'results' => $res->result_array()
    );



return num_rows and query results - El Forum - 05-29-2009

[eluser]Samuurai[/eluser]
Ahh.. durrr, I just realised, if I'm paginating, mysql_num_rows will only show me 10 (or whatever i'm LIMITing it to).

Your solution is much more betterer (sic) Smile

I'll have a tinker with that.. thanks a lot (for the second time today ! Smile)


return num_rows and query results - El Forum - 05-29-2009

[eluser]TheFuzzy0ne[/eluser]
This is the beauty of SQL_CALC_FOUND_ROWS. It basically ignores the LIMIT clause, so you'll get the total number of rows matched by your query.
Code:
function get($where=array(), $limit=25, $offset=0)
{
    $this->db->select('SQL_CALC_FOUND_ROWS *');
    $this->db->where($where);
    $this->db->limit($limit, $offset);
    $res = $this->db->get('my_table');
        
    $total_rows = $this->db->query('SELECT FOUND_ROWS()');
    $total_rows = $total_rows->result_array();
        
    return array(
            $res->result_array(),
            $total_rows[0]['FOUND_ROWS()']
        );
}

Try it, you might be pleasantly surprised, just remember that I think this is only supported by MySQL. If you want it to be platform independent, you'll need to do a separate call to count_all_results() and hit the database twice. This isn't a limit imposed by the library, but rather by the database platform.