+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: CodeIgniter 4 (https://forum.codeigniter.com/forumdisplay.php?fid=28)
+--- Forum: CodeIgniter 4 Support (https://forum.codeigniter.com/forumdisplay.php?fid=30)
+--- Thread: MySQL Date_Format and Query Builder (/showthread.php?tid=76534)
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.
