replace null or empty response from other static word in cakephp 3 query

Question

I am using cakephp 3 to replace with a static word in place of null/empty column value.

For Example :- I have a product table which contain id,price,image, I need to fetch all the records but which product's does not have image, it should return "no_image.jpg" in place of null/empty.

Like : SELECT if(image='','no_image.jpg', image), id, price FROM user_images. how can i convert above query in cakephp 3. Thanks in Advance.

https://stackoverflow.com/questions/49129058/cakephp-3-query-ifnull — Don't Panic, Aug 10 '18 at 17:35
Personally, I wouldn't do this in the query and would show no_image.jpg for null image values while outputting the records instead. You may have a good reason for doing it in the database, but just another option to consider. — Don't Panic, Aug 10 '18 at 17:38

score 0 · Answer 1 · answered Aug 10 '18 at 17:35

You can use NULLIF to set NULL when image = ''.

Then use COALESCE to return the second parameter if first is null.

SELECT COALESCE(NULLIF(image,''), 'no_image.jpg') as image, id, price FROM user_images

In other words, if image is NULL or '', it will return no_image.jpg,, else it will return the image itself.

NULLIF: https://www.w3schools.com/sql/func_mysql_nullif.asp
COALESCE: https://www.w3schools.com/sql/func_mysql_coalesce.asp

Of course, you can also return null/empty and put this logic in application, not in the query.

replace null or empty response from other static word in cakephp 3 query

1 Answers1