CODEWITHSUNDEEP
phpmysqlsql-order-bystr-to-date
odd_duck
odd_duck

Reputation: 4111

MySQL - ORDER BY STR_TO_DATE not working

I am trying to run a PDO query with PHP and MySql and then sort it by date.

My query is below:

    $query_params = array( 
        ':website' => $site
    );

    $query = " 
        SELECT 
            DATE_FORMAT(date, '%d/%m/%Y') AS date,
            id
        FROM
            items as bi
        INNER JOIN 
            basket as bb ON bi.item_number=bb.basket_item
        INNER JOIN
            orders as bo ON bb.basket_order=bo.order_number 
        WHERE
            bi.website = :website
        ORDER BY 
            STR_TO_DATE(date,'%d/%m/%Y') DESC
    "; 

    try { 
        $stmt = DB::get()->prepare($query); 
        $stmt->execute($query_params); 
        $rows = $stmt->fetchAll();
    } 
    catch(PDOException $ex) {} 

    foreach($rows as $row):
        $output .= "".$row["date"].",";
        $output .= "".$row["id"].",";
        $output .= "\r\n <br />";
    endforeach;

Where my output should be:

13/06/2014, 8676,
12/06/2014, 5765,
12/04/2014, 7683, 
08/12/2013, 1098, 
06/12/2013, 2003, 
06/12/2013, 6755,

It doesn't seem to be sorting by anything:

12/06/2014, 5765,
12/04/2014, 7683, 
13/06/2014, 8676, 
06/12/2013, 2003, 
06/12/2013, 6755, 
08/12/2013, 1098,

Should STR_TO_DATE(date,'%d/%m/%Y') DESCnot be sorting as intended?

Upvotes: 1

Views: 3800

Answers (2)

Fluffeh
Fluffeh

Reputation: 33542

You already have a lovely date column in your table - why on earth try to sort by some formatted string based on that?

    SELECT 
        DATE_FORMAT(date, '%d/%m/%Y') AS date,
        id
    FROM
        items as bi
    INNER JOIN 
        basket as bb ON bi.item_number=bb.basket_item
    INNER JOIN
        orders as bo ON bb.basket_order=bo.order_number 
    WHERE
        bi.website = :website
    ORDER BY 
        date DESC

Sure, format the date output to the user however you like - but you are not only making the DB do a lot more by formatting each row of data then sorting by something that could be done natively by the database the way it was meant to be.

Edit: Interesting. I wonder if the fact that date is a semi-reserved word is causing your sort not to happen as expected?

Maybe try this:

    ORDER BY 
        bo.date DESC

Upvotes: 2

Gabber
Gabber

Reputation: 7249

try this

$query = " 
    SELECT 
        DATE_FORMAT(date, '%d/%m/%Y') AS date,
        id
    FROM
        items as bi
    INNER JOIN 
        basket as bb ON bi.item_number=bb.basket_item
    INNER JOIN
        orders as bo ON bb.basket_order=bo.order_number 
    WHERE
        bi.website = :website
    ORDER BY 
        date DESC
";

Upvotes: 1

Related Questions