Asked  7 Months ago    Answers:  5   Viewed   39 times

I'm trying to retrieve a count of all unique values in a field.

Example SQL:

SELECT count(distinct accessid) FROM (`accesslog`) WHERE record = '123'

How can I do this kind of query inside of CodeIgniter?

I know I can use $this->db->query(), and write my own SQL query, but I have other requirements that I want to use $this->db->where() for. If I use ->query() though I have to write the whole query myself.

 Answers

97
$record = '123';

$this->db->distinct();

$this->db->select('accessid');

$this->db->where('record', $record); 

$query = $this->db->get('accesslog');

then

$query->num_rows();

should go a long way towards it.

Wednesday, March 31, 2021
 
samrap
answered 7 Months ago
27

For me the issue was in the php.ini file. The property mysql.default_socket was pointing to file in a non-existent directory. The property was pointing to /var/mysql/mysql.sock but in OSX, the file was located in /tmp/mysql.sock.

Once I updated the entry in php.ini and restarted the webserver, the issue was resolved.

Wednesday, March 31, 2021
 
jcubic
answered 7 Months ago
81

Don't be afraid to change core files, just alter FCPATH/system/database/drivers/mysqli/mysqli_driver.php

function escape_str($str, $like = FALSE)
{
    if (is_array($str))
    {
        foreach ($str as $key => $val)
        {
            $str[$key] = $this->escape_str($val, $like);
        }

        return $str;
    }

    if (function_exists('mysqli_real_escape_string') AND is_object($this->conn_id))
    {
        $str = mysqli_real_escape_string($this->conn_id, $str);
    }
    else
    {
        $str = addslashes($str);
    }

    // escape LIKE condition wildcards
    if ($like === TRUE)
    {
        $str = str_replace(array('%', '_'), array('\%', '\_'), $str);
    }

    return $str;
}

I had the same issue


Better solution -> https://ellislab.com/forums/viewthread/228288/ "stated in github that it will be fixed in CodeIgniter 3.0 the fix already exists in that repository"

Wednesday, March 31, 2021
 
themihai
answered 7 Months ago
36

For you particular data, you could do:

order by (case when cat_type = 'free' then id*1.0
               else 2*id - 7.5
          end)

This seems very arcane. If you have a more general problem, you should ask another question, giving a better description of what you really want to accomplish.

Saturday, May 29, 2021
 
BetaRide
answered 5 Months ago
32

If you want a total count for all records, then you would do this:

SELECT COUNT(*)
FROM
(
    select distinct profile_id 
    from userprofile_...

    union all

    select distinct profile_id 
    from productions_...
) x
Monday, August 9, 2021
 
learningpython
answered 3 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :