Neater way to extract 'count(id)' style entries from query->row() call

[eluser]jedd[/eluser]
I want to pull a count of a table, which is pretty straightforward, but the juggling of the return values just seems really quite messy. I expect there's a tidier CI way of doing this.

I have some code like this:

Code:

$query = $this->db->query ('SELECT COUNT(id)
                    FROM tablewotsit' );
$raw_count = (array)$query->row();
$actual_count  = $rawcount['COUNT(id)'];

I can't do the ['COUNT(id)'] bit to the $query-row directly. I tried not casting to array, and doing the -> thing to the 'COUNT(id)' string, but PHP choked with parenthesis in the attribute/key. If there were multiple responses I'd do the foreach $key=>$value thing and happily take $value, but that approach seems wrong given I know I'm dealing with just one row.

[eluser]JayTee[/eluser]

Code:

$raw_count = $this->db->query("select count(*) as ct from yourtable")->row(0)->ct;

[eluser]pistolPete[/eluser]
Be aware that Method Chaining only works with PHP 5.

[eluser]jedd[/eluser]
[quote author="JayTee" date="1235937401"]

Code:

$raw_count = $this->db->query("select count(*) as ct from yourtable")->row(0)->ct;

[/quote]

Ah, excellent, thanks. I've used 'AS' before, but only when trying to neaten out joins from tables with identical named columns. Didn't even think of it here.

Be aware that Method Chaining only works with PHP 5.

Which brings up that ol' hoary chestnut of how committed to writing PHP4-compatible code is the average CI user? I'm in two minds - the PHP4 side is encouraged by the fact that (pretty much) all CI example code uses PHP4-isms - and the PHP5 side by the fact that pretty much all my other reference material is based on PHP5.

For my own future readability I think I'll go halfway between my three lines and JayTee's one line, in this particular instance.

[eluser]xwero[/eluser]
the AR class of the database library has the methods count_all and count_all_results which give you a nicer way to get the row count.

[eluser]TheFuzzy0ne[/eluser]
Or if you are using MySQL and want execute the query once, you can use [url="http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows"]SQL_CALC_FOUND_ROWS[/url].

[eluser]jedd[/eluser]
Thanks for the two extra pointers.

I'm still not sure about AR with this project. I toyed with the ActiveRecord plugin from the wiki, but then went back to 'raw' CI db calls.

Similarly unsure about using MySQL-isms in my models .. just in case I ever get around to shifting this to postgres.

[eluser]JayTee[/eluser]
[quote author="xwero" date="1235950991"]the AR class of the database library has the methods count_all and count_all_results which give you a nicer way to get the row count.[/quote]

I was avoiding that one since he appeared to be wanting to use the db->query style. For those that are curious:

Code:

$raw_count = $this->db->count_all('my_table');

[eluser]TheFuzzy0ne[/eluser]
One more thing for you to ponder upon, Jedd. To my knowledge, using Active Record should keep your SQL portable, so switching to another type of database shouldn't affect your application in any way. Sometimes it's necessary to circumvent AR, but personally, I find it a total delight to work with, and it seems to make my SQL so much more readable, and is ideal for generating dynamic statements on the fly without the errors I used to get when I did it myself.