Welcome Guest, Not a member yet? Register   Sign In
select on active record fails when add some mysql function
#1

[eluser]!Z[/eluser]
This query on MySQL works fine but when I create this one on CI with Active Record Class
throw an exception on the query.

MySQL Query
Code:
select users.ID, users.FirstName, users.LastName, users.EMail, users.Username, users.Pwd,
    users.City, users.ZipCode,DATE_FORMAT(users.FecNac,'%d/%m/%y') as 'FecNac', users.Tall, users.Weight, users.Gender,
    usersprofile.IDTypeContext, usersprofile.IDTypeAC,usuariosperfiles.NewsLetter,
    usersprofile.Offers, usersprofile.BodyOffers, usersprofile.YearsOverWeight, usersprofile.CantSons
from     users
inner join usersprofile on users.ID = usersprofile.IDUser

Active Record Class
Code:
$this->db->select("select users.ID, users.FirstName, users.LastName, users.EMail, users.Username, users.Pwd,
    users.City, users.ZipCode,DATE_FORMAT(users.FecNac,'%d/%m/%y') as 'FecNac', users.Tall, users.Weight, users.Gender,
    usersprofile.IDTypeContext, usersprofile.IDTypeAC,usuariosperfiles.NewsLetter,
    usersprofile.Offers, usersprofile.BodyOffers, usersprofile.YearsOverWeight, usersprofile.CantSons");
$this->db->join("usersprofile","users.ID = usersprofile.IDUsers");                                                                
$this->db->where("users.ID",1);
return $this->db->get("users")->row();

I think this is because in the DB_active_rec.php file in the line 83 has this sentence
Code:
$select = explode(',', $select);
I think the CI guys could improve it to get a fully compatibility with MySQL and his functions.

I know that I can format the date from the php code,but I want to do this from mysql.

Anyone knows if there is some solution?

Thanks ;-)
#2

[eluser]xwero[/eluser]
you can set the second parameter of the select method to false to prevent the string from being processed.

The error is not in the line you mentioned, i think the error is because of the single quotes around the alias FecNac. i think this will through off the select string processing method _protect_identifiers.

You can add $this->db->_compile_select() to see if you sql statment is as you want it to be.
#3

[eluser]TheFuzzy0ne[/eluser]
Pass FALSE as a second parameter to $this->db->select(). AR is escaping your select statement, which is breaking your call to DATE_FORMAT().
#4

[eluser]!Z[/eluser]
Thanks this works perfectly !!!
#5

[eluser]kgill[/eluser]
Why bother with AR at all? Unless you're using the pass in an array and have it generate statements for you aspect, you're gaining no real benefit from AR at this point.




Theme © iAndrew 2016 - Forum software by © MyBB