CODEWITHSUNDEEP
phpmysqlpdocase
mowgli
mowgli

Reputation: 2869

Order by CASE ASC

First time using CASE in PDO (mySQL database)

I'm trying to ORDER BY a date, but only if the date is not 0000-00-00 (it can be)

But it seems to ignore the case all together.. What am I doing wrong?

$sql = 'SELECT * FROM mytable WHERE groupid = '.$groupid.' ORDER BY CASE WHEN groupdate != "0000-00-00" THEN groupdate END ASC';

$STH = $conn->query($sql);

if ($STH->rowCount() > 0) {

    while ($row = $STH->fetch()) {

        // output the rows

    }

} else {

    echo '<p>No dates found!</p>';

}

Upvotes: 0

Views: 87

Answers (2)

Naktibalda
Naktibalda

Reputation: 14110

What do you want to do with zero-date rows?
Return them after other rows?
Then use this query

SELECT * 
FROM mytable 
WHERE groupid = $groupid 
ORDER BY groupdate = '0000-00-00', groupdate

groupdate = '0000-00-00' is a boolean expresion and returns 1 for matching rows, 0 for non-matching rows. 0 comes before 1 in ascending order.
Non-zero rows are sorted by groupdate.

Upvotes: 2

Niklas
Niklas

Reputation: 1777

As you can read in the CASE documentation you should have an ELSE statement in the end. Try something like:

$sql = 'SELECT * FROM mytable WHERE groupid = '.$groupid.' ORDER BY (CASE WHEN groupdate != "0000-00-00" THEN groupdate ELSE groupid END)';

Upvotes: 1

Related Questions