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.


$record = '123';



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

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



should go a long way towards it.

Wednesday, March 31, 2021
answered 7 Months ago

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
answered 7 Months ago

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);
        $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 -> "stated in github that it will be fixed in CodeIgniter 3.0 the fix already exists in that repository"

Wednesday, March 31, 2021
answered 7 Months ago

For you particular data, you could do:

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

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
answered 5 Months ago

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

    select distinct profile_id 
    from userprofile_...

    union all

    select distinct profile_id 
    from productions_...
) x
Monday, August 9, 2021
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 :