CODEWITHSUNDEEP
phpsqlcodeigniteractiverecordrowcount
Kirill Ivanov
Kirill Ivanov

Reputation: 77

Get row's distinct count from query result with Codeigniter

My model query:

public function getWorkDays ($id, $month) {
    $query = $this->db->query("SELECT count(DISTINCT (day)) FROM hours WHERE userid = ? AND month = ?", array($id, $month));
    $row = $query->result();
    return $row[0];
}

My view:

<p>Total Days: <?php echo var_dump($workDays); ?></p>

The error:

Total Days: object(stdClass)#35 (1) { ["count(DISTINCT (day))"]=> string(1) "9" }

9 should be a correct value but I can't get it with a simple result echo. Also I'm interested why do I have a part of my query at the result obj (["count(DISTINCT (day))"]).

I don't want to use Active record the query should stay like it is.

Upvotes: 2

Views: 638

Answers (1)

iam-decoder
iam-decoder

Reputation: 2564

The problem is, CodeIgniter will return a StdClass Object by default rather than an array, so if you want to use return $row[0];, you'll need to use an array returning function.

from CodeIgniter's Documentation:

use result_array()

This function returns the query result as a pure array, or an empty array when no result is produced. Typically you'll use this in a foreach loop, like this:

$query = $this->db->query("YOUR QUERY");

foreach ($query->result_array() as $row)
{
   echo $row['title'];
   echo $row['name'];
   echo $row['body'];
}

you could do something like this:

public function getWorkDays ($id, $month){
    $query = $this->db->query("SELECT count(DISTINCT day) as workDays FROM hours WHERE userid = ? AND month = ?", array($id, $month));
    $row = $query->result_array();
    return $row[0];
}

You also said you wanted a single result, it might even be easier for you to do this:

public function getWorkDays ($id, $month){
    $query = $this->db->query("SELECT count(DISTINCT day) as workDays FROM hours WHERE userid = ? AND month = ?", array($id, $month));
    return $query->row_array();
}

EDIT:

Here's for your simple echo...

public function getWorkDays ($id, $month){
    $query = $this->db->query("SELECT count(DISTINCT day) as workDays FROM hours WHERE userid = ? AND month = ?", array($id, $month));
    $row = $query->row_array();
    return $row['workDays'];
}

Upvotes: 2

Related Questions