CodeIgniter Forums
Active Records / Query Help - How to use db->where [SOLVED] - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Active Records / Query Help - How to use db->where [SOLVED] (/showthread.php?tid=42245)

Active Records / Query Help - How to use db->where [SOLVED] - El Forum - 05-31-2011

[eluser]snowstar[/eluser]
Hi All,

I'm having trouble writing a query. It currently looks like this:

Code:
$this->db->select('id, date, clientid');
        $this->db->where('date', $date);
        $query = $this->db->get('tbl_sales');
        return $query->result();

I need the query to return data from a table where date matches my $date and where clientid is unique (its possible for the clientid to have a clients id appear more than once)

Any help is greatly appreciated

Active Records / Query Help - How to use db->where [SOLVED] - El Forum - 06-01-2011

[eluser]snowstar[/eluser]
Ive tried this but didn't work for me :

Code:
$query = $this->db->query("SELECT DISTINCT clientid FROM mytable where date=$date");
return $query->result();

Not sure if im using the where correctly or if i can use where in the query

Active Records / Query Help - How to use db->where [SOLVED] - El Forum - 06-01-2011

[eluser]snowstar[/eluser]
SOLUTION

Code:
function get_total_new_customers($date)
    {
$query = $this->db->query("SELECT DISTINCT clientid FROM mytable WHERE date = '$date'");    
        return $query->result();
    }

function get_total_existing_customers($date)
    {
        $query = $this->db->query("SELECT clientid, COUNT(*) as count
FROM mytable
WHERE date = '$date'
GROUP BY clientid
HAVING count > 1");    
        return $query->result();
    }

get_total_new_customers checks the db for new client id's where get_total_existing_customers counts for multiple client id's