How Do I Sort A MySQL Database

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

How Do I Sort A MySQL Database

Post by icesolid »

I want to be able to sort my MySQL database by a NON primary key.

Example:

Code: Select all

<?php
mysql_query("SELECT * FROM table ORDER BY order ASC");
?>
In this case the field order is an alpha field (a, b, c, d, e, f, etc.).
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

have you tried it?

and please start posting in the correct boards....
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

I have

Post by icesolid »

I have tried. I get an error, basically telling me that the order field cannot be used to sort.

So I currently use my id primary key field to sort, but I want to give my users the able to set the sort order.
User avatar
Jean-Yves
Forum Contributor
Posts: 148
Joined: Wed Jul 02, 2003 2:13 pm
Location: West Country, UK

Post by Jean-Yves »

"ORDER" is a reserved word in SQL. Try renaming your field to "SortOrder" or something similar.
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Post by Bill H »

Or put the field name in single quotes in the query.

Code: Select all

<?php
mysql_query("SELECT * FROM table ORDER BY 'order' ASC");
?>
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

ahem, backticks... not quotes.
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Post by Bill H »

oops. I know that backticks are the usual specifier for bracketing field and table names. But single quotes work perfectly well for this application, actually. After reading your post I had to go to phpmyadmin to check my memory, and try it in a line of code, since I've been avoiding the use of reserved words. (Which is actually the better practice anyway.)

Code: Select all

$Result = mysql_query("SELECT Pass FROM Admin WHERE id=1 ORDER BY 'order'", $Link);
That line of code executed fine with single quotes. MySQL is very forgiving (up to a point).
User avatar
Jean-Yves
Forum Contributor
Posts: 148
Joined: Wed Jul 02, 2003 2:13 pm
Location: West Country, UK

Post by Jean-Yves »

Personally, I'd advise against starting off by adopting bad habits such as using reserved words for field names. You're just storing up problems for later.
Post Reply