Help with this SELECT statement

[eluser]jerry4christ[/eluser]
Please help me check this, what am I doing wrong.

Code:

$this->db
   ->select("blog.*, blog_categories.title AS category_title, blog_categories.slug AS category_slug")
   ->select("SUM(IF(comments.is_active = 1,1,0)) total_comments")
   ->select('users.username, profiles.display_name')
   ->join('blog_categories', 'blog.category_id = blog_categories.id', 'left')
   ->join('comments', 'comments.entry_id = blog.id', 'left')
   ->join('profiles', 'profiles.user_id = blog.author_id', 'left')
   ->join('users', 'blog.author_id = users.id', 'left')
   ->order_by('created_on', 'DESC');

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

[eluser]rufnex[/eluser]
try with "as":

Code:

->select("SUM(IF(comments.is_active = 1,1,0)) as total_comments")

[eluser]jerry4christ[/eluser]
[quote author="rufnex" date="1413489548"]try with "as":

Code:

->select("SUM(IF(comments.is_active = 1,1,0)) as total_comments")

[/quote]

I've tried with as and still get the same error.

[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")

[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")

[/quote]

Code:

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` <= 1413529819 ORDER BY `created_on` DESC LIMIT 100

Same result. don't know why this is showing up `0))`

[eluser]rufnex[/eluser]
next try ;o)

Code:

->select("SUM(IF(comments.is_active = '1',1,0)) total_comments", false)

[eluser]jerry4christ[/eluser]
[quote author="rufnex" date="1413537883"]next try ;o)

Code:

->select("SUM(IF(comments.is_active = '1',1,0)) total_comments", false)

[/quote]


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:

[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-gu...tml#select