Convert my sql query to codeigniter syntax

I have a query which is:

SELECT COALESCE(sum(`lead_att`.`cost`), 0) as sale
FROM `lead_att`
JOIN `leads` ON `lead_att`.`lead_id` = `leads`.`lead_id`
WHERE (
    `leads`.`test_mode` = FALSE
    AND `lead_att`.`status` NOT IN (8, 9)
    AND str_to_date(date_format(`lead_att`.`created_at`, '%Y-%m-%d %H:%i'), '%Y-%m-%d %H:%i') BETWEEN str_to_date(date_format(convert_tz( DATE_FORMAT(subdate(CURDATE(), 1), '%Y-%m-%dT04:00:00'), '+00:00', '-04:00'), '%Y-%m-%d %H:%i'), '%Y-%m-%d %H:%i') AND str_to_date(date_format(convert_tz( DATE_FORMAT(CURDATE(), '%Y-%m-%dT03:59:00'), '+00:00', '-04:00'), '%Y-%m-%d %H:%i'), '%Y-%m-%d %H:%i')
    AND `leads`.`source` = 4
  )
LIMIT 2000


Where I need to translate to codeigniter format.

Also, if possible, I have to make the CURDATE() be a dynamic date variable which I include - meaning I could include any date range and it would change the query accordingly. Anyone have any ideas on how I can do this? Thanks

@andreaccident,

There are multiple ways to translate your query to CI.  Here is one way... (https://codeigniter.com/userguide3/datab...y-bindings)

PHP Code:

$sql = "SELECT COALESCE(sum(`lead_att`.`cost`), 0) as sale
        FROM `lead_att`
        JOIN `leads` ON `lead_att`.`lead_id` = `leads`.`lead_id`
        WHERE (
            `leads`.`test_mode` = FALSE
            AND `lead_att`.`status` NOT IN (8, 9)
            AND str_to_date(date_format(`lead_att`.`created_at`, '%Y-%m-%d %H:%i'), '%Y-%m-%d %H:%i') BETWEEN str_to_date(date_format(convert_tz( 
            DATE_FORMAT(subdate(CURDATE(), 1), '%Y-%m-%dT04:00:00'), '+00:00', '-04:00'), '%Y-%m-%d %H:%i'), '%Y-%m-%d %H:%i') AND 
            str_to_date(date_format(convert_tz( DATE_FORMAT(CURDATE(), '%Y-%m-%dT03:59:00'), '+00:00', '-04:00'), '%Y-%m-%d %H:%i'), '%Y-%m-%d %H:%i')
            AND `leads`.`source` = 4
             )
        LIMIT 2000";
$this->db->query($sql); 


PHP Code:

$sql = "SELECT COALESCE(sum(`lead_att`.`cost`), 0) as sale
        FROM `lead_att`
        JOIN `leads` ON `lead_att`.`lead_id` = `leads`.`lead_id`
        WHERE (
            `leads`.`test_mode` = FALSE
            AND `lead_att`.`status` NOT IN (8, 9)
            AND str_to_date(date_format(`lead_att`.`created_at`, '%Y-%m-%d %H:%i'), '%Y-%m-%d %H:%i') BETWEEN str_to_date(date_format(convert_tz( 
            DATE_FORMAT(subdate(CURDATE(), 1), '%Y-%m-%dT04:00:00'), '+00:00', '-04:00'), '%Y-%m-%d %H:%i'), '%Y-%m-%d %H:%i') AND 
            str_to_date(date_format(convert_tz( DATE_FORMAT(CURDATE(), '%Y-%m-%dT03:59:00'), '+00:00', '-04:00'), '%Y-%m-%d %H:%i'), '%Y-%m-%d %H:%i')
            AND `leads`.`source` = 4
             )
        LIMIT 2000";
$this->db->query($sql); 


I tried doing it this way but the thing is that I need it in the codeigniter syntax form because I need to make edits in certain strings of the query. Is there a way for this? Thanks so much in advance

@andreaccident,

Do you want to add variables to it? Did you checkout the link? Or the documentations? It will help you to convert it.

Code:

$this->db->select('COALESCE(sum(`lead_att`.`cost`), 0) as sale');
$this->db->from('lead_att');
$this->db->join('leads', 'lead_att.lead_id = leads.lead_id');
$this->db->group_start();
$this->db->where('leads.test_mode', 'FALSE');
$this->db->where_not_in('lead_att.status, array(8,9));
$this->db->where('str_to_date(date_format(`lead_att`.`created_at`, '%Y-%m-%d %H:%i'), '%Y-%m-%d %H:%i') BETWEEN str_to_date(date_format(convert_tz( DATE_FORMAT(subdate(CURDATE(), 1), '%Y-%m-%dT04:00:00'), '+00:00', '-04:00'), '%Y-%m-%d %H:%i'), '%Y-%m-%d %H:%i') AND str_to_date(date_format(convert_tz( DATE_FORMAT(CURDATE(), '%Y-%m-%dT03:59:00'), '+00:00', '-04:00'), '%Y-%m-%d %H:%i'), '%Y-%m-%d %H:%i')', null, false);
$this->db->where('leads.source', 4);
$this->db->group_end();
$this->db->limit(2000);

Didn't really tested but it would be something like this.

Website: marcomonteiro.net  | Blog: blog.marcomonteiro.net | Twitter: @marcogmonteiro | TILThings: tilthings.com