MySQL DATE_FORMAT - Printable Version

MySQL DATE_FORMAT - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: MySQL DATE_FORMAT (/showthread.php?tid=53070)

MySQL DATE_FORMAT - El Forum - 07-10-2012

[eluser]Constantin Iliescu[/eluser]
Hello,

When I try to select formatted date using active record

Code:
$this->db->select( "date_format(created, '%d.%m.%Y') as created_date", FALSE );

$res = $this->db->get( 'ticket' );

the month gets prefixed with the table prefix I set in database configuration file (sl), like so:

Code:
[created_date] => 05.sl_07.2012

It works if the format is something like this:

Code:
'%d-%m-%Y'

Is this a bug?
Thanks in advance.

MySQL DATE_FORMAT - El Forum - 07-10-2012

[eluser]borgir[/eluser]
This happens when the DB_active_rec.php tries to compile your SELECTed fields (_compile_select method).
It runs the _protect_identifiers method foreach of the selected columns.
However, your select statement is a function, not column(s) so the statement is exploded by commas.

Code:
[0] => date_format(checkout_date

[1] => '%d.%m.%Y') as created_date

and the _protect_identifiers method, expecting something like this:

Code:
SELECT * FROM hostname.database.table.column AS c FROM hostname.database.table

Or a query with aliasing:

SELECT m.member_id, m.member_name FROM members AS m

adds your table prefix thinking that is "compiling" something like this m.member_id here (DB_driver.php file):

Code:
// We only add the table prefix if it does not already exist

if (substr($parts[$i], 0, strlen($this->dbprefix)) != $this->dbprefix)

{

$parts[$i] = $this->dbprefix.$parts[$i];

}

For now I suggest that you use this syntax:

Code:
$res = $this->db->query( "SELECT date_format(created, '%d.%m.%Y') as created_date FROM slticket");

MySQL DATE_FORMAT - El Forum - 07-11-2012

[eluser]Constantin Iliescu[/eluser]
Thank you, borgir.
Works for now, as you said Smile

MySQL DATE_FORMAT - El Forum - 08-19-2012

[eluser]Hammoc Matthew[/eluser]
I write code the same above but it error. Can you help me?

Error Number: 1064

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 'FROM (`tb_news`) ORDER BY `new_created` DESC LIMIT 8' at line 2

SELECT *, date_format(new_created, `"%d/%m")` as day FROM (`tb_news`) ORDER BY `new_created` DESC LIMIT 8

Filename: E:\WWW\gamemobile24h\system\database\DB_driver.php

Line Number: 330