Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11
  1. #1
    Regular Coder
    Join Date
    Jan 2006
    Posts
    377
    Thanks
    8
    Thanked 1 Time in 1 Post

    How do you sort your MySQL results according to column name?

    Hi,

    I normally output the results of a mysql query according to ID. I display the results in tables.

    Example:

    PHP Code:
    <?php

    // file.php

    if(isset($GET['op'])) {
        switch(
    $op) {
            case 
    "sort_ID";
            
    $query =  "SELECT ID,name,surname,date FROM table ORDER BY ID";
            break;
            case 
    "sort_name";
            
    $query =  "SELECT ID,name,surname,date FROM table ORDER BY name";
            break;
            case 
    "sort_surname";
            
    $query =  "SELECT ID,name,surname,date FROM table ORDER BY surname";
            break;
            case 
    "sort_date";
            
    $query =  "SELECT ID,name,surname,date FROM table ORDER BY date";
            break;
        }
    } else { 
    // use default sorting
        
    $query "SELECT ID,name,surname,date FROM table";
    }

    include(
    "db.php");
    $result mysql_query($query);

    echo 
    '<table>';
    echo 
    '<tr>';
    echo 
    '<td><a href="file.php?op=sort_ID">ID</a></td>';
    echo 
    '<td><a href="file.php?op=sort_name">Name</a></td>';
    echo 
    '<td><a href="file.php?op=sort_surname">Surname</a></td>';
    echo 
    '<td><a href="file.php?op=sort_date">Date</a></td>';
    echo 
    '</tr>';

    while (
    $row mysql_fetch_array($result)) {
        echo 
    '<tr>';
        echo 
    '<td>' $row['ID'] . '</td>';
        echo 
    '<td>' $row['name'] . '</td>';
        echo 
    '<td>' $row['surname'] . '</td>';
        echo 
    '<td>' $row['date'] . '</td>';
        echo 
    '</tr>';
    }
    echo 
    '</table>';

    ?>
    When this table is displayed, I would like to sort the results according to name, surname or date when clicked on the column name. When clicked again, I want to sort them upside down (DESC).

    What I did may work (except the reversal sorting) but what if I had many columns? Do I need to write so many lines of code?

    Maybe there is a class for this?
    Last edited by guvenck; 01-05-2007 at 02:01 AM.

  • #2
    Senior Coder whizard's Avatar
    Join Date
    Jan 2005
    Location
    Philadelphia, PA, USA
    Posts
    1,662
    Thanks
    14
    Thanked 76 Times in 76 Posts
    How about just doing this:

    PHP Code:
    f(isset($GET['op'])) {
    $op $_GET['op'];
    $query =  "SELECT ID,name,surname,date FROM table ORDER BY ".$op;

    HTH
    Dan
    PHP Tip: If you want to use short tags (<? or <?=$var) then make sure short_open_tag is set to "1". It really helps.

    Don't forget to save everyone time and mark your thread as Resolved :)

    "Also note that it is your responsibility to die() if necessary."

    DON'T USE THE MYSQL_ EXTENSION

  • #3
    Regular Coder
    Join Date
    Jun 2004
    Posts
    565
    Thanks
    0
    Thanked 18 Times in 18 Posts
    Quote Originally Posted by whizard View Post
    How about just doing this:

    PHP Code:
    f(isset($GET['op'])) {
    $op $_GET['op'];
    $query =  "SELECT ID,name,surname,date FROM table ORDER BY ".$op;

    HTH
    Dan
    What if I do a request to "file.php?op=ID;DROP DATABASE"? I can execute any SQL command with your solution.

    This is a more secure version:
    PHP Code:
    <?php
    $sortColumns 
    = array('ID''name''surname''date');
    $sortOrder = array('ASC''DESC');

    if(!isset(
    $_GET['op']) || !isset($sortColumns[$_GET['op']]))
    {
        
    $_GET['op'] = 0;
    }
    if(!isset(
    $_GET['ord']) || !isset($sortOrder[$_GET['ord']]))
    {
        
    $_GET['ord'] = 0;
    }

    $query 'SELECT '.implode(', '$sortColumns).' FROM table ORDER BY '.$sortColumns[$_GET['op']].' '.$sortOrder[$_GET['ord']];
    $fileName getenv('SCRIPT_NAME');
    include 
    'db.php';
    $result mysql_query($query);

    echo 
    '<table>';
    echo 
    '<tr>';
    foreach(
    $sortColumns as $key => $column)
    {
        echo 
    '<td><a href="',$fileName,'?op=',$key,'&ord=',($_GET['op'] == $key && == $_GET['ord']) ? '1' '0','">',$column,'</a></td>';
    }
    echo 
    '</tr>';

    while (
    $row mysql_fetch_array($result))
    {
        echo 
    '<tr>';
        foreach(
    $sortColumns as $column)
        {
            echo 
    '<td>',$row[$column],'</td>';
        }
        echo 
    '</tr>';
    }
    echo 
    '</table>';
    ?>
    dumpfi
    Last edited by dumpfi; 01-05-2007 at 12:53 PM.
    "Failure is not an option. It comes bundled with the software."
    ....../)/)..(\__/).(\(\................../)_/)......
    .....(-.-).(='.'=).(-.-)................(o.O)...../<)
    ....(.).(.)("}_("}(.)(.)...............(.)_(.))Ż/.
    ŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻ
    Little did the bunnies suspect that one of them was a psychotic mass murderer with a 6 ft. axe.

  • #4
    Regular Coder
    Join Date
    Jan 2006
    Posts
    377
    Thanks
    8
    Thanked 1 Time in 1 Post
    dumpfi, that is a great solution, it works... By adding or removing members to the array, you can build a sortable table output for any field...

    I wonder, could this solution be embedded as a class?

  • #5
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Quote Originally Posted by dumpfi View Post
    What if I do a request to "file.php?op=ID;DROP DATABASE"? I can execute any SQL command with your solution.
    then you would just wrap the GET in mysql_real_escape_string to prevent that wouldn't you?

    And more to the point you would not have such permissions for a user over the web, you would create a secure user with only select, insert and update privileges and none other.

  • #6
    Regular Coder
    Join Date
    Feb 2005
    Posts
    663
    Thanks
    5
    Thanked 14 Times in 14 Posts
    Searching is your friend

    I'm looking to possibly do the same as above, and like the code posted by dumpfi to create the sortable table.

    But can someone maybe help explain/show how you can possibly expand the sortable columns to include additional columns that do not officially exist in your database. I'd like to include columns/fields that are being created during the actual sql query such as:

    PHP Code:
    $sql =  "SELECT *, ( wins / (win + losses ) ) AS `Rating`, "

  • #7
    Banned
    Join Date
    Feb 2008
    Location
    Winnipeg, Canada
    Posts
    396
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Firstly, this snippet:

    PHP Code:
    if(isset($GET['op'])) {
        switch(
    $op) {
            case 
    "sort_ID";
            
    $query =  "SELECT ID,name,surname,date FROM table ORDER BY ID";
            break;
            case 
    "sort_name";
            
    $query =  "SELECT ID,name,surname,date FROM table ORDER BY name";
            break;
            case 
    "sort_surname";
            
    $query =  "SELECT ID,name,surname,date FROM table ORDER BY surname";
            break;
            case 
    "sort_date";
            
    $query =  "SELECT ID,name,surname,date FROM table ORDER BY date";
            break;
        }
    } else { 
    // use default sorting
        
    $query "SELECT ID,name,surname,date FROM table";

    could be be written as: (just change the $_GET["op"] values from sort_ID,sort_name,etc to ID,name,etc)

    PHP Code:
    $query "SELECT ID,name,surname,date FROM table";

    if (isset(
    $_GET["op"]))
       
    $query .= " ORDER BY " $_GET["op"]; 
    Secondly, to make them order in the opposite direction add " DESC" to the end of your query, so "SELECT * FROM table ORDER BY col" becomes "SELECT * FROM table ORDER BY col DESC"

    Thirdly, if you'd like to sort by multiple rows just add a comma. For example "SELECT * FROM tables ORDER BY col1,col2 DESC,col3" would order first by col1 then by col2 (descending) then by col3

    Hopefully all that can help you get your code working more efficiently.

  • #8
    Senior Coder
    Join Date
    Jan 2007
    Posts
    1,648
    Thanks
    1
    Thanked 58 Times in 54 Posts
    Did you read the topic? You just repeated everything that was posted here.

  • #9
    Banned
    Join Date
    Feb 2008
    Location
    Winnipeg, Canada
    Posts
    396
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Quote Originally Posted by aedrin View Post
    Did you read the topic? You just repeated everything that was posted here.
    I did not.

  • #10
    Senior Coder
    Join Date
    Jan 2007
    Posts
    1,648
    Thanks
    1
    Thanked 58 Times in 54 Posts
    Quote Originally Posted by Andrew Johnson View Post
    I did not.
    You probably should, so you can add something useful to the topic, rather than confuse the user by making them think that this is something new. It also saves you time.

  • #11
    Banned
    Join Date
    Feb 2008
    Location
    Winnipeg, Canada
    Posts
    396
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Quote Originally Posted by aedrin View Post
    You probably should, so you can add something useful to the topic, rather than confuse the user by making them think that this is something new. It also saves you time.
    Thanks for the tidbit of knowledge.


  •  

    Posting Permissions

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