Results 1 to 9 of 9

Thread: MySQL-- Ordering results by...

  1. #1
    Join Date
    Mar 2006
    Location
    Illinois, USA
    Posts
    12,164
    Thanks
    265
    Thanked 690 Times in 678 Posts

    Default MySQL-- Ordering results by...

    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.
    Daniel - Freelance Web Design | <?php?> | <html>| espa˝ol | Deutsch | italiano | portuguŕs | catalÓ | un peu de franšais | some knowledge of several other languages: I can sometimes help translate here on DD | Linguistics Forum

  2. #2
    Join Date
    Sep 2005
    Location
    India
    Posts
    1,627
    Thanks
    6
    Thanked 107 Times in 107 Posts

    Thumbs up

    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.

  3. #3
    Join Date
    Mar 2006
    Location
    Illinois, USA
    Posts
    12,164
    Thanks
    265
    Thanked 690 Times in 678 Posts

    Default

    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?
    Daniel - Freelance Web Design | <?php?> | <html>| espa˝ol | Deutsch | italiano | portuguŕs | catalÓ | un peu de franšais | some knowledge of several other languages: I can sometimes help translate here on DD | Linguistics Forum

  4. #4
    Join Date
    Dec 2004
    Location
    UK
    Posts
    2,358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    Quote Originally Posted by djr33
    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

  5. #5
    Join Date
    Jun 2005
    Location
    英国
    Posts
    11,876
    Thanks
    1
    Thanked 180 Times in 172 Posts
    Blog Entries
    2

    Default

    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.
    Twey | I understand English | 日本語が分かります | mi jimpe fi le jbobau | mi esperanton komprenas | je comprends franšais | entiendo espa˝ol | t˘i Ýt hiểu tiếng Việt | ich verstehe ein bisschen Deutsch | beware XHTML | common coding mistakes | tutorials | various stuff | argh PHP!

  6. #6
    Join Date
    Sep 2005
    Location
    India
    Posts
    1,627
    Thanks
    6
    Thanked 107 Times in 107 Posts

    Thumbs up

    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.

  7. #7
    Join Date
    Mar 2006
    Location
    Illinois, USA
    Posts
    12,164
    Thanks
    265
    Thanked 690 Times in 678 Posts

    Default

    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.
    Daniel - Freelance Web Design | <?php?> | <html>| espa˝ol | Deutsch | italiano | portuguŕs | catalÓ | un peu de franšais | some knowledge of several other languages: I can sometimes help translate here on DD | Linguistics Forum

  8. #8
    Join Date
    Jun 2005
    Location
    英国
    Posts
    11,876
    Thanks
    1
    Thanked 180 Times in 172 Posts
    Blog Entries
    2

    Default

    Code:
    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.
    Twey | I understand English | 日本語が分かります | mi jimpe fi le jbobau | mi esperanton komprenas | je comprends franšais | entiendo espa˝ol | t˘i Ýt hiểu tiếng Việt | ich verstehe ein bisschen Deutsch | beware XHTML | common coding mistakes | tutorials | various stuff | argh PHP!

  9. #9
    Join Date
    Mar 2006
    Location
    Illinois, USA
    Posts
    12,164
    Thanks
    265
    Thanked 690 Times in 678 Posts

    Default

    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.
    Daniel - Freelance Web Design | <?php?> | <html>| espa˝ol | Deutsch | italiano | portuguŕs | catalÓ | un peu de franšais | some knowledge of several other languages: I can sometimes help translate here on DD | Linguistics Forum

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •