CodeIgniter Forums
MySQL Date_Format and Query Builder - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: CodeIgniter 4 (https://forum.codeigniter.com/forum-28.html)
+--- Forum: CodeIgniter 4 Support (https://forum.codeigniter.com/forum-30.html)
+--- Thread: MySQL Date_Format and Query Builder (/thread-76534.html)



MySQL Date_Format and Query Builder - stlake2011 - 05-21-2020

Good Morning all,

So I tried the query builder now I am running into a classic SQL error. 

This is the piece of code I am having a problem with and cannot get to work properly:

PHP Code:
$this->builder->table('articles');
$this->builder->select('*,DATE_FORMAT(published_on,"%M %d, %Y at %H:%i") as Published');
return 
$result $this->builder->get(); 

It returns the result as expected, BUT and this is a BIG BUT, this is the result that returns:

May `21`, 2020 at 08:31

Running the straight SQL instead of using the Builder it returns a properly formatted result:
May 21, 2020 at 08:31

How do I stop the backticks from surrounding the day part in the Builder version? Or should I return to using SQL where I have more granular control of the queries?

I tried this found on another forum on here:
$this->builder->set(date_format(published_on,"%M %d, %Y at %H:%i") as Published,FALSE);

But this made absolutely no difference to the returned result


RE: MySQL Date_Format and Query Builder - jreklund - 05-21-2020

Odd that it escapes that part, have you tried to disable the escape for that select? I see that you tried it with set, but not select.
PHP Code:
$this->builder->select('*,DATE_FORMAT(published_on,"%M %d, %Y at %H:%i") as Published'false); 



RE: MySQL Date_Format and Query Builder - stlake2011 - 05-21-2020

(05-21-2020, 07:59 AM)jreklund Wrote: Odd that it escapes that part, have you tried to disable the escape for that select? I see that you tried it with set, but not select.
PHP Code:
$this->builder->select('*,DATE_FORMAT(published_on,"%M %d, %Y at %H:%i") as Published'false); 

Thank you, that worked! Not sure how I missed that but thanks. Smile