CODEWITHSUNDEEP
phpmysql
Pacijent
Pacijent

Reputation: 149

MySQL order by date doesn't work properly

I am having php code like this:

<?php
    session_start();
    include("../Php/ConfigBaza.php");

    $kid = $_SESSION['KORISNIKID'];

    $sql = "SELECT REZERVACIJAID, DATE_FORMAT(DATUM, '%d.%m.%y') AS DATUM, 
                    STATUS, VREDNOST 
            FROM REZERVACIJE 
            WHERE KORISNIKID = '$kid' 
            ORDER BY DATUM DESC";
    $result = $mysqli->query($sql);

    if($result->num_rows < 1) {
        echo("Nemate ni jednu rezervaciju!");
        die();
    } else {
        echo("<style>");
        include('../Styles/_Rezervacije.css');
        echo("</style>");
        while($r = mysqli_fetch_assoc($result)) {
            $realizovana = "";
            $rid = $r['REZERVACIJAID'];
            $h_rid = hash('fnv1a32', $rid);
            $datum = $r['DATUM'];
            $status = $r['STATUS'];
            $v = $r['VREDNOST'];

            $v_txt = number_format($v, 2);

            if($status == 2) {
                $realizovana = "realizovana";
            } else if($status == 0) {
                $realizovana = "neaktivna";
            }
            echo("
                <div class='rezervacija $realizovana' id='$rid' onclick='IdiNaRezervaciju(this.id)'>
                    <p style='font-weight: bolder; font-size: large'>$h_rid</p>
                    <br>
                    <p style='font-size: large'>$v_txt</p>
                    <p style='font-weight: bolder'>$datum</p>
                </div>
            ");
        }
    }
?>

as you can see in query i am ordering by date but for some reason it does normaly for days but not for months.

Result i am getting is:

enter image description here

In mysql database my date column is type of date

enter image description here

Upvotes: 1

Views: 298

Answers (1)

M. Eriksson
M. Eriksson

Reputation: 13645

Just as @RiggsFolly brilliantly mentioned in his comment, the problem is that you're reformatting the date column you're using to sort the records.

When you reformat the date column, MySQL won't read it as a date field anymore but rather as text, and as text, 04.08.18 is lower than 31.08.18 which is why you're getting the order you're currently getting.

The solution is simply to reformat the date column as another name:

DATE_FORMAT(DATUM, '%d.%m.%y') AS SOME_DATE,

You can of course change SOME_DATE to what ever you want. Then in your code, you fetch it with $r['SOME_DATE']; instead.

Here's a fiddle demonstrating the difference: http://sqlfiddle.com/#!9/0d2e5f/1

Upvotes: 2

Related Questions