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
    Feb 2010
    Posts
    74
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Sort Query By Clicking Column Header

    I want to be able to re-order the list by clicking on the respective header. This is my code

    Code:
    <table border="0" cellspacing="0" style="border-top:solid 1px #333;">
     <tr style="border-left:solid 1px #333;border-right:solid 1px #333; border-bottom:solid 1px #333; background-color:#CCCCCC;" >
     	<td style="width:200px;padding:2px 2px 2px 2px;"><b>Entrant</b></td>
     	<td style="width:50px;padding:2px 2px 2px 2px;"><b>Points</b></td>
     	<td style="width:150px;padding:2px 2px 2px 2px;"><b>Choosen Winner</b></td>
     	<td style="width:100px;padding:2px 2px 2px 2px;"><b>View Bracket</b></td>
     </tr>
     </table>
     <table border="0" cellspacing="0" style="background-image:url(images/table-bg.png);border-bottom:solid 1px #333;">
    <?php
    // make connection
    mysql_connect("localhost", "root", "moreland") or die('Could not connect');
    mysql_select_db("ipool") or die('Could not connect to database');
    
    // build query
     $query = mysql_query("SELECT * FROM TestTable ORDER by ID");
    // display results
     while ($row  = mysql_fetch_array($query)) {
     echo 
     "<tr style='border-left:solid 1px #333;border-right:solid 1px #333;'>
     	<td style='width:200px;padding:2px 2px 2px 2px;'>" .$row['fullname']. "</td>
    	<td style='width:50px;padding:2px 2px 2px 2px;'>N/A</td>
    	<td style='width:150px;padding:2px 2px 2px 2px;'>" .$row['pick6_1']. "</td>
    	<td style='width:100px;padding:2px 2px 2px 2px;'><p class='view-bracket-link'><a href='system/displays/display-bracket-id-".$row['ID'].".php'>View Bracket</a></p></td>
      </tr>"
    
     ;}
    
    ?>
     </table>
    you can see it in action here: http://www.tomhilsee.com/ipool/standings.php

    thanks
    Last edited by thilss0o; 03-07-2010 at 09:18 PM. Reason: Sorry for the PHP not color coded, the tags were getting rid of word wrap

  • #2
    Supreme Master coder! _Aerospace_Eng_'s Avatar
    Join Date
    Dec 2004
    Location
    In a place far, far away...
    Posts
    19,291
    Thanks
    2
    Thanked 1,043 Times in 1,019 Posts
    Make the headers links and then in the link append a query string to the url. Use your php to get this query string. If it is set pass it through mysql_real_escape_string and then use that as the ORDER BY instead of ID.
    ||||If you are getting paid to do a job, don't ask for help on it!||||

  • #3
    Master Coder
    Join Date
    Jun 2003
    Location
    Cottage Grove, Minnesota
    Posts
    9,549
    Thanks
    8
    Thanked 1,095 Times in 1,086 Posts
    Use cookies ...

    Set two cookies, one for which column to sort, and which order ASC or DESC.

    Like this:
    PHP Code:
    <?php

    // Assign the name of your script so it will work no matter what you named it.
    $url=$_SERVER['PHP_SELF'];

    // If a sort was requested, then process it.
    if($action == "sort"){
    $s=$_GET['s'];
    $o=$_GET['o'];
    setcookie("csort"$stime()+604800);
    setcookie("order"$otime()+604800);
    // after cookies are set, re-run the script.
    header ("location: $url");
    }

    // set the default, in case there are no cookies set.
    $csort="ID";
    $order="DESC";

    // Read the Sort Cookie (if it exists)
    if (isset($_COOKIE["csort"])){
    $csort=$_COOKIE["csort"];
    }
    if (isset(
    $_COOKIE["order"])){
    $order=$_COOKIE["order"];
    }

    // sanitize any variables used in the MySQL query (always sanitize the variables).
    $csort mysql_real_escape_string($csort);
    $order mysql_real_escape_string($order);
    ?>

    <table border="0" cellspacing="0" style="border-top:solid 1px #333;">
    <tr style="border-left:solid 1px #333;border-right:solid 1px #333; border-bottom:solid 1px #333; background-color:#CCCCCC;" >
         <td style="width:200px;padding:2px 2px 2px 2px;"><b>Sort by: <a href="<?=$url?>?action=sort&s=fullname&s=ASC">asc</a> | <a href="<?=$url?>?action=sort&s=fullname&s=DESC">desc</a></b></td>
         <td style="width:50px;padding:2px 2px 2px 2px;"><b>Sort by: asc | desc</b></td>
         <td style="width:150px;padding:2px 2px 2px 2px;"><b>Sort by: <a href="<?=$url?>?action=sort&s=pick6_1&s=ASC">asc</a> | <a href="<?=$url?>?action=sort&s=pick6_1&s=DESC">desc</a></b></td>
         <td style="width:100px;padding:2px 2px 2px 2px;"><b>Sort by: <a href="<?=$url?>?action=sort&s=ID&s=ASC">asc</a> | <a href="<?=$url?>?action=sort&s=ID&s=DESC">desc</a></b></td>
     </tr>
     <tr style="border-left:solid 1px #333;border-right:solid 1px #333; border-bottom:solid 1px #333; background-color:#CCCCCC;" >
         <td style="width:200px;padding:2px 2px 2px 2px;"><b>Entrant</b></td>
         <td style="width:50px;padding:2px 2px 2px 2px;"><b>Points</b></td>
         <td style="width:150px;padding:2px 2px 2px 2px;"><b>Choosen Winner</b></td>
         <td style="width:100px;padding:2px 2px 2px 2px;"><b>View Bracket</b></td>
     </tr>
     </table>
     <table border="0" cellspacing="0" style="background-image:url(images/table-bg.png);border-bottom:solid 1px #333;">
    <?php
    // make connection
    mysql_connect("localhost""root""moreland") or die('Could not connect');
    mysql_select_db("ipool") or die('Could not connect to database');

    // build query
     
    $query mysql_query("SELECT * FROM TestTable ORDER by $csort $order");
    // display results
     
    while ($row  mysql_fetch_array($query)) {
     echo 
     
    "<tr style='border-left:solid 1px #333;border-right:solid 1px #333;'>
         <td style='width:200px;padding:2px 2px 2px 2px;'>" 
    .$row['fullname']. "</td>
        <td style='width:50px;padding:2px 2px 2px 2px;'>N/A</td>
        <td style='width:150px;padding:2px 2px 2px 2px;'>" 
    .$row['pick6_1']. "</td>
        <td style='width:100px;padding:2px 2px 2px 2px;'><p class='view-bracket-link'><a href='system/displays/display-bracket-id-"
    .$row['ID'].".php'>View Bracket</a></p></td>
      </tr>"

     
    ;}

    ?>
     </table>
    EDIT:
    The same answer as Aerospace ... we posted at the same time.
    I like to use cookies though, because the sort will remain the same when you revisit the page.
    sort of like saving your sort preference.

    I didn't make a way to delete the cookies, but putting a negative number in for the time will remove the cookie(s).


    .
    Last edited by mlseim; 03-07-2010 at 10:25 PM.

  • #4
    New Coder
    Join Date
    Feb 2010
    Posts
    74
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Hey looks great thanks! Looks like you are both doing the same thing, but is it possible to do it without the cookies? I just would rather the user to be able to see the default sorting if they return.


    --- and i'm also having trouble with the stuff actually sorting... the url changes, and it seems like it should work. I tried doing a manual ORDER by to see if the columns are actually sortable, and they are, but i just cant get it to do it by clicking on asc or dec

    any thoughts?
    http://www.tomhilsee.com/ipool/standings.php (updated with code from mlseim post)

  • #5
    Master Coder
    Join Date
    Jun 2003
    Location
    Cottage Grove, Minnesota
    Posts
    9,549
    Thanks
    8
    Thanked 1,095 Times in 1,086 Posts
    Give us a new listing of what you actually have ...

    My guess is you're missing this:
    $query = mysql_query("SELECT * FROM TestTable ORDER by $csort $order");

    We can use sessions instead of cookies, those go away when the user closes their browser.

  • #6
    New Coder
    Join Date
    Feb 2010
    Posts
    74
    Thanks
    9
    Thanked 0 Times in 0 Posts
    sessions would be ideal, yes. I do have the ORDER BY $scort or whatever, ill post the listing anyway tho:

    PHP Code:
    <?php 

    // Assign the name of your script so it will work no matter what you named it. 
    $url=$_SERVER['PHP_SELF']; 

    // If a sort was requested, then process it. 
    if($action == "sort"){ 
    $s=$_GET['s']; 
    $o=$_GET['o']; 
    setcookie("csort"$stime()+604800); 
    setcookie("order"$otime()+604800); 
    // after cookies are set, re-run the script. 
    header ("location: $url"); 


    // set the default, in case there are no cookies set. 
    $csort="ID"
    $order="DESC"

    // Read the Sort Cookie (if it exists) 
    if (isset($_COOKIE["csort"])){ 
    $csort=$_COOKIE["csort"]; 

    if (isset(
    $_COOKIE["order"])){ 
    $order=$_COOKIE["order"]; 


    // sanitize any variables used in the MySQL query (always sanitize the variables). 
    $csort mysql_real_escape_string($csort); 
    $order mysql_real_escape_string($order); 
    ?> 

    <table border="0" cellspacing="0" style="border-top:solid 1px #333;"> 
    <tr style="border-left:solid 1px #333;border-right:solid 1px #333; border-bottom:solid 1px #333; background-color:#CCCCCC;" > 
         <td style="width:200px;padding:2px 2px 2px 2px;"><b>Sort by: <a href="<?=$url?>?action=sort&s=fullname&s=ASC">asc</a> | <a href="<?=$url?>?action=sort&s=fullname&s=DESC">desc</a></b></td> 
         <td style="width:50px;padding:2px 2px 2px 2px;"><b>Sort by: asc | desc</b></td> 
         <td style="width:150px;padding:2px 2px 2px 2px;"><b>Sort by: <a href="<?=$url?>?action=sort&s=pick6_1&s=ASC">asc</a> | <a href="<?=$url?>?action=sort&s=pick6_1&s=DESC">desc</a></b></td> 
         <td style="width:100px;padding:2px 2px 2px 2px;"><b>Sort by: <a href="<?=$url?>?action=sort&s=ID&s=ASC">asc</a> | <a href="<?=$url?>?action=sort&s=ID&s=DESC">desc</a></b></td> 
     </tr> 
     <tr style="border-left:solid 1px #333;border-right:solid 1px #333; border-bottom:solid 1px #333; background-color:#CCCCCC;" > 
         <td style="width:200px;padding:2px 2px 2px 2px;"><b>Entrant</b></td> 
         <td style="width:50px;padding:2px 2px 2px 2px;"><b>Points</b></td> 
         <td style="width:150px;padding:2px 2px 2px 2px;"><b>Choosen Winner</b></td> 
         <td style="width:100px;padding:2px 2px 2px 2px;"><b>View Bracket</b></td> 
     </tr> 
     </table> 
     <table border="0" cellspacing="0" style="background-image:url(images/table-bg.png);border-bottom:solid 1px #333;">
    <?php 
    // make connection 
    mysql_connect("localhost""root""moreland") or die('Could not connect'); 
    mysql_select_db("ipool") or die('Could not connect to database'); 

    // build query 
     
    $query mysql_query("SELECT * FROM TestTable ORDER by $csort $order"); 
    // display results 
     
    while ($row  mysql_fetch_array($query)) { 
     echo  
     
    "<tr style='border-left:solid 1px #333;border-right:solid 1px #333;'> 
         <td style='width:200px;padding:2px 2px 2px 2px;'>" 
    .$row['fullname']. "</td> 
        <td style='width:50px;padding:2px 2px 2px 2px;'>N/A</td> 
        <td style='width:150px;padding:2px 2px 2px 2px;'>" 
    .$row['pick6_1']. "</td> 
        <td style='width:100px;padding:2px 2px 2px 2px;'><p class='view-bracket-link'><a href='system/displays/display-bracket-id-"
    .$row['ID'].".php'>View Bracket</a></p></td> 
      </tr>" 

     
    ;} 

    ?> 
     </table>
    </div>
    thanks.... i think its the same as your previous post tho, wasn't sure if changing anything was necessary to make it work, but it didnt seem like it as you said the sort should work regardless of what the name is...

  • #7
    Master Coder
    Join Date
    Jun 2003
    Location
    Cottage Grove, Minnesota
    Posts
    9,549
    Thanks
    8
    Thanked 1,095 Times in 1,086 Posts
    With SESSIONS instead ...

    Try this ... see if it works this time ...

    PHP Code:
    <?php 
    session_start
    ();

    // Assign the name of your script so it will work no matter what you named it. 
    $url=$_SERVER['PHP_SELF']; 

    // If a sort was requested, then process it. 
    if($action == "sort"){ 
    $s=$_GET['s']; 
    $o=$_GET['o']; 
    $_SESSION['csort'] = $s
    $_SESSION['order'] = $o
    // after sessions are set, re-run the script. 
    header ("location: $url"); 


    // set the default, in case session is not set. 
    $csort="ID"
    $order="DESC"

    // Read the SESSION variables (if they exist) 
    if(isset($_SESSION['csort'])){ 
    $csort=$_SESSION['csort']; 

    if(isset(
    $_SESSION['order'])){ 
    $order=$_SESSION['order']; 


    // sanitize any variables used in the MySQL query (always sanitize the variables). 
    $csort mysql_real_escape_string($csort); 
    $order mysql_real_escape_string($order); 
    ?> 

    <table border="0" cellspacing="0" style="border-top:solid 1px #333;"> 
    <tr style="border-left:solid 1px #333;border-right:solid 1px #333; border-bottom:solid 1px #333; background-color:#CCCCCC;" > 
         <td style="width:200px;padding:2px 2px 2px 2px;"><b>Sort by: <a href="<?=$url?>?action=sort&s=fullname&s=ASC">asc</a> | <a href="<?=$url?>?action=sort&s=fullname&s=DESC">desc</a></b></td> 
         <td style="width:50px;padding:2px 2px 2px 2px;"><b> &nbsp; </b></td> 
         <td style="width:150px;padding:2px 2px 2px 2px;"><b>Sort by: <a href="<?=$url?>?action=sort&s=pick6_1&s=ASC">asc</a> | <a href="<?=$url?>?action=sort&s=pick6_1&s=DESC">desc</a></b></td> 
         <td style="width:100px;padding:2px 2px 2px 2px;"><b>Sort by: <a href="<?=$url?>?action=sort&s=ID&s=ASC">asc</a> | <a href="<?=$url?>?action=sort&s=ID&s=DESC">desc</a></b></td> 
     </tr> 
     <tr style="border-left:solid 1px #333;border-right:solid 1px #333; border-bottom:solid 1px #333; background-color:#CCCCCC;" > 
         <td style="width:200px;padding:2px 2px 2px 2px;"><b>Entrant</b></td> 
         <td style="width:50px;padding:2px 2px 2px 2px;"><b>Points</b></td> 
         <td style="width:150px;padding:2px 2px 2px 2px;"><b>Choosen Winner</b></td> 
         <td style="width:100px;padding:2px 2px 2px 2px;"><b>View Bracket</b></td> 
     </tr> 
     </table> 
     <table border="0" cellspacing="0" style="background-image:url(images/table-bg.png);border-bottom:solid 1px #333;">
    <?php 
    // make connection 
    mysql_connect("localhost""root""moreland") or die('Could not connect'); 
    mysql_select_db("ipool") or die('Could not connect to database'); 

    // build query 
     
    $query mysql_query("SELECT * FROM TestTable ORDER by $csort $order"); 
    // display results 
     
    while ($row  mysql_fetch_array($query)) { 
     echo  
     
    "<tr style='border-left:solid 1px #333;border-right:solid 1px #333;'> 
         <td style='width:200px;padding:2px 2px 2px 2px;'>" 
    .$row['fullname']. "</td> 
        <td style='width:50px;padding:2px 2px 2px 2px;'>N/A</td> 
        <td style='width:150px;padding:2px 2px 2px 2px;'>" 
    .$row['pick6_1']. "</td> 
        <td style='width:100px;padding:2px 2px 2px 2px;'><p class='view-bracket-link'><a href='system/displays/display-bracket-id-"
    .$row['ID'].".php'>View Bracket</a></p></td> 
      </tr>" 

     
    ;} 

    ?> 
     </table>
    </div>

  • #8
    New Coder
    Join Date
    Feb 2010
    Posts
    74
    Thanks
    9
    Thanked 0 Times in 0 Posts
    thanks, but sorry it still doesnt work, you can see for yourself http://tomhilsee.com/ipool/standings...fullname&s=ASC

    its the exact code you entered, should i be editing some line somewhere to fit my site? let me know, and thanks for your continued help

  • #9
    Master Coder
    Join Date
    Jun 2003
    Location
    Cottage Grove, Minnesota
    Posts
    9,549
    Thanks
    8
    Thanked 1,095 Times in 1,086 Posts
    Oh jeez ... maybe it's as simple as missing single-quotes?

    Change this line:

    $query = mysql_query("SELECT * FROM TestTable ORDER by $csort $order");

    To this (copy and paste this line over the old one):

    $query = mysql_query("SELECT * FROM TestTable ORDER BY `$csort` $order");



    .

  • #10
    New Coder
    Join Date
    Feb 2010
    Posts
    74
    Thanks
    9
    Thanked 0 Times in 0 Posts
    nah ORDER BY doesn't require the field to be in quotes anyway, but i tried it and still no luck.

    do you want to mess around with the file on my server? i trust you enough lol, ill trust anyone. ill pm you the ftp info if you want to mess with it

  • #11
    New Coder
    Join Date
    Feb 2010
    Posts
    74
    Thanks
    9
    Thanked 0 Times in 0 Posts
    well if worst comes to worst i could have it link to new pages with different ORDER BY fields, but that would be pretty lame

  • #12
    Master Coder
    Join Date
    Jun 2003
    Location
    Cottage Grove, Minnesota
    Posts
    9,549
    Thanks
    8
    Thanked 1,095 Times in 1,086 Posts
    PM me with your FTP account info ...

    this is driving me nuts ... there has to be a reason, I just can't see it,
    but if I experiment, I might get it. Create a temporary FTP login that you
    can erase in a couple of days (then PM me).

  • #13
    New Coder
    Join Date
    Feb 2010
    Posts
    74
    Thanks
    9
    Thanked 0 Times in 0 Posts
    i just pmed you, not sure if it went through tho, my sent items are still empty, let me know if you got it

  • #14
    Master Coder
    Join Date
    Jun 2003
    Location
    Cottage Grove, Minnesota
    Posts
    9,549
    Thanks
    8
    Thanked 1,095 Times in 1,086 Posts
    I found a few stupid mistakes on my part ... my bad.
    It's OK now.

    Make a safe copy of it in case you decide to do other things to it.

  • Users who have thanked mlseim for this post:

    thilss0o (03-09-2010)

  • #15
    New Coder
    Join Date
    Feb 2010
    Posts
    74
    Thanks
    9
    Thanked 0 Times in 0 Posts
    yep, works great now, thanks!


  •  

    Posting Permissions

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