Board index   FAQ   Search  
Register  Login
Board index php forum :: Database mySQL & php coding

Question: "Click to sort" table headings...

Codes here !

Moderators: macek, egami, gesf

Question: "Click to sort" table headings...

Postby devisevere » Sun Feb 16, 2003 11:26 pm

Ok, I'm fairly (read: very) new to this but I think I'm doing alright with picking it up.. anyway, here's my question.

I've got the code down for displaying a table in HTML from the MySQL database, however, for the final result I want to be able to sort them in ascending or descending order by clicking on the column header.

I'll just use the example I found, which I tried out, but it doesn't work.

Code: Select all
// Connecting to MySQL database
$link = mysql_connect($mysql_server, $mysql_user_name, $mysql_user_pass) or die("Could not connect to MySQL database");
print "Connected to MySQL database successfully";

// To read from the database, select it
 mysql_select_db("pbx") or die("Could not select database");

print ("<p>Table: " . htmlspecialchars ("pbx") . "</p>\n");
print ("<p>Click on a column name to sort the table by that column.</p>\n");

# Get the name of the column to sort by (optional).  If missing, use
# column one.  If present, perform simple validation on column name;
# it must consist only of alphanumeric or underscore characters.

$sort_col = get_param_val ("date");        # column name to sort by (optional)
if (!isset ($sort_col))
    $sort_col = "1";        # just sort by first column
else if (!ereg ("^[0-9a-zA-Z_]+$", $sort_col))
    die (htmlspecialchars ("Column name $sort_col is invalid"));

# Construct query to select records from the named table, optionally sorting
# by a particular column.  Limit output to 50 rows to avoid dumping entire
# contents of large tables.

$query = " SELECT clientmatter,date,time,extention,charge,number,duration,location FROM pbx";
$query .= " WHERE date = '2002-12-24' AND charge > '0' ";
$query .= " ORDER BY $sort_col";
$query .= " LIMIT 50";

$result_id = mysql_query ($query, $link);
if (!$result_id)
    die (htmlspecialchars (mysql_error ($link)));

# Display query results as HTML table.  Use query metadata to get column
# names, and display names in first row of table as hyperlinks that cause
# the table to be redisplayed, sorted by the corresponding table column.

print ("<table border=\"1\">\n");
#@ _HEADER_ROW_
$self_path = get_self_path ();
print ("<tr>\n");
for ($i = 0; $i < mysql_num_fields ($result_id); $i++)
{
    $col_name = mysql_field_name ($result_id, $i);
    printf ("<th><a href=\"%s?sort=%s\">%s</a></th>\n",
                $self_path,
                urlencode ($col_name),
                htmlspecialchars ($col_name));
}
print ("</tr>\n");
#@ _HEADER_ROW_
while ($row = mysql_fetch_row ($result_id))
{
    print ("<tr>\n");
    for ($i = 0; $i < mysql_num_fields ($result_id); $i++)
    {
        # encode values, using   for empty cells
        $val = $row[$i];
        if (isset ($val) && $val != "")
            $val = htmlspecialchars ($val);
        else
            $val = " ";
        printf ("<td>%s</td>\n", $val);
    }
    print ("</tr>\n");
}
mysql_free_result ($result_id);
print ("</table>\n");

mysql_close ($link);

?>


Now the only differences are that the db I'll be using is cd_test, the table is cd_info, and the select statement will be slightly different.

I honestly have no clue about what to change and where to change it. I'm teaching myself and learning by example so far... So if anyone could help it would be much appreciated!
devisevere
New php-forum User
New php-forum User
 
Posts: 2
Joined: Sun Feb 16, 2003 11:11 pm

Postby *JaH* » Mon Feb 17, 2003 6:51 am

first of you need another GET variable in yur tablename-links

create a variable $sortOrder = "DESC" if it does not exist, otherwise the GET sortorder-variable.


You need to place it in yur query:

Code: Select all
$query .= " ORDER BY $sort_col $sortOrder";


You need to place it in yur link, but it needs to be the ASC if it has sorted it DESC, so make a switch for it

Code: Select all
switch($sortOrder) {
case "ASC":  $sortOrder="DESC"; break;
case "DESC":$sortOrder="ASC"; break;
}


then place the new sortOrder in yur link:


Code: Select all
printf ("<th><a href=\"%s?sort=%s&sortorder=$sortOrder\">%s</a></th>\n",
                $self_path,
                urlencode ($col_name),
                htmlspecialchars ($col_name));
*JaH*
New php-forum User
New php-forum User
 
Posts: 80
Joined: Fri Jan 31, 2003 4:18 pm

Postby devisevere » Mon Feb 17, 2003 2:41 pm

Ok, edit, I had a friend work with me on it and this is what we've got so far.

Code: Select all
<?php
   
   /* Connecting, selecting database */
    $link = mysql_connect("dbhost", "dbuser", "dbpass")
        or die("Could not connect: " . mysql_error());
      
   // To read from the database, select it
   mysql_select_db("cd_test") or die("Could not select database");

   # Get the name of the column to sort by (optional).  If missing, use
   # column one.  If present, perform simple validation on column name;
   # it must consist only of alphanumeric or underscore characters.
   
   $sort_col = $_GET["sort"];
   if (is_null($sort_col)) // check to see if the value is null
   $sort_col = "cd_album"; // or whatever you want to call the default column to sort by
   else if (!ereg ("^[0-9a-zA-Z_]+$", $sort_col))
    die (htmlspecialchars ("Column name $sort_col is invalid"));

   $sortOrder = $_GET["sortOrder"];
   if(is_null($sortOrder))
   $sortOrder = "ASC";
   
   # Construct query to select records from the named table, optionally sorting
   # by a particular column.  Limit output to 50 rows to avoid dumping entire
   # contents of large tables.
   
   /* Performing SQL query */
    $query = "SELECT `cd_artist` , `cd_album`
   FROM `cd_info`
   WHERE `cd_artist` = 'Nine Inch Nails'
   ORDER BY $sort_col $sortOrder  LIMIT 0 , 30";
   
   switch($sortOrder) {
   case "ASC":  $sortOrder="DESC"; break;
   case "DESC":$sortOrder="ASC"; break;
   }
   
   echo($sortOrder);

   $result_id = mysql_query ($query, $link);
   if (!$result_id)
    die (htmlspecialchars (mysql_error ($link)));
   
   # Display query results as HTML table.  Use query metadata to get column
   # names, and display names in first row of table as hyperlinks that cause
   # the table to be redisplayed, sorted by the corresponding table column.
   
   print ("<table border=\"1\">\n");
   #@ _HEADER_ROW_
   $self_path = $_SERVER['PHP_SELF'];
   print ("<tr>\n");
   for ($i = 0; $i < mysql_num_fields ($result_id); $i++)
   {
    $col_name = mysql_field_name ($result_id, $i);
    printf ("<th><a href=\"%s?sort=%s&sortorder=$sortOrder\">%s</a></th>\n",
                $self_path,
                urlencode ($col_name),
                htmlspecialchars ($col_name));
   }
   print ("</tr>\n");
   #@ _HEADER_ROW_
   while ($row = mysql_fetch_row ($result_id))
   {
    print ("<tr>\n");
    for ($i = 0; $i < mysql_num_fields ($result_id); $i++)
    {
        # encode values, using   for empty cells
        $val = $row[$i];
        if (isset ($val) && $val != "")
            $val = htmlspecialchars ($val);
        else
            $val = " ";
        printf ("<td>%s</td>\n", $val);
    }
    print ("</tr>\n");
   }
   mysql_free_result ($result_id);
   print ("</table>\n");

   mysql_close ($link);

?>


Now the only problem is when I try to click on a column header to re-sort it, it doesn't...

But at least I'm making some progress! :)
devisevere
New php-forum User
New php-forum User
 
Posts: 2
Joined: Sun Feb 16, 2003 11:11 pm


Return to mySQL & php coding

Who is online

Users browsing this forum: No registered users and 3 guests

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.

cron