CodeIgniter Forums
Problem with MySql functions on Active Record - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived General Discussion (https://forum.codeigniter.com/forumdisplay.php?fid=21)
+--- Thread: Problem with MySql functions on Active Record (/showthread.php?tid=11976)

Problem with MySql functions on Active Record - El Forum - 09-30-2008

[eluser]kmil0[/eluser]
Hi guys.

I have a strange problem when i use $this->db->select()
I have something like this

Code:
$query = $this->db->select(
                           array(
                              "u.name_u  as name",
                              "DATE_FORMAT(u.date_field, '%Y-%d-%m %H:%i%s') as date_field"
                           )
                          );
                            
$query = $this->db->join("rol r", "r.code_r = u.cod_rol");
$query = $this->db->get("users u", $offset, $lenght);

but, i got a "Database error number 1064"
and this is the final query:
Code:
SELECT u.name_u AS name, creditex_DATE_FORMAT(u.date_field, '%Y-%d-%m %H:%i%s'\) as date_field FROM (`creditex_users` u) JOIN `creditex_rol` r ON r.codigo_r = u.cod_rol LIMIT 0, 5

look that SELECT add the the dbprefix to MySql DATE_FORMAT function, and is the same with STR_TO_DATE
What could be happening?

thank's guys Wink
excuse my bad english Big Grin

Problem with MySql functions on Active Record - El Forum - 09-30-2008

[eluser]ray73864[/eluser]
try removing the double quotes from around the DATE_FORMAT function, i'm not sure but it looks like it might be attempting to find a field name called 'DATE_FORMAT' at present.

Problem with MySql functions on Active Record - El Forum - 09-30-2008

[eluser]kmil0[/eluser]
No men, I try with this:

Code:
$query = $this->db->select(
                           array(
                              'u.name_u  as name',
                              'DATE_FORMAT(u.date_field, "%Y%m%d %H:%i%s") as date_field'
                           )
                          );
                            
$query = $this->db->join("rol r", "r.code_r = u.cod_rol");
$query = $this->db->get("users u", $offset, $lenght);
but...
the problem persist, but thanks for the fast answe Wink

Problem with MySql functions on Active Record - El Forum - 09-30-2008

[eluser]Colin Williams[/eluser]
Definitely a bug you should report. For the time being, I would write the select statement out "by hand" unless you want to open up the DB drivers and fix yourself

Problem with MySql functions on Active Record - El Forum - 10-01-2008

[eluser]Phil Sturgeon[/eluser]
Have you tried adding FALSE to $this->db->select() as this turns off other assumptions by AR. It may well just turn off the adding of backticks, but it could help with this too.

Problem with MySql functions on Active Record - El Forum - 10-01-2008

[eluser]kmil0[/eluser]
Thanks phyromaniac Big Grin
the final query is:

Code:
$query = $this->db->select(
                           array(
                              'u.name_u  as name',
                              'DATE_FORMAT(u.date_field, "%Y%m%d %H:%i%s") as date_field'
                           ), FALSE
                          );
                            
$query = $this->db->join("rol r", "r.code_r = u.cod_rol");
$query = $this->db->get("users u", $offset, $lenght);

Now it works! Big Grin
but... where can I see the arguments for the db functions?
I don't see it in the user guide, however Wink

Thanks to all again Wink