SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Addict
    Join Date
    Aug 2009
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    ORDER BY - Table headings

    Hi Chaps,

    I have a SQL Query:

    Code:
    mysql_select_db($database_conndb2, $conndb2);
    $query_rsProjects = "SELECT tbl_projects.projid, tbl_projects.projtitle, tbl_customers.custname, tbl_projects.projstart, tbl_projects.projdue, tbl_projects.projstatus, tbl_projects.projstatus, DATE_FORMAT(tbl_projects.projstart, '%d/%m/%Y') as projstart_format, DATE_FORMAT(tbl_projects.projdue, '%d/%m/%Y') as projdue_format, tbl_projects.projpriority FROM tbl_projects, tbl_customers WHERE tbl_projects.FK_custid=tbl_customers.custid AND tbl_projects.projstatus!='Complete' ORDER BY projid ASC";
    $rsProjects = mysql_query($query_rsProjects, $conndb2) or die(mysql_error());
    $row_rsProjects = mysql_fetch_assoc($rsProjects);
    $totalRows_rsProjects = mysql_num_rows($rsProjects);
    The data is presented in a table:

    HTML Code:
    <table border="0" cellpadding="0" cellspacing="0" id="tblrepeat_proj">
          <caption>
            <img src="../Images/project.jpg" border="0" /><a href="project_add.php"><img src="../Images/new.jpg" border="0" /></a><a href="#" onclick="print()"><img src="../Images/print.jpg" border="0"/></a>
          </caption>
          <tr>
            <th scope="col">Order No.</th>
            <th scope="col">Document Name</th>
            <th scope="col">Customer</th>
            <th scope="col">Start Date</th>
            <th scope="col">Due Date</th>
            <th scope="col">Status</th>
            <th scope="col">Edit</th>
            <th scope="col">Remove</th>
          </tr>
          <?php do { ?>
          <td>
          <?php echo $row_rsProjects['projid']; ?></span></td>
              <td><a href="project_details.php?id=<?php echo $row_rsProjects['projid']; ?>"><?php echo $row_rsProjects['projtitle']; ?></a></td>
              <td><?php echo $row_rsProjects['custname']; ?></td>
              <td><?php echo $row_rsProjects['projstart_format']; ?></td>
              <td><?php echo $row_rsProjects['projdue_format']; ?></td>
              <td><?php echo $row_rsProjects['projstatus']; ?></span></td>
              <td><a href="project_edit.php?id=<?php echo $row_rsProjects['projid']; ?>">Edit</a></td>
              <td><a href="project_remove.php?id=<?php echo $row_rsProjects['projid']; ?>" onclick="tmt_confirm('Are%20you%20sure%20you%20want%20to%20delete%20this%20reco rd?');return document.MM_returnValue">Remove</a></td>
            </tr>
            <?php } while ($row_rsProjects = mysql_fetch_assoc($rsProjects)); ?>
        </table>
    Is it possible to use the table headings as the control for the ORDER BY in the SQL query?

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    What do you mean?

  3. #3
    SitePoint Addict
    Join Date
    Aug 2009
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi guido,
    at the moment, as you can see in the SQL query, the data is ORDERed BY projid.
    What I want is, if you click on one of the table headings, Order No, Document Name, Customer, Start Date, Due Date, the data will then re-sort itself......

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    You could make the column headers into a link that points to the same page, and with a parameter like for example &sortorder in the query string. Give this parameter a unique value for each column you want to order by.
    Then, in your code, you'll have to construct the query, and use the value passed in the query string to create an ORDER BY clause that uses the requested column name.

  5. #5
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Put a link in the table heading

    <a href="?order_by=cust_name">Customer</a>

    PHP Code:
    // check order_by IS one of your permitted values:

    $allowed = array(
    "cust_name" ,
    );

    if( isset(
    $_GET['order_by'] && !in_array$_GET['order_by'], $allowed) ) {

    exit( 
    "someone is sending bad values" ) ;

    }

    $query_rsProjects $qry // your query with NO order by 


    if( $_GET['order_by'] === "cust_name" ){

    $query_rsProjects .= " ORDER BY cust_name";

    }else{

    $query_rsProjects .= " ORDER BY projid ASC";


    Thats a very simple example, you should be able to spot how you can use the value Customer to be inserted into your sql query - the important thing to spot is that you check the value against a white-list of permitted values before you just insert it into your sql stream = otherwise you face the dreaded SQL injection attacks.
    Last edited by Cups; Nov 9, 2009 at 08:19. Reason: Changed customer to cust_name to match the db column

  6. #6
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Second condition is not needed though.
    Just make it smth like
    $query_rsProjects = "SELECT ... ORDER BY $_GET[order_by]";
    ASC and DESC can be dynamic too

    Also, if your script called with some query string params, you will need to rebuild query string to make links in table headings
    http_build_query will do the thing

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    in my opinion (and i'm a database guy), the best solution is ~not~ to re-execute the query with a different ORDER BY parameter, but to use javascript

    no round trip to the server, just re-sort the results in the browser
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Addict
    Join Date
    Aug 2009
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks chaps, I've added the links, with parameters . . but I'm not sure where to stick your code. . .
    My query:
    Code:
    mysql_select_db($database_conndb2, $conndb2);
    $query_rsProjects = "SELECT tbl_projects.projid, tbl_projects.projtitle, tbl_customers.custname, tbl_projects.projstart, tbl_projects.projdue, tbl_projects.projstatus, tbl_projects.projstatus, DATE_FORMAT(tbl_projects.projstart, '%d/%m/%Y') as projstart_format, DATE_FORMAT(tbl_projects.projdue, '%d/%m/%Y') as projdue_format, tbl_projects.projpriority FROM tbl_projects, tbl_customers WHERE tbl_projects.FK_custid=tbl_customers.custid AND tbl_projects.projstatus!='Complete'";
    $rsProjects = mysql_query($query_rsProjects, $conndb2) or die(mysql_error());
    $row_rsProjects = mysql_fetch_assoc($rsProjects);
    $totalRows_rsProjects = mysql_num_rows($rsProjects);
    My links:
    HTML Code:
    <tr>
            <th scope="col"><a href="?order_by=projid">Order No.</a></th>
            <th scope="col"><a href="?order_by=projtitle">Document Name</a></th>
            <th scope="col"><a href="?order_by=custname">Customer</a></th>
            <th scope="col"><a href="?order_by=projstart">Start Date</a></th>
            <th scope="col"><a href="?order_by=projdue">Due Date</a></th>
            <th scope="col"><a href="?order_by=projstatus">Status</a></th>
            <th scope="col">Edit</th>
            <th scope="col">Remove</th>
          </tr>

  9. #9
    SitePoint Addict
    Join Date
    Aug 2009
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    so do I stick with the original query (ORDER by projid ASC) . . .?
    if so, where can I find the javascript I need?

  10. #10
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    in my opinion (and i'm a database guy), the best solution is ~not~ to re-execute the query with a different ORDER BY parameter, but to use javascript

    no round trip to the server, just re-sort the results in the browser
    I would agree with the database guy , unless of course, your result set is initially limited and you're not dealing with all the data.

    In this case, another round trip would be required.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  11. #11
    SitePoint Addict
    Join Date
    Aug 2009
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    if the Javascript option is the way to go, shall I re-post this in a Javascript forum then?

  12. #12
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Javascript is OK.
    I'd recommend it too.
    But only if there is only one page.
    If there is more, it will sort only one page, not whole table.


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
  •