I have a issue with ORDER BY Date. Doesn't return any value

Question

The current issue i am having is with ORDER BY date. For some reason it returns nothing whenever i try to order it by date, my MySQL column is the date datatype. No idea why this is happening. I attempted to try changing the datatype to datetime, but that didn't work. Works fine when I order it by any of my other fields, be it varchar, decimal, or int.

Thanks for common sense for the error handling options. I go this back. SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Release' at line 1

function getAllGames() { //Returns all database values, sorted by name then score.
    try {
        $db = getDBConnection();
        $query = "SELECT * FROM game ORDER BY Release";
        $statement = $db->prepare($query);
        $statement->execute();
        $results = $statement->fetchAll();
        $statement->closeCursor();
        return $results;           // Assoc Array of Rows
    } catch (PDOException $e) {
        $errorMessage = $e->getMessage();
        include '../view/errorPage.php';
        die;
    }       
}

[PDO query fails but I can't see any errors. How to get an error message from PDO?](http://stackoverflow.com/questions/15990857/reference-frequently-asked-questions-about-pdo#15990858) — Your Common Sense, Apr 21 '13 at 21:50
THanks for common sense for the error handling. I go this back. SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Release' at line 1 — user2305203, Apr 21 '13 at 21:57

score 3 · Accepted Answer · answered Apr 21 '13 at 22:02

3

The problem stems from the fact that "release" is a reserved word in MySQL. (See the full list here: https://dev.mysql.com/doc/refman/5.6/en/reserved-words.html)

As per the above link, you can avoid the issue by enclosing the word in backticks as follows:

$query = "SELECT * FROM game ORDER BY `Release`";

Alternatively, if this is liable to cause maintenance issues further down the road, you may wish to simply rename the column. ('release_date' or similar perhaps?)

answered Apr 21 '13 at 22:02

John Parker

54,048
11
129
129

@user2305203 Once you've been bitten by such things once, you'll know what to expect next time. (It's the best way to learn.) :-) – John Parker Apr 21 '13 at 22:05

score 0 · Answer 2 · answered Apr 21 '13 at 22:04

0

Release is a keyword in SQL/MySQL, try to change the column name into something different.

answered Apr 21 '13 at 22:04

auicsc

297
1
3
14

I have a issue with ORDER BY Date. Doesn't return any value

2 Answers2