Welcome Guest, Not a member yet? Register   Sign In
MySQL DATE_FORMAT
#1

[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.
#2

[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");
#3

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

[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




Theme © iAndrew 2016 - Forum software by © MyBB