Asked  2 Years ago    Answers:  5   Viewed   81 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

3
$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.

Tuesday, October 25, 2022
4

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.

Friday, October 7, 2022
4

As default a MySQL Server is configured that he allowes only a binding from localhost.

Go to your my.cnf and comment the line

bind 127.0.0.1

Then you should look that your MySQL user has enough rights to access from other ips.

MySQL Remote Access

Thursday, November 10, 2022
2

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"

Thursday, December 1, 2022
 
3

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.

Tuesday, August 2, 2022
 
3

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
Tuesday, October 4, 2022
 
Only authorized users can answer the search term. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 

Browse Other Code Languages