[Need Help - PHP/MySQL] - Clickable/sortable table headers

  • kingdutka
  • Newbie
  • Newbie
  • kingdutka
  • Posts: 10
  • Loc: USA

Post 3+ Months Ago

So, I followed this guide:
http://scriptplayground .com/tutorials/php/Printing-a-MySQL-table-to-a-dynamic-HTML-table-with-PHP/

And got myself a nice looking table for my database. Now I want it to be 'sortable'.

How would I make the column headings/titles 'clickable' so that the table is 'ORDER BY' that column? Also, I'd like to click a second time to change the order from ascending to descending or vice versa.

Example:

A table that has columns: Name, Age, Weight

The table lists out by 'Name', descending, by default. When I click on 'Name', the order changes from descending to ascending. When I click on 'Age', the table is re-ordered by Age, descending.

Any ideas?
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • kingdutka
  • Newbie
  • Newbie
  • kingdutka
  • Posts: 10
  • Loc: USA

Post 3+ Months Ago

How long does it usually take to get replies on this forum? I am new to OZZU, so I don't know much about it, but the forums I usually participate in only take an hour or so at most to get answers.... It's been 2 days since I started this topic... Doesn't anyone know how to do this stuff?
  • SpooF
  • ٩๏̯͡๏۶
  • Bronze Member
  • User avatar
  • Posts: 3422
  • Loc: Richland, WA

Post 3+ Months Ago

Could you post your code where you make the query string?
  • kingdutka
  • Newbie
  • Newbie
  • kingdutka
  • Posts: 10
  • Loc: USA

Post 3+ Months Ago

I'll give you everything I have :)

Wii.php
Code: [ Select ]
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3 .org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3 .org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Nintendo Wii Game Lists</title>
</head>
<?php
include("./SQLTables.php");
?>

<body>

<div align="center">
    <h1>Nintendo Wii Games Lists</h1>
</div>

<div align="center">
 <h2>Wish List</h2>
<p><?php
 //Connection
    $global_dbh = mysql_connect('localhost','root','mangos') or die("Unable to connect: " . mysql_error() . "<br>");
    $database = mysql_select_db("wii game list") or die( "Unable to select database<br>");
    $table = "wish_list";
    $sort = "Name";
    display_db_table($table, $sort, $global_dbh, FALSE, "border='5'");
    ?></p>

<div align="center"> 
 <h2>Owned List</h2>
<p><?php
 //Connection
    $global_dbh = mysql_connect('localhost','root','mangos') or die("Unable to connect: " . mysql_error() . "<br>");
    $database = mysql_select_db("wii game list") or die( "Unable to select database<br>");
    $table = "owned_list";
    $sort = "Name";
    display_db_table($table, $sort, $global_dbh, FALSE, "border='5'");
    ?></p>
  
</div>


</body>
</html>
  1. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3 .org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  2. <html xmlns="http://www.w3 .org/1999/xhtml">
  3. <head>
  4. <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
  5. <title>Nintendo Wii Game Lists</title>
  6. </head>
  7. <?php
  8. include("./SQLTables.php");
  9. ?>
  10. <body>
  11. <div align="center">
  12.     <h1>Nintendo Wii Games Lists</h1>
  13. </div>
  14. <div align="center">
  15.  <h2>Wish List</h2>
  16. <p><?php
  17.  //Connection
  18.     $global_dbh = mysql_connect('localhost','root','mangos') or die("Unable to connect: " . mysql_error() . "<br>");
  19.     $database = mysql_select_db("wii game list") or die( "Unable to select database<br>");
  20.     $table = "wish_list";
  21.     $sort = "Name";
  22.     display_db_table($table, $sort, $global_dbh, FALSE, "border='5'");
  23.     ?></p>
  24. <div align="center"> 
  25.  <h2>Owned List</h2>
  26. <p><?php
  27.  //Connection
  28.     $global_dbh = mysql_connect('localhost','root','mangos') or die("Unable to connect: " . mysql_error() . "<br>");
  29.     $database = mysql_select_db("wii game list") or die( "Unable to select database<br>");
  30.     $table = "owned_list";
  31.     $sort = "Name";
  32.     display_db_table($table, $sort, $global_dbh, FALSE, "border='5'");
  33.     ?></p>
  34.   
  35. </div>
  36. </body>
  37. </html>


SQLTables.php
Code: [ Select ]

<?php
function display_db_query($query_string, $connection, $image, $table_params) {
    $result_id = mysql_query($query_string, $connection) or die("display_db_query:" . mysql_error());
    $column_count = mysql_num_fields($result_id) or die("display_db_query:" . mysql_error());
    // Here the table attributes from the $table_params variable are added
    print("<TABLE $table_params >\n");
    // Print Headers
    print("<TR>");
    for($column_num = 0; $column_num < $column_count; $column_num++) {
        $field_name = mysql_field_name($result_id, $column_num);
        echo ("<TH>$field_name</TH>");
    }
    print("</TR>\n");
    // Print the body
    while($row = mysql_fetch_row($result_id)) {
        print("<TR ALIGN=LEFT VALIGN=TOP>");
        for($column_num = 0; $column_num < $column_count; $column_num++) {
            if($column_num==0 && $image){
              print("<TD><img src=imageLoader.php?image=amf_bowling</TD>\n");
            }
            else{
              print("<TD>$row[$column_num]</TD>\n");
            }
        }
        print("</TR>\n");
    }
    print("</TABLE>\n");
}

function display_db_table($tablename, $sort, $connection, $image, $table_params) {
    $query_string = "SELECT * FROM $tablename ORDER BY $sort";
    display_db_query($query_string, $connection, $image, $table_params);
}
?>
  1. <?php
  2. function display_db_query($query_string, $connection, $image, $table_params) {
  3.     $result_id = mysql_query($query_string, $connection) or die("display_db_query:" . mysql_error());
  4.     $column_count = mysql_num_fields($result_id) or die("display_db_query:" . mysql_error());
  5.     // Here the table attributes from the $table_params variable are added
  6.     print("<TABLE $table_params >\n");
  7.     // Print Headers
  8.     print("<TR>");
  9.     for($column_num = 0; $column_num < $column_count; $column_num++) {
  10.         $field_name = mysql_field_name($result_id, $column_num);
  11.         echo ("<TH>$field_name</TH>");
  12.     }
  13.     print("</TR>\n");
  14.     // Print the body
  15.     while($row = mysql_fetch_row($result_id)) {
  16.         print("<TR ALIGN=LEFT VALIGN=TOP>");
  17.         for($column_num = 0; $column_num < $column_count; $column_num++) {
  18.             if($column_num==0 && $image){
  19.               print("<TD><img src=imageLoader.php?image=amf_bowling</TD>\n");
  20.             }
  21.             else{
  22.               print("<TD>$row[$column_num]</TD>\n");
  23.             }
  24.         }
  25.         print("</TR>\n");
  26.     }
  27.     print("</TABLE>\n");
  28. }
  29. function display_db_table($tablename, $sort, $connection, $image, $table_params) {
  30.     $query_string = "SELECT * FROM $tablename ORDER BY $sort";
  31.     display_db_query($query_string, $connection, $image, $table_params);
  32. }
  33. ?>
  • SpooF
  • ٩๏̯͡๏۶
  • Bronze Member
  • User avatar
  • Posts: 3422
  • Loc: Richland, WA

Post 3+ Months Ago

What your doing is a little complicated since its completely dynamic.

Basically what your going to what to do is for each field you will want to make the name a link and send what field you want to sort by and attached the direction (asc,desc)
Code: [ Select ]
for($column_num = 0; $column_num < $column_count; $column_num++) {
    $field_name = mysql_field_name($result_id, $column_num);
    echo ("<TH><a href="page.php?order_by=$field_name&sorting=$sort</TH>");
}
  1. for($column_num = 0; $column_num < $column_count; $column_num++) {
  2.     $field_name = mysql_field_name($result_id, $column_num);
  3.     echo ("<TH><a href="page.php?order_by=$field_name&sorting=$sort</TH>");
  4. }


Your $sort will change depending on whats currently set.

Code: [ Select ]
$order_by = (isset($_GET['order_by'])) ? $_GET['order_by'] : 'Name';
$sorting = (isset($_GET['sorting'])) ? $_GET['sorting'] : 'desc';
  1. $order_by = (isset($_GET['order_by'])) ? $_GET['order_by'] : 'Name';
  2. $sorting = (isset($_GET['sorting'])) ? $_GET['sorting'] : 'desc';


Code: [ Select ]
switch($sorting) {
    case "asc":
        $sort = 'desc';
        break;
    case "desc":
        $sort = 'asc';
        break;
}
  1. switch($sorting) {
  2.     case "asc":
  3.         $sort = 'desc';
  4.         break;
  5.     case "desc":
  6.         $sort = 'asc';
  7.         break;
  8. }
  • kingdutka
  • Newbie
  • Newbie
  • kingdutka
  • Posts: 10
  • Loc: USA

Post 3+ Months Ago

Seriously... THANKS!!!

Wii.php
Code: [ Select ]
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3 .org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3 .org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Nintendo Wii Game Lists</title>
</head>
<?php
include("./SQLTables.php");
?>

<body>

<div align="center">
    <h1>Nintendo Wii Games Lists</h1>
</div>

<div align="center">
 <h2>Wish List</h2>
<p><?php
 //Connection
    $global_dbh = mysql_connect('localhost','root','mangos') or die("Unable to connect: " . mysql_error() . "<br>");
    $database = mysql_select_db("wii game list") or die( "Unable to select database<br>");
    $table = "wish_list";
    $page = "Wii";
    display_db_table($page, $table, $global_dbh, FALSE, "border='5'");
    ?></p>

<div align="center"> 
 <h2>Owned List</h2>
<p><?php
 //Connection
    $global_dbh = mysql_connect('localhost','root','mangos') or die("Unable to connect: " . mysql_error() . "<br>");
    $database = mysql_select_db("wii game list") or die( "Unable to select database<br>");
    $table = "owned_list";
    $page = "Wii";
    display_db_table($page, $table, $global_dbh, FALSE, "border='5'");
    ?></p>
  
</div>


</body>
</html>
  1. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3 .org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  2. <html xmlns="http://www.w3 .org/1999/xhtml">
  3. <head>
  4. <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
  5. <title>Nintendo Wii Game Lists</title>
  6. </head>
  7. <?php
  8. include("./SQLTables.php");
  9. ?>
  10. <body>
  11. <div align="center">
  12.     <h1>Nintendo Wii Games Lists</h1>
  13. </div>
  14. <div align="center">
  15.  <h2>Wish List</h2>
  16. <p><?php
  17.  //Connection
  18.     $global_dbh = mysql_connect('localhost','root','mangos') or die("Unable to connect: " . mysql_error() . "<br>");
  19.     $database = mysql_select_db("wii game list") or die( "Unable to select database<br>");
  20.     $table = "wish_list";
  21.     $page = "Wii";
  22.     display_db_table($page, $table, $global_dbh, FALSE, "border='5'");
  23.     ?></p>
  24. <div align="center"> 
  25.  <h2>Owned List</h2>
  26. <p><?php
  27.  //Connection
  28.     $global_dbh = mysql_connect('localhost','root','mangos') or die("Unable to connect: " . mysql_error() . "<br>");
  29.     $database = mysql_select_db("wii game list") or die( "Unable to select database<br>");
  30.     $table = "owned_list";
  31.     $page = "Wii";
  32.     display_db_table($page, $table, $global_dbh, FALSE, "border='5'");
  33.     ?></p>
  34.   
  35. </div>
  36. </body>
  37. </html>


SQLTables.php
Code: [ Select ]
<?php
function display_db_query($page, $query_string, $connection, $sort, $image, $table_params) {
    $result_id = mysql_query($query_string, $connection) or die("display_db_query:" . mysql_error());
    $column_count = mysql_num_fields($result_id) or die("display_db_query:" . mysql_error());
    // Here the table attributes from the $table_params variable are added
    print("<TABLE $table_params >\n");
    // Print Headers
    print("<TR>");
    for($column_num = 0; $column_num < $column_count; $column_num++) {
        $field_name = mysql_field_name($result_id, $column_num);
        echo ("<TH><a href=\"$page.php?order_by=$field_name&sorting=$sort\">$field_name</a></TH>");
    }
    print("</TR>\n");
    // Print the body
    while($row = mysql_fetch_row($result_id)) {
        print("<TR ALIGN=LEFT VALIGN=TOP>");
        for($column_num = 0; $column_num < $column_count; $column_num++) {
            if($column_num==0 && $image){
              print("<TD><img src=imageLoader.php?image=amf_bowling</TD>\n");
            }
            else{
              print("<TD>$row[$column_num]</TD>\n");
            }
        }
        print("</TR>\n");
    }
    print("</TABLE>\n");
}

function display_db_table($page, $tablename, $connection, $image, $table_params) {
    $order_by = (isset($_GET['order_by'])) ? $_GET['order_by'] : 'Name';
    $sorting = (isset($_GET['sorting'])) ? $_GET['sorting'] : 'desc';
    switch($sorting){
  case "asc":
    $sort = 'desc';
    break;
  case "desc":
    $sort = 'asc';
    break;
    }
    $query_string = "SELECT * FROM $tablename ORDER BY $order_by"." $sort";
    display_db_query($page, $query_string, $connection, $sort, $image, $table_params);
}
?>
  1. <?php
  2. function display_db_query($page, $query_string, $connection, $sort, $image, $table_params) {
  3.     $result_id = mysql_query($query_string, $connection) or die("display_db_query:" . mysql_error());
  4.     $column_count = mysql_num_fields($result_id) or die("display_db_query:" . mysql_error());
  5.     // Here the table attributes from the $table_params variable are added
  6.     print("<TABLE $table_params >\n");
  7.     // Print Headers
  8.     print("<TR>");
  9.     for($column_num = 0; $column_num < $column_count; $column_num++) {
  10.         $field_name = mysql_field_name($result_id, $column_num);
  11.         echo ("<TH><a href=\"$page.php?order_by=$field_name&sorting=$sort\">$field_name</a></TH>");
  12.     }
  13.     print("</TR>\n");
  14.     // Print the body
  15.     while($row = mysql_fetch_row($result_id)) {
  16.         print("<TR ALIGN=LEFT VALIGN=TOP>");
  17.         for($column_num = 0; $column_num < $column_count; $column_num++) {
  18.             if($column_num==0 && $image){
  19.               print("<TD><img src=imageLoader.php?image=amf_bowling</TD>\n");
  20.             }
  21.             else{
  22.               print("<TD>$row[$column_num]</TD>\n");
  23.             }
  24.         }
  25.         print("</TR>\n");
  26.     }
  27.     print("</TABLE>\n");
  28. }
  29. function display_db_table($page, $tablename, $connection, $image, $table_params) {
  30.     $order_by = (isset($_GET['order_by'])) ? $_GET['order_by'] : 'Name';
  31.     $sorting = (isset($_GET['sorting'])) ? $_GET['sorting'] : 'desc';
  32.     switch($sorting){
  33.   case "asc":
  34.     $sort = 'desc';
  35.     break;
  36.   case "desc":
  37.     $sort = 'asc';
  38.     break;
  39.     }
  40.     $query_string = "SELECT * FROM $tablename ORDER BY $order_by"." $sort";
  41.     display_db_query($page, $query_string, $connection, $sort, $image, $table_params);
  42. }
  43. ?>


I'm a BIG fan of dynamic code :)

This all works fine and good, but... I have 2 tables on the page and it is reloading both of them using the 'click' from one table. So, if I click on the 'Name' column in the wish list, both the wish list and the owned list are getting organized by name. I can almost live with that, but if you click the 'price' column, the 'owned list' table disappears because I don't have a 'price' column in the 'owned_list' database table, so that one gives me an error.

Not really sure how I can get around this one... I only want the one table to get reloaded when the heading links are clicked...
  • SpooF
  • ٩๏̯͡๏۶
  • Bronze Member
  • User avatar
  • Posts: 3422
  • Loc: Richland, WA

Post 3+ Months Ago

You can use a different variable name in the url to differ from each table.
  • kingdutka
  • Newbie
  • Newbie
  • kingdutka
  • Posts: 10
  • Loc: USA

Post 3+ Months Ago

SpooF wrote:
You can use a different variable name in the url to differ from each table.


Ya, I figured that much, but.., wouldn't I need to track all the tables on the page and make sure each one is listed out by the last heading that was clicked? I'd need this to be dynamic and expandable to %i number of tables on a single page...
  • SpooF
  • ٩๏̯͡๏۶
  • Bronze Member
  • User avatar
  • Posts: 3422
  • Loc: Richland, WA

Post 3+ Months Ago

heres an example of using sessions to store your data for each table.

PHP Code: [ Select ]
<?
// begin the session
session_start();
 
$table_name = (isset($_GET['table'])) ? $_GET['table'] : NULL;
$order_by = (isset($_GET['order_by'])) ? $_GET['order_by'] : 'id';
$sorting = (isset($_GET['sorting'])) ? $_GET['sorting'] : 'desc';
 
// Check to see if we have some data saved in our session
if(isset($_SESSION['table_info']))
{
   // Get data in session
   $table_info  = $_SESSION['table_info'];
}
else
{
   // There's no data in the session so lets initialize an array
   $table_info = array();
}
 
// If there is no table supplied we can't do much
if($table_name != NULL)
{
   // Check if the table already has data in our session
   if(isset($table_info[$table_name]))
   {
      // Get table data from session
      $table = $table_info[$table_name];     
   }
   else
   {
      // There's no data for the table so lets initialize an array for the table
      $table = array();
   }
   
   // Update table data (these should always be given in the url so we wont worry about not updating them if they are not set)
   $table['order_by'] = $order_by;
   $table['sorting'] = $sorting;
 
   // Put the table back into our array
   $table_info[$table_name] = $table;
}
 
// Update session with new data
$_SESSION['table_info'] = $table_info;
 
echo '<pre>';
print_r($table_info);
echo '</pre>';
?>
  1. <?
  2. // begin the session
  3. session_start();
  4.  
  5. $table_name = (isset($_GET['table'])) ? $_GET['table'] : NULL;
  6. $order_by = (isset($_GET['order_by'])) ? $_GET['order_by'] : 'id';
  7. $sorting = (isset($_GET['sorting'])) ? $_GET['sorting'] : 'desc';
  8.  
  9. // Check to see if we have some data saved in our session
  10. if(isset($_SESSION['table_info']))
  11. {
  12.    // Get data in session
  13.    $table_info  = $_SESSION['table_info'];
  14. }
  15. else
  16. {
  17.    // There's no data in the session so lets initialize an array
  18.    $table_info = array();
  19. }
  20.  
  21. // If there is no table supplied we can't do much
  22. if($table_name != NULL)
  23. {
  24.    // Check if the table already has data in our session
  25.    if(isset($table_info[$table_name]))
  26.    {
  27.       // Get table data from session
  28.       $table = $table_info[$table_name];     
  29.    }
  30.    else
  31.    {
  32.       // There's no data for the table so lets initialize an array for the table
  33.       $table = array();
  34.    }
  35.    
  36.    // Update table data (these should always be given in the url so we wont worry about not updating them if they are not set)
  37.    $table['order_by'] = $order_by;
  38.    $table['sorting'] = $sorting;
  39.  
  40.    // Put the table back into our array
  41.    $table_info[$table_name] = $table;
  42. }
  43.  
  44. // Update session with new data
  45. $_SESSION['table_info'] = $table_info;
  46.  
  47. echo '<pre>';
  48. print_r($table_info);
  49. echo '</pre>';
  50. ?>


This requires that your links give a table name, field for sorting and how you want it to be sorted.
  • SpooF
  • ٩๏̯͡๏۶
  • Bronze Member
  • User avatar
  • Posts: 3422
  • Loc: Richland, WA

Post 3+ Months Ago

Heres some out put from a few tests:

Code: [ Select ]
// Link: http://192.168.1.200/table_session.php?table=Data&order_by=Field1&sorting=desc

// Output
Array
(
  [Data] => Array
    (
      [order_by] => Field1
      [sorting] => desc
    )

)

// Link: http://192.168.1.200/table_session.php?table=Data&order_by=Field2&sorting=desc

// Output
Array
(
  [Data] => Array
    (
      [order_by] => Field2
      [sorting] => desc
    )

)

// Link: http://192.168.1.200/table_session.php?table=Users&order_by=Field1&sorting=asc

// Output
Array
(
  [Data] => Array
    (
      [order_by] => Field2
      [sorting] => desc
    )

  [Users] => Array
    (
      [order_by] => Field1
      [sorting] => asc
    )

)
  1. // Link: http://192.168.1.200/table_session.php?table=Data&order_by=Field1&sorting=desc
  2. // Output
  3. Array
  4. (
  5.   [Data] => Array
  6.     (
  7.       [order_by] => Field1
  8.       [sorting] => desc
  9.     )
  10. )
  11. // Link: http://192.168.1.200/table_session.php?table=Data&order_by=Field2&sorting=desc
  12. // Output
  13. Array
  14. (
  15.   [Data] => Array
  16.     (
  17.       [order_by] => Field2
  18.       [sorting] => desc
  19.     )
  20. )
  21. // Link: http://192.168.1.200/table_session.php?table=Users&order_by=Field1&sorting=asc
  22. // Output
  23. Array
  24. (
  25.   [Data] => Array
  26.     (
  27.       [order_by] => Field2
  28.       [sorting] => desc
  29.     )
  30.   [Users] => Array
  31.     (
  32.       [order_by] => Field1
  33.       [sorting] => asc
  34.     )
  35. )
  • kingdutka
  • Newbie
  • Newbie
  • kingdutka
  • Posts: 10
  • Loc: USA

Post 3+ Months Ago

Holy crap... LOL! I just started learning PHP this past Sunday and I am very busy with work atm, so it will take me a little while to digest all this and get something setup.

Thanks a ton for all the help so far! Now my family can bask in my geekness and maybe buy me a Wii game while they're at it! :D
  • kingdutka
  • Newbie
  • Newbie
  • kingdutka
  • Posts: 10
  • Loc: USA

Post 3+ Months Ago

So I've got everything working, but now I need to add in a "secondary sort". I understand how it works in SQL, but not in PHP. I'm looking at one line of code in particular:

Code: [ Select ]
echo ("<TH><a href=\"$page.php?order_by=$field_name&sorting=$sort&tablename=$tablename\">$field_name</a></TH>");


What would I add to the PHP URL to give a secondary sort like you see in example #4 here:
techonthenet . com/sql/order_by.php
  • righteous_trespasser
  • Scuffle
  • Genius
  • User avatar
  • Posts: 6230
  • Loc: South-Africa

Post 3+ Months Ago

I've got another solution for you ... how about jQuery's tablesorter.

Post Information

  • Total Posts in this topic: 13 posts
  • Users browsing this forum: No registered users and 125 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.