+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: Convert my sql query to codeigniter syntax (/showthread.php?tid=76944)
Convert my sql query to codeigniter syntax - andreaccident - 07-06-2020
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
RE: Convert my sql query to codeigniter syntax - php_rocs - 07-06-2020
@andreaccident,
There are multiple ways to translate your query to CI. Here is one way... (https://codeigniter.com/userguide3/database/queries.html#query-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);
RE: Convert my sql query to codeigniter syntax - andreaccident - 07-06-2020
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
RE: Convert my sql query to codeigniter syntax - php_rocs - 07-06-2020
@andreaccident,
Do you want to add variables to it? Did you checkout the link? Or the documentations? It will help you to convert it.
RE: Convert my sql query to codeigniter syntax - marcogmonteiro - 07-07-2020
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.