Using IFNULL with CONCAT in a Codeigniter ActiveRecord Query

Question

I'm using the Codeigniter ActiveRecord ORM to conduct a query that will select some data from my database. One of the bits of data in my database table is from the column called updated_by which is represented by an integer from the users table which tells which user updated that particular record. This integer is currently a int(11) that defaults to NULL when the initial record is created and can obviously have a value of NULL for the column.

When the query is ran I do not get a value for updated_by field. Is there something I'm doing wrong?

$this->db->select("IFNULL(CONCAT_WS(' ', updator.first_name, updator.last_name), '----') AS updated_by", FALSE);

For the comments made below this was my echoed query statement.

SELECT `pages`.`page_id`, `pages`.`page_name`, `pages`.`created_at`, `pages`.`updated_at`, `statuses`.`status_name`, CONCAT_WS(' ', creator.first_name, creator.last_name) AS created_by, IFNULL(CONCAT_WS(' ', updator.first_name, updator.last_name), '----') AS updated_by FROM (`pages`) JOIN `users` AS creator ON `creator`.`user_id` = `pages`.`created_by` LEFT JOIN `users` AS updator ON `updator`.`user_id` = `pages`.`updated_by` JOIN `statuses` ON `statuses`.`status_id` = `pages`.`status_id`

@Nil'z do you have a macro for posting this comment? :D Don't get me wrong, I consider it a good idea. — Patrick Manser
– Patrick Manser, Commented Oct 24, 2013 at 14:14
Nothing like that, just don't like complexity firsthand. @PatrickManser — Nil'z
– Nil'z, Commented Oct 24, 2013 at 14:24
@user2576961 Did you run this query in phpmyadmin/console to test it? — Patrick Manser
– Patrick Manser, Commented Oct 24, 2013 at 14:38

user2844810 · Accepted Answer · 2013-10-24 20:32:14Z

0

The problem basically is that CONCAT_WS() will never return NULL, you can run the query

SELECT CONCAT_WS(' ',NULL,NULL);

And it will always return a blankspace, therefore if there is not updater.firstname or updater.last_name it will return you a blankspace instead of the NULL value you're expecting to get.

You can do this by changing your IFNULL to a simple IF

IF(updator.firstname IS NULL, '----', CONCAT_WS(' ', updator.first_name, updator.last_name))

answered Oct 24, 2013 at 20:32

user2844810

9857 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using IFNULL with CONCAT in a Codeigniter ActiveRecord Query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related