• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Database Num_rows()?

#1
Hi all,

Stupid Question here: 
Is there a function to return num rows like there was in CI 3? or is it a case of having to run a SQL COUNT field in my queries? 
Am fine doing that, but just find it more convenient to have a num rows function or field already available. I have looked through the Database reference pages but did;t see it, unless I overlooked it somewhere.

BTW, am expecting some stupid, some know-it-all, even some answers that completely miss the question. But don't I won't take offence.  Big Grin
Reply

#2
$builder->where(...);
$builder->countAllResults();

or 

$builder->countAll(); counts everything in table.
Reply

#3
If you are in  a CI4 model you can use the
PHP Code:
$this->countAll() 
function.

Or if you used an instance of the base builder inside a model and you only want to count the rows that matches your criteria you can use
PHP Code:
$this->builder()->countAllResults(false
- the parameter false prevents the query from being reset.

you can find detailed information here https://codeigniter4.github.io/userguide...ng-results
Dirk B.
Abatrans Software
No SEO spam - see forum guidelines
Reply

#4
This has already been answered on the forums here:

DB doesn't have num_rows/numRows
What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )
Reply

#5
Thanks, I knew I was probably missing something somewhere.


For anyone remotely interested as it appears from what I have read, that there is no Num_Rows() function in CI 4, I have thrown together a quick and dirty function you can include in your models if needed. 

Note: I did NOT include any checking if the $table param was present or not, that you can write specifically for yourself.
Also only tested this for MySQL so other Databases I cannot vouch for. 

Code:

Code:
private function NumRows($table)
{
    //SQL for Counting rows in a query
    $sql = "SELECT COUNT(*) as Count FROM ".$table;

    //Execute the query and assign the result to the $row variable
    $result = $this->db->query($sql);
    $row = $result->getRow();
       
    //Get the count from the $row variable and return the result to the caller
    return $count = $row->Count;
}
Reply

#6
(03-02-2020, 03:21 PM)stlake2011 Wrote: Thanks, I knew I was probably missing something somewhere.


For anyone remotely interested as it appears from what I have read, that there is no Num_Rows() function in CI 4, I have thrown together a quick and dirty function you can include in your models if needed. 

Note: I did NOT include any checking if the $table param was present or not, that you can write specifically for yourself.
Also only tested this for MySQL so other Databases I cannot vouch for. 

Code:

Code:
private function NumRows($table)
{
    //SQL for Counting rows in a query
    $sql = "SELECT COUNT(*) as Count FROM ".$table;

    //Execute the query and assign the result to the $row variable
    $result = $this->db->query($sql);
    $row = $result->getRow();
       
    //Get the count from the $row variable and return the result to the caller
    return $count = $row->Count;
}

countAllResults and countAll do the same thing for you. I guess num_rows was performance killer so they removed it.
Reply

#7
(03-02-2020, 03:38 PM)tweenietomatoes Wrote:
(03-02-2020, 03:21 PM)stlake2011 Wrote: Thanks, I knew I was probably missing something somewhere.


For anyone remotely interested as it appears from what I have read, that there is no Num_Rows() function in CI 4, I have thrown together a quick and dirty function you can include in your models if needed. 

Note: I did NOT include any checking if the $table param was present or not, that you can write specifically for yourself.
Also only tested this for MySQL so other Databases I cannot vouch for. 

Code:

Code:
private function NumRows($table)
{
    //SQL for Counting rows in a query
    $sql = "SELECT COUNT(*) as Count FROM ".$table;

    //Execute the query and assign the result to the $row variable
    $result = $this->db->query($sql);
    $row = $result->getRow();
       
    //Get the count from the $row variable and return the result to the caller
    return $count = $row->Count;
}

countAllResults and countAll do the same thing for you. I guess num_rows was performance killer so they removed it.

If I was using the query builder (which I am not) then these work great, but i need something that I can use working directly with old fashioned SQL.
Reply

#8
The result returned from `getRows()` will always be an array. So a simple count() is all you need to see how many rows were returned.
Reply

#9
If you are using MySQL and not limited by a query builder. MySQL has SQL_CALC_FOUND_ROWS which will calculate the total number of results within the same query used to fetch the result set.

https://coderwall.com/p/n3b0iw/mysql-sql...erformance
Reply

#10
(03-02-2020, 09:23 PM)kilishan Wrote: The result returned from `getRows()` will always be an array. So a simple count() is all you need to see how many rows were returned.
Unless I'm missing something, it's not always "a simple count()". In some cases, queries can be enormously complicated with many tables joined and distinct or group by or sum/avg/max specified. The db method countAllResults does seem to take a lot of this into account, but the extra query that must be run also appears to obliterate any prior query results which might exist in the Builder object. And I'm not sure, but mightn't be some (probably very small) risk of a name collision between the numrows used as an alias for the COUNT(*) in the other query.

Perhaps most critically, this countAllResults function clearly contains more PHP code than a single mysqli_num_rows, sqlsrv_num_rows, or pg_num_rows function call and it requires another query to be run on the db server. In my experience, a db server is quite likely to be your bottleneck on a busy server. If the PHP client has some simple built-in function to return the number of rows corresponding to a query, why not use it? The coding effort to expose this function for the most popular database engines is probably trivial, and you can just throw an exception ("use countAllResults instead!") if the DBMS does not support such a function. I could be dead wrong, but I expect the vast majority of CI developers are using MySQLi, MS SQL, or PostGres, and all of those support this function.

To bolster my point, consider the PHP source code for the mysqli_num_rows function. It goes through a few macros but I think they end up just returning the value from a struct.
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2021 MyBB Group.