Search code examples
drupalmysqlidrupal-6

Query is ordered correctly in SQL but not in Drupal

I have a query that takes a string 'noticeDate' and converts it to a date to order it by. The query works perfectly when I run it in phpmyadmin but not in drupal

Here's my query

    <h3 style="text-align: center;">Public Notices</h3>
<?php
$query="SELECT `noticeTitle`, `noticeDate`, `filename`, STR_TO_DATE(`noticeDate`, '%m/%d/%Y') as filedate, `filepath` 
        FROM `public_notice` 
        ORDER BY filedate DESC
        LIMIT 5";
$results = db_query($query);

while ($row = db_fetch_array($results)) {
    echo '<tr>';
    echo '<td><u><a href=http://website.com/'. $row["filepath"] . '>' .$row["noticeDate"].'</u>- '.$row["noticeTitle"]. '</a></td><br />';
    echo '</tr>';
}
?>

In phpmyadmin, it orders 5 dates perfectly- but in drupal it looks like this.

08/04/2009- sometext
09/14/2009- sometext
01/28/2009- sometext
01/23/2009- sometext
02/25/2009- sometext
Solution

  • I think the problem is with the way Drupal substitutes arguments / named parameters. See the second parameter for db_query.

    You should really refactor your code to use a db_select. https://api.drupal.org/api/drupal/includes!database!database.inc/function/db_query/7

    https://api.drupal.org/api/drupal/includes!database!database.inc/function/db_select/7

    To add the STR_TO_DATE functionality, use the AddExpression functionality. https://api.drupal.org/api/drupal/includes!database!select.inc/function/SelectQuery%3A%3AaddExpression/7

    Using these two will definitely solve your problem. Good luck.