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 15 of 15
  1. #1
    New Coder
    Join Date
    Jan 2005
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question Sort by hyperlinks ?

    Is there some way that a query can be made so it will sort by hyperlinks in a table header ?

    Ive noticed this a lot on other sites like this but have no idea where to start.

    pagename.php?&sort=name&order=desc

  • #2
    Super Moderator
    Join Date
    May 2002
    Location
    Perth Australia
    Posts
    4,040
    Thanks
    10
    Thanked 92 Times in 90 Posts
    alter your SQL based on the url data..

    $sql = "
    SELECT blah
    FROM table
    ORDER BY '{$_GET['sort']}'
    ".strtoupper($_GET['order']);

    ok DONT do exactly that, you have to clean $_GET['sort'] etc to make sure noone has put any dodgy code in there , but thems the basics.
    resistance is...

    MVC is the current buzz in web application architectures. It comes from event-driven desktop application design and doesn't fit into web application design very well. But luckily nobody really knows what MVC means, so we can call our presentation layer separation mechanism MVC and move on. (Rasmus Lerdorf)

  • #3
    New Coder
    Join Date
    Jan 2005
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by firepages
    alter your SQL based on the url data..

    $sql = "
    SELECT blah
    FROM table
    ORDER BY '{$_GET['sort']}'
    ".strtoupper($_GET['order']);

    ok DONT do exactly that, you have to clean $_GET['sort'] etc to make sure noone has put any dodgy code in there , but thems the basics.

    Ok thanks I'll give it a try although I dont understand this part ".strtoupper"

  • #4
    Senior Coder
    Join Date
    Apr 2005
    Location
    Colorado, United States
    Posts
    1,208
    Thanks
    0
    Thanked 0 Times in 0 Posts
    It's more for semantics. It'll change asc or deSc into ASC and DESC respectively so that the query looks correct. It'd work either way, but its always good form to create queries that are properly formatted. Same goes for code.
    "$question = ( to() ) ? be() : ~be();"

  • #5
    New Coder
    Join Date
    Jan 2005
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts
    when I use the full piece of code like you suggested.

    ORDER BY '{$_GET['sort']}'
    ".strtoupper($_GET['order']);

    I get a parse error ?

    but if I use just this part on the end of the query
    ORDER BY '{$_GET['sort']}'

    and use a link like

    page.php?sort=fieldname

    this then works but only in desc order ?

  • #6
    New Coder
    Join Date
    Jan 2005
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts
    thanks Velox could you give me an example without using the strtoupper function ?

    As long as there not a Psycho Semantic it's ok with me

  • #7
    Senior Coder
    Join Date
    Apr 2005
    Location
    Colorado, United States
    Posts
    1,208
    Thanks
    0
    Thanked 0 Times in 0 Posts
    It isn't his query, I just ran it through PHP and it didn't throw any errors. Here's the query formatted in my style...try it.

    PHP Code:
    $sql 'SELECT blah FROM table ORDER BY "' $_GET['sort'] . '" ' strtoupper($_GET['order']); 
    "$question = ( to() ) ? be() : ~be();"

  • #8
    New Coder
    Join Date
    Jan 2005
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts
    No its still giving me parse errors and it must be me causing them

    here is the line with the query im trying to use it on.

    PHP Code:
    $result mysql_query("SELECT oamembers.surname, oamembers.firstname, oamembers.nickname, oamembers.email, DATE_FORMAT(oamembers.signup, '%d %b %Y') AS signup, oamembers.mobile, oamembers.bay, oamembers.aisle, oamembers.seat, oamembers.oa_id, oamembers.club_member, oamembers.seasontix, oamembers.anchor FROM oamembers ORDER BY oamembers.surname, oamembers.firstname"); 

  • #9
    Senior Coder
    Join Date
    Apr 2005
    Location
    Colorado, United States
    Posts
    1,208
    Thanks
    0
    Thanked 0 Times in 0 Posts
    PHP Code:
    $result mysql_query('SELECT oamembers.surname, oamembers.firstname, oamembers.nickname, oamembers.email, DATE_FORMAT(oamembers.signup, "%d %b %Y") AS signup, oamembers.mobile, oamembers.bay, oamembers.aisle, oamembers.seat, oamembers.oa_id, oamembers.club_member, oamembers.seasontix, oamembers.anchor FROM oamembers ORDER BY "' $_GET['sort'] . '" ' strtoupper($_GET['order'])); 
    "$question = ( to() ) ? be() : ~be();"

  • #10
    New Coder
    Join Date
    Jan 2005
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Velox your a champ

    Just a final question though it is possible to sort by to fields using this method?.

    etc
    page.php?sort=fieldname1,fieldname2&order=desc

    is this correct ?

  • #11
    Senior Coder
    Join Date
    Apr 2005
    Location
    Colorado, United States
    Posts
    1,208
    Thanks
    0
    Thanked 0 Times in 0 Posts
    All but the last field would be sorted as ascending, but it'd work. If you wanted pairs, you could explode the variables by , and use them in a foreach to generate the ORDER BY.
    "$question = ( to() ) ? be() : ~be();"

  • #12
    New Coder
    Join Date
    Jan 2005
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Velox

    I also tried using that end piece of code on other pages to do the same thing and are getting errors ? all I tried to do was paste this piece of code on the end of the query.

    PHP Code:
    ORDER BY "' . $_GET['sort'] . '" ' . strtoupper($_GET['order])); 
    and then replaced the " at the start of the line with a ' like you did with the copy you edited for me so I must be missing something there or was that for use with only that query.

  • #13
    Regular Coder Element's Avatar
    Join Date
    Jul 2004
    Location
    Lynnwood, Washington, US
    Posts
    855
    Thanks
    2
    Thanked 2 Times in 2 Posts
    there can't already be those operators in the query. the query must be formated like:

    SELECT * FROM table WHERE whatever=whatever ORDER BY whatever ASC

    See how that is formed? Check that against your query and make sure the operators are in that order. Operators should be capital.

  • #14
    Super Moderator
    Join Date
    May 2002
    Location
    Perth Australia
    Posts
    4,040
    Thanks
    10
    Thanked 92 Times in 90 Posts
    Velox is right , the uppercasing of DESC etc is just semantics and not required in MySQL and I think my original snippet was missing a space

    just posting to note that if you are only selecting from 1 table then there is no reason to prefix the fieldnames (even in a join you don't always need them)
    PHP Code:
    <?php
    $sort 
    mysql_real_escape_string($_GET['sort']);
    $order='';
    if(
    strtolower($_GET['order'])=='desc'){
        
    $order='DESC';
    }
    $result mysql_query("
        SELECT surname, firstname, nickname, email, 
        DATE_FORMAT(signup, '%d %b %Y') AS signup, 
        mobile, bay, aisle, seat, oa_id, club_member, seasontix, anchor 
        FROM oamembers 
        ORDER BY $sort 
        $order
    "
    );
    ?>
    resistance is...

    MVC is the current buzz in web application architectures. It comes from event-driven desktop application design and doesn't fit into web application design very well. But luckily nobody really knows what MVC means, so we can call our presentation layer separation mechanism MVC and move on. (Rasmus Lerdorf)

  • #15
    New Coder
    Join Date
    Jan 2005
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by firepages
    Velox is right , the uppercasing of DESC etc is just semantics and not required in MySQL and I think my original snippet was missing a space

    just posting to note that if you are only selecting from 1 table then there is no reason to prefix the fieldnames (even in a join you don't always need them)
    PHP Code:
    <?php
    $sort 
    mysql_real_escape_string($_GET['sort']);
    $order='';
    if(
    strtolower($_GET['order'])=='desc'){
        
    $order='DESC';
    }
    $result mysql_query("
        SELECT surname, firstname, nickname, email, 
        DATE_FORMAT(signup, '%d %b %Y') AS signup, 
        mobile, bay, aisle, seat, oa_id, club_member, seasontix, anchor 
        FROM oamembers 
        ORDER BY $sort 
        $order
    "
    );
    ?>

    Ive tested your piece of code in a few other pages Im usingand it works fine and I want to know if its possible using this snippet to sort by two fields instead of just one?..

    example using one field to sort by
    php?sort=bay&order=desc

    how could I use this so it would sort by two fields for example the field bay then sort by aisle as well.
    so if I had 5 records like below which were sorted just by the bay field

    bay 1 aisle 3
    bay 1 aisle 2
    bay 1 aisle 1
    bay 1 aisle 4
    bay 1 aisle 2

    how could I use this to make them sort by the bay field first then the aisle field ?

    bay 1 aisle 1
    bay 1 aisle 2
    bay 1 aisle 3
    bay 1 aisle 4
    bay 1 aisle 5

    I have tried using the link below without any success.

    php?sort=bay,aisle&order=desc


  •  

    Posting Permissions

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