MySQL-- Ordering results by...

[djr33]

I'm working on a php script that will connect to a database.

In short, without going into too much detail, the database will hold information about different films for my site's theater.

I'm not too concerned about most of the php, but I realized that it would be very complex to write a function to sort the results of the films to view them by date, name, etc.

Is there a way to use mysql to order the results by name, for example?

I'd like to order them by the name, date, and perhaps a few other things.

It would be possible but very annoying to do it with just php, but getting all the results then "stacking" in the right way, but even doing that seems like quite a bit of work to alphabatize.

[codeexploiter]

Syntax:

SELECT [fields][*] FROM [table name] [WHERE] [condition if any] ORDER BY [field name] [ASC][DESC]

Eg:

Assuming that you have a user table and you want to view records sorted ascending based on their first name field. Assume that primary key of the table is not first name.

SELECT * FROM users ORDER BY firstName ASC

In the above statement the ASC is optional by default the sorting is ascending


You want to view records sorted descending based on their first name field.

SELECT * FROM users ORDER BY firstName DESC

Please specify the DESC option if you want to perform a descending sorting


Ex:
SELECT * FROM users ORDER BY firstName, lastName ASC

In this case it will first sort the result based on firstname, if there two similar firstnames then it will sort that records based on their lastName value in the records.

You can retrieve the records from the table in a sorted manner into your PHP script so there is no need to perform a sorting operation from PHP.

[djr33]

Thanks a lot.

That will alphabatize and/or order by numbers?

One of the things I'd like to order by is the "rating" of the film... that's clearly not a default. Could I specify a order.... like "G" then "PG" then "PG-13" then "R", or would it just be simpler to use php for that?
I guess I could just use a WHERE statement for those, but is there a special way to order by a certain example set of order or something?


Anyway, that's great, and it'll be very helpful.

Is "ASC" required if I want ascending, or will it default to that, and I just need to specify DESC if I want descending?

[mwinter]

That will alphabatize and/or order by numbers?

Ordering depends on the character set that applies to character data. The default is decided when MySQL is installed, but it can be overridden at the per-database, -table, and -column level. I suggest that you read the chapter on Character Set Support (Ch. 10 in MySQL 5 documentation) for more information - it's a lot to explain here.

One of the things I'd like to order by is the "rating" of the film... that's clearly not a default. Could I specify a order.... like "G" then "PG" then "PG-13" then "R" ...

That should be the natural order, but no, you can't define your own custom ordering algorithm, only a collation.

Is "ASC" required if I want ascending ...

No, it's the default.

Mike

[Twey]

One of the things I'd like to order by is the "rating" of the film... that's clearly not a default. Could I specify a order.... like "G" then "PG" then "PG-13" then "R", or would it just be simpler to use php for that?

Personally, I'd define some numerical constants here.

[codeexploiter]

posted by djr33
Is "ASC" required if I want ascending, or will it default to that, and I just need to specify DESC if I want descending?

By default whenever you use ORDER BY clause with an SQL statement it will be an ASCENDING order sorting no need to specify ASC in the statement.

But if you want a DESCENDING order sorting then you must specify DESC, if you miss DESC it will give you an ascending order sorting.

[djr33]

Thanks for the info, guys. Very helpful.

Twey, using numerical constants for ratings is a good idea, but adds a step. Might help, though. I'll keep it in mind. The downside I see is if I wanted to add a rating in between two later; I'd have to renumber all of them that were above it. Instead, if I was using WHERE statements (or the nonexistant "key" I was asking about earlier), I could just add it to that. But... yeah, that's an idea.

[Twey]

mysql> select * from nums order by n;
+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    3 |
|    4 |
+------+
5 rows in set (0.00 sec)

mysql> update nums set n = n + 1 where n > 1;
Query OK, 4 rows affected (0.02 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> insert into nums (n) values (2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from nums order by n;
+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    4 |
|    5 |
+------+
6 rows in set (0.00 sec)

mysql>

... and of course you'll be storing the PHP constants in a separate file, making them easy to edit.

[djr33]

Well, that took a bit of time to code/plan, though.

What I meant was changing the relationships... if I wanted to add something to the middle, I'd have the "PG" rating still relating to 2, and so fourth, but I suppose that's fixed.
Anyway, that's an option, and I've certainly got enough to work with now.

And... off to convert all 623 lines of my old page to the new page. Fun.