Welcome Guest, Not a member yet? Register   Sign In
Active Record bug sets unintended apostroph
#1

[eluser]Unknown[/eluser]
Hello, i am using CI 2.1.0 and getting a strage Bug when using this Active Record:

Code:
$this->db->select('site.id, site.name, site.logo, stat1.fb_uid,stat1.likes, stat1.checkins, stat1.talking_about, stat1.created_at, CONVERT(((stat1.likes - stat2.likes) / stat2.likes * 100), dec(20,2)) AS likes_trend');
$this->db->from('fb_sites_statistics stat1, (SELECT stat2.likes FROM fb_sites_statistics stat2 WHERE fb_uid = '.$id.' AND stat2.created_at BETWEEN subdate(curdate(), interval 7 day) AND adddate(curdate(), interval 1 day) ORDER BY stat2.created_at ASC LIMIT 1) stat2, fb_sites site');
$this->db->where('stat1.fb_uid = '.$id.' AND site.fb_uid = stat1.fb_uid');
$this->db->order_by('stat1.created_at DESC');
$this->db->limit(1);
$query = $this->db->get();

This is generated SQL and the Error:

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`2))` AS likes_trend FROM (`fb_sites_statistics` stat1, (SELECT stat2.likes FROM' at line 1

SELECT `site`.`id`, `site`.`name`, `site`.`logo`, `stat1`.`fb_uid`, `stat1`.`likes`, `stat1`.`checkins`, `stat1`.`talking_about`, `stat1`.`created_at`, CONVERT(((stat1.likes - stat2.likes) / stat2.likes * 100), dec(20, `2))` AS likes_trend FROM (`fb_sites_statistics` stat1, (SELECT stat2.likes FROM fb_sites_statistics stat2 WHERE fb_uid = 307811577888 AND stat2.created_at BETWEEN subdate(curdate(), `fb_interval` 7 day) AND adddate(curdate(), `fb_interval` 1 day) ORDER BY stat2.created_at ASC LIMIT 1) stat2, `fb_sites` site) WHERE `stat1`.`fb_uid` = 307811577888 AND site.fb_uid = stat1.fb_uid ORDER BY `stat1`.`created_at` DESC LIMIT 1

Filename: /var/www/_labs/socialiq/models/site_statistics_model.php

Line Number: 60

Is there a trick or how can i turn off the ` ?
Same error occures when using CAST.
#2

[eluser]Jason Hamilton-Mascioli[/eluser]
Yes, try to set the select's second parameter to false (as shown in the docs) to resolve this ...

Code:
$this->db->select('(SELECT SUM(payments.amount) FROM payments WHERE payments.invoice_id=4') AS amount_paid', FALSE);
$query = $this->db->get('mytable');
#3

[eluser]Unknown[/eluser]
Thx for the fast help, didn't saw it in the docs. Smile




Theme © iAndrew 2016 - Forum software by © MyBB