如何在codeigniter中执行关于COUNT查询的num_rows() ?

This works:

如此:

        $sql = "SELECT id
                FROM `users`
                WHERE `account_status` = '" . $i . "'"; 
        $query = $this->db->query($sql);
        var_dump($query->num_rows());

But this doesn't:

但这并不是:

        $sql = "SELECT COUNT(*)
                FROM `users`
                WHERE `account_status` = '" . $i . "'"; 
        $query = $this->db->query($sql);
        var_dump($query->num_rows());

How to do a num_rows on a COUNT(*) query? Also is doing it the 2nd way any better performance wise?

如何对COUNT(*)查询执行num_rows ?还有就是第二种方式更好的表现吗?

8 个解决方案

#1

Doing a COUNT(*) will only give you a singular row containing the number of rows and not the results themselves.

执行COUNT(*)只会给您一个单独的行，其中包含行数，而不是结果本身。

To access COUNT(*) you would need to do

要访问COUNT(*)，您需要这样做

$result = $query->row_array();
$count = $result['COUNT(*)'];

The second option performs much better since it does not need to return a dataset to PHP but instead just a count and therefore is much more optimized.

第二个选项执行得更好，因为它不需要将数据集返回给PHP，而是只需要一个计数，因此更优化。

#2

In CI it's really simple actually, all you need is

在CI中，其实很简单，你需要的就是。

$this->db->where('account_status', $i);
$num_rows = $this->db->count_all_results('users');
var_dump($num_rows); // prints the number of rows in table users with account status $i

#3

$query->num_rows()

The number of rows returned by the query. Note: In this example, $query is the variable that the query result object is assigned to:

查询返回的行数。注意:在本例中，$query是分配给查询结果对象的变量:

$query = $this->db->query('SELECT * FROM my_table');

echo $query->num_rows();

#4

num_rows on your COUNT() query will literally ALWAYS be 1. It is an aggregate function without a GROUP BY clause, so all rows are grouped together into one. If you want the value of the count, you should give it an identifier SELECT COUNT(*) as myCount ..., then use your normal method of accessing a result (the first, only result) and get it's 'myCount' property.

您的COUNT()查询中的num_rows将始终为1。它是一个没有GROUP BY子句的聚合函数，因此所有的行都被分组为一个。如果您想要计数的值，您应该给它一个标识符SELECT count(*)作为myCount…，然后使用访问结果的常规方法(第一个，唯一的结果)并获取它的“myCount”属性。

#5

This will only return 1 row, because you're just selecting a COUNT(). you will use mysql_num_rows() on the $query in this case.

这将只返回一行，因为您只是选择COUNT()。在本例中，您将在$查询中使用mysql_num_rows()。

If you want to get a count of each of the ID's, add GROUP BY id to the end of the string.

如果您想获得每个ID的计数，请在字符串的末尾按ID添加组。

Performance-wise, don't ever ever ever use * in your queries. If there is 100 unique fields in a table and you want to get them all, you write out all 100, not *. This is because * has to recalculate how many fields it has to go, every single time it grabs a field, which takes a lot more time to call.

在性能方面，永远不要在查询中使用*。如果一个表中有100个唯一字段，而您想要全部获取它们，那么您将全部写出100，而不是*。这是因为*必须重新计算它要去多少个字段，每次它夺取一个字段时，调用的时间要长得多。

#6

I'd suggest instead of doing another query with the same parameters just immediately running a SELECT FOUND_ROWS()

我建议不要使用相同的参数执行另一个查询，而是直接运行SELECT FOUND_ROWS()

#7

    $list_data = $this->Estimate_items_model->get_details(array("estimate_id" => $id))->result();
    $result = array();
    $counter = 0;
    $templateProcessor->cloneRow('Title', count($list_data));
    foreach($list_data as $row) {
        $counter++;
        $templateProcessor->setValue('Title#'.$counter, $row->title);
        $templateProcessor->setValue('Description#'.$counter, $row->description);
        $type = $row->unit_type ? $row->unit_type : "";
        $templateProcessor->setValue('Quantity#'.$counter, to_decimal_format($row->quantity) . " " . $type);
        $templateProcessor->setValue('Rate#'.$counter, to_currency($row->rate, $row->currency_symbol));
        $templateProcessor->setValue('Total#'.$counter, to_currency($row->total, $row->currency_symbol));   
    }

#8

$query = $this->db->get();
if ($query->num_rows() > 0) {
   echo 'have row';
} else {
   echo 'no row return from db';
}

#1