By Nikdilis on

Hello drupal experts,

seems that the following database-query results in an error due to the fact that the date's format-%d conflicts with the id's type-%d. (As a result, the date's format-%d has the value of $id)

$result = db_query_range('SELECT DATE_FORMAT(end, "%d.%m.%Y um %H:%i Uhr") as end FROM {tablename} WHERE id = %d AND status = "finished"' ,$id, 0, 10);

Not a big deal, of course, as I could fix it the way shown below (untested!). I just want to know out of curiosity if there is a way to make the query above work. (I know, there are alternatives like timestamp)
Thank you very much,
Dominik

$result = db_query_range('SELECT end FROM {tablename} WHERE id = %d AND status = "finished"' ,$id, 0, 10);

function date_format_change($Date)  //thanks to http://www.webmaster-resource.de/tricks/php/mysql-datum-in-deutsches-format-umwandeln.php
{
    if(strlen($Date) == 10)
    {
        $ChangedDate = substr($Date, 8, 2);
        $ChangedDate .= ".";
        $ChangedDate .= substr($Date, 5, 2);
        $ChangedDate .= ".";
        $ChangedDate .= substr($Date, 0, 4);
        return $ChangedDate;
    }
    elseif(strlen($Date) == 19)
    {
        $ChangedDate = substr($Date, 8, 2);
        $ChangedDate .= ".";
        $ChangedDate .= substr($Date, 5, 2);
        $ChangedDate .= ".";
        $ChangedDate .= substr($Date, 0, 4);
        $ChangedDate .= substr($Date, 10);
        return $ChangedDate;
    }
    else
    {
        return FALSE;
    }
}

while ($row = db_fetch_object($result)) {
date_format_change($row->end);
}
⋅ Categories: Drupal 5.x

Comments

calebtr’s picture

calebtr commented

MySQL's date_format also appears to conflicts with db_query's %b, %f ans %s tokens.

I'm okay with formatting the output using Drupal, but want to use date_format to group nodes by day:

 ... GROUP BY date_format(from_unixtime(created),'%d%m%Y')

Rather than call multiple GROUP BYs to get the same result:

 ... GROUP BY YEAR(FROM_UNIXTIME(created)),DAYOFYEAR(FROM_UNIXTIME(created))

This seems just like a necessary evil - I'd rather use a heavier and secure query than a quick insecure one, but I'd love it if anyone knew a secret that was both quick and secure.

rar’s picture

rar commented

The solution is to use %%d in your sql string. So your query would become 

$result = db_query_range('SELECT DATE_FORMAT(end, "%%d.%m.%Y um %H:%i Uhr") as end FROM {tablename} WHERE id = %d AND status = "finished"' ,$id, 0, 10);