![]() |
Help with this SELECT statement - 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: Help with this SELECT statement (/showthread.php?tid=61208) |
Help with this SELECT statement - El Forum - 10-16-2014 [eluser]jerry4christ[/eluser] Please help me check this, what am I doing wrong. Code: $this->db I keep getting this error: A Database Error Occurred 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 '`total_comments`, `default_users`.`username`, `default_profiles`.`display_name` ' at line 1 SELECT `default_blog`.*, `default_blog_categories`.`title` AS `category_title`, `default_blog_categories`.`slug` AS `category_slug`, SUM(IF(comments.is_active = 1, `1 0))` `total_comments`, `default_users`.`username`, `default_profiles`.`display_name` FROM `default_blog` LEFT JOIN `default_blog_categories` ON `default_blog`.`category_id` = `default_blog_categories`.`id` LEFT JOIN `default_comments` ON `default_comments`.`entry_id` = `default_blog`.`id` LEFT JOIN `default_profiles` ON `default_profiles`.`user_id` = `default_blog`.`author_id` LEFT JOIN `default_users` ON `default_blog`.`author_id` = `default_users`.`id` WHERE `status` = 'live' AND `default_blog`.`created_on` <= 1413476587 ORDER BY `created_on` DESC LIMIT 100 Help with this SELECT statement - El Forum - 10-16-2014 [eluser]rufnex[/eluser] try with "as": Code: ->select("SUM(IF(comments.is_active = 1,1,0)) as total_comments") Help with this SELECT statement - El Forum - 10-16-2014 [eluser]jerry4christ[/eluser] [quote author="rufnex" date="1413489548"]try with "as": Code: ->select("SUM(IF(comments.is_active = 1,1,0)) as total_comments") I've tried with as and still get the same error. Help with this SELECT statement - El Forum - 10-16-2014 [eluser]rufnex[/eluser] Sorry as is not relevant here (was late on yesterday ;o) Try to set ' in your condition: Code: ->select("SUM(IF(comments.is_active = '1',1,0)) total_comments") Help with this SELECT statement - El Forum - 10-17-2014 [eluser]jerry4christ[/eluser] [quote author="rufnex" date="1413527527"]Sorry as is not relevant here (was late on yesterday ;o) Try to set ' in your condition: Code: ->select("SUM(IF(comments.is_active = '1',1,0)) total_comments") Code: A Database Error Occurred Same result. don't know why this is showing up `0))` Help with this SELECT statement - El Forum - 10-17-2014 [eluser]rufnex[/eluser] next try ;o) Code: ->select("SUM(IF(comments.is_active = '1',1,0)) total_comments", false) Help with this SELECT statement - El Forum - 10-17-2014 [eluser]jerry4christ[/eluser] [quote author="rufnex" date="1413537883"]next try ;o) Code: ->select("SUM(IF(comments.is_active = '1',1,0)) total_comments", false) Now you just hit the Jackpot!.... Thanks man;-) but could you please explain what role that false is playing there Nooooo.... the condition affected the selection on the parent table(blog). it's not supposed to filter the blog:down: Help with this SELECT statement - El Forum - 10-17-2014 [eluser]rufnex[/eluser] Form the documentation: "$this->db->select() accepts an optional second parameter. If you set it to FALSE, CodeIgniter will not try to protect your field or table names with backticks. This is useful if you need a compound select statement." https://ellislab.com/codeigniter/user-guide/database/active_record.html#select |