Help building this query using AR

[eluser]ReyPM[/eluser]
Hi, coders. I'm running a little problem here and can't find the solution. I'm building a query using Active Record from CI. This is the code for the query:

Code:

$this->db->select("u.id AS user_id, u.email, p.display_name, p.first_name, p.last_name, s.status_id, s.message, DATE_FORMAT(s.created_at, %d %d %Y %h %i %p) AS created_at");
$this->db->join($this->_table_users . ' u', 'u.id = s.user_id');
$this->db->join($this->_table_profiles . ' p', 'p.user_id = u.id');
        
$this->db->where_in('user_id', $str);
$this->db->where('deleted', 0);

$this->db->from($this->_table . ' s');
$this->db->order_by('s.created_at', 'desc');

But I'm getting this error:

Unknown column '%d' in 'field list'

SELECT `u`.`id` AS user_id, `u`.`email`, `p`.`display_name`, `p`.`first_name`, `p`.`last_name`, `s`.`status_id`, `s`.`message`, DATE_FORMAT(s.created_at, `%d` %d %Y %h %i %p) AS created_at FROM (`default_status` s, `default_status`) JOIN `default_users` u ON `u`.`id` = `default_s`.`user_id` JOIN `default_profiles` p ON `p`.`user_id` = `u`.`id` WHERE `user_id` IN ('5726, 2, 10293') AND `deleted` = 0 ORDER BY `s`.`created_at` desc LIMIT 2

Does any know where I can use DATE_FORMAT within this query?
Cheers and thanks in advance

[eluser]Massaki[/eluser]
DATE_FORMAT(s.created_at, '%d %m %Y %h %i %p') <= Put simple quotes before %d and after %p

[eluser]michalsn[/eluser]

Code:

$this->db->select("u.id AS user_id, u.email, p.display_name, p.first_name, p.last_name, s.status_id, s.message, DATE_FORMAT(s.created_at, '%d %d %Y %h %i %p') AS created_at", FALSE);

[eluser]ReyPM[/eluser]
Ok, thanks both of yours, I've change a bit the code and now the query looks like:

Code:

$this->db->select("u.id AS user_id, u.email, p.display_name, p.first_name, p.last_name, s.status_id, s.message");
$this->db->select("DATE_FORMAT(s.created_at, `Publicado el %d/%m/%Y a las %h:%i %p`) AS created_at", FALSE);

$this->db->join($this->_table_users . ' u', 'u.id = s.user_id');
$this->db->join($this->_table_profiles . ' p', 'p.user_id = u.id');

$this->db->where_in('user_id', $str);
$this->db->where('deleted', 0);

$this->db->from($this->_table . ' s');
$this->db->order_by('s.created_at', 'desc');

But now get this error:

Error Number: 1054

Unknown column 'Publicado el %d/%m/%Y a las %h:%i %p' in 'field list'

SELECT `u`.`id` AS user_id, `u`.`email`, `p`.`display_name`, `p`.`first_name`, `p`.`last_name`, `s`.`status_id`, `s`.`message`, DATE_FORMAT(s.created_at, `Publicado el %d/%m/%Y a las %h:%i %p`) AS created_at FROM (`default_status` s, `default_status`) JOIN `default_users` u ON `u`.`id` = `default_s`.`user_id` JOIN `default_profiles` p ON `p`.`user_id` = `u`.`id` WHERE `user_id` IN ('5726, 2, 10293') AND `deleted` = 0 ORDER BY `s`.`created_at` desc LIMIT 2

The funny is the same query write as pure SQL works Cheers and thanks in advance, so what's wrong here?

[eluser]Massaki[/eluser]
Maybe it's because you have used a crasis, instead simple quote
` <- this is a crasis
' <- and this is a simple quote

[eluser]ReyPM[/eluser]
Didn't work either:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%d/%m/%Y a las %h:%i %p’) AS created_at FROM (`default_status` s, `default_sta' at line 1

SELECT `u`.`id` AS user_id, `u`.`email`, `p`.`display_name`, `p`.`first_name`, `p`.`last_name`, `s`.`status_id`, `s`.`message`, DATE_FORMAT(s.created_at, ’Publicado el %d/%m/%Y a las %h:%i %p’) AS created_at FROM (`default_status` s, `default_status`) JOIN `default_users` u ON `u`.`id` = `default_s`.`user_id` JOIN `default_profiles` p ON `p`.`user_id` = `u`.`id` WHERE `user_id` IN ('14974') AND `deleted` = 0 ORDER BY `s`.`created_at` desc LIMIT 2