mysql query results sorting

Question

I am calling the results form a query to a list on my site based on if the item is "downtown_hosted". This works fine but I would now like to sort that list DESC, but can't seem to get the syntax correct.

Below is what I have:

$result_events = mysql_query("SELECT * FROM events 
                                WHERE downtown='downtown_hosted'
                                ORDER BY date DESC
                                LIMIT 5 ");

The issue is that events don't display when I have the "ORDER BY date DESC LIMIT 5" in select from. If I remove this call the items display but out of the desired DESC order. — pixelJockey
– pixelJockey, Commented Jun 17, 2010 at 21:13

Matt · Accepted Answer · 2010-06-16 15:56:47Z

6

You need to escape the word "date" with backticks.

E.g.:

$result_events = mysql_query("
    SELECT * FROM events
    WHERE downtown='downtown_hosted'
    ORDER BY `date` DESC
    LIMIT 5
");

In practice it's not a bad habit to get into always enclosing your columns with backticks, so you will not have to worry about conflicting with language keywords.

answered Jun 16, 2010 at 15:56

Matt

44.1k6 gold badges105 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Matt Over a year ago

@Salil - backticks are optional, but not when your column shares its name with a language keyword.

Wrikken Over a year ago

In practice it's not a bad habit to try to use ANSI SQL as much as possible and to avoid keywords as column names :)

pixelJockey Over a year ago

If I remove the "ORDER BY date DESC LIMIT 5" the items will display in the list but they are out of order. So the issue maybe with my list. The code I have for my list is: <? $i = 0; while($row = mysql_fetch_assoc($result_events)) { if(strtotime($row['end_date']) > date('U') && $i < 6) { $i++; $title = $row['title']; $id = $row['id']; ?> <table> <tr> <td align="center" <? if($i % 2){ ?> bgcolor="#DAE8F7"<? } ?>><span class=" newsLINK"><a href="events.php#<?=$id?>"><?=$title?></a></span></td> </tr> <? } } ?> </table>

Hammerite · Accepted Answer · 2010-06-16 15:57:16Z

6

date is an SQL keyword. You can have a column called date, but every time you refer to it you will have to use identifier quotes. In MySQL this is accomplished using backticks: `date`

answered Jun 16, 2010 at 15:57

Hammerite

22.5k6 gold badges76 silver badges103 bronze badges

Collectives™ on Stack Overflow

mysql query results sorting

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related