users sorting column content alphabetically

headcutter · May 9, 2006

I want the users on my website to be able to sort columns by content (for example clicking on the colums name to sort all of them alphabetically or to sort numbers)

Could anyone recommend any guide or post a sample code? Thanks.

ober · May 9, 2006

There really is no easy way to do this. I think I've done it differently for almost every instance that I needed it. But here are the basics:

1) You have a default search order. If nothing is in the POST/GET array, you sort by some default.
2) Each column is a link like this: <a href="results.php?sortid=10">Column 1</a> where the 1 represents the column and the second number represents the direction (ascending or descending).
3) When the user clicks on a column, you reload the same page but now you have $_GET['sortid'] in the URL. You can split that up and do some if/switch logic to determine what your SQL WHERE clause is going to look like.

That's a quick and dirty version, but hopefully it'll put you on the right track.

Here's code from part of one of my applications:
[code]    $orderstr = "";
    if(!isset($_REQUEST['sortid']))
        $orderstr = "ORDER BY SpellName, Pos ASC";
    if(isset($_REQUEST['sortid']))
    {
        switch(substr($_REQUEST['sortid'], 0, 1))
        {
            case 1:
                $orderstr = "ORDER BY SpellName, Pos";
            break;
            case 2:
                $orderstr = "ORDER BY Pos";
            break;
            case 5:
                $orderstr = "ORDER BY PartNo";
            break;
            case 7:
                $orderstr = "ORDER BY Updated";
            break;
            default:
                $orderstr = "ORDER BY SpellName";
        }
        if(substr($_REQUEST['sortid'], 1, 1) == 1)
            $orderstr .= " DESC";
        else
            $orderstr .= " ASC";
    }[/code]

.josh · May 9, 2006

okay here is some code i made for a game i was making a long time ago. it shows several columns and the you can click on the column name to re-display the list sorted by that column. this code comes complete with pagination. it can actually be cleaned up a lot... like i said, it's kinda old, and i've learned a lot since then. but the point is, is that it works, so it should be a good starting point for you.

hope this helps!

[code]
<?php
          #display columns names as links, where you can sort players by
          $columns ="<font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"2\" color=\"dcc59f\">Click on column name to sort list by that column.</font><br><br>";
          $columns.="<table border=\"0\" width=\"70%\"><tr>";
          $columns.="<td width=\"25%\"><div align=\"left\"><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"2\" color=\"dcc59f\"><a href=\"$PHP_SELF?sortby=userid\">Player</a></font></div></td>";
          $columns.="<td width=\"25%\"><div align=\"left\"><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"2\" color=\"dcc59f\"><a href=\"$PHP_SELF?sortby=battleswon\">Battles Won</a></font></div></td>";
          $columns.="<td width=\"25%\"><div align=\"right\"><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"2\" color=\"dcc59f\"><a href=\"$PHP_SELF?sortby=battleslost\">Battles Lost</a></font></div></td>";
          $columns.="<td width=\"25%\"><div align=\"right\"><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"2\" color=\"dcc59f\"><a href=\"$PHP_SELF?sortby=gold\">Gold</a></font></div></td>";
          $columns.="</tr></table>";
          $columns.="<table border=\"0\" width=\"70%\"><tr><td><hr></td></tr></table>";
          echo($columns);

if(!isset($_GET['page'])){
    $page = 1;
} else {
    $page = $_GET['page'];
}

// Define the number of results per page
$max_results = 10;

// Figure out the limit for the query based
// on the current page number.
$from = (($page * $max_results) - $max_results);

// Perform MySQL query on only the current page number's results; determine how user wants to see rank list
if(!isset($_GET['sortby'])){
    $sortby = "userid";
    $sql = "SELECT * FROM characterinfo ORDER BY $sortby LIMIT $from, $max_results";
}
else {
  $sortby = $_GET['sortby'];
  $sql = "SELECT * FROM characterinfo ORDER BY $sortby ";
    if ($sortby != 'userid'){ $sql.= "DESC "; }
    $sql.="LIMIT $from, $max_results";
}

msyql_query($sql) or die(msyql_error());

echo("<table border=\"0\" width=\"70%\">");
while($rankings = mysql_fetch_array($sql)){
    // Build your formatted results here.
               $listranks="<tr><td width=\"25%\">";
               $listranks.="<div align=\"left\"><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"1\" color=\"dcc59f\"><a href=\"attack.php?targetplayer=".$rankings["username"]."\">".$rankings["username"]."</a></div></font>";
               $listranks.="</td><td width=\"25%\">";
               $listranks.="<div align=\"right\"><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"1\" color=\"dcc59f\">".$rankings["battleswon"]."</font></div>";
               $listranks.="</td><td width=\"25%\">";
               $listranks.="<div align=\"right\"><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"1\" color=\"dcc59f\">".$rankings["battleslost"]."</font></div>";
               $listranks.="</td><td width=\"25%\">";
               $listranks.="<font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"1\" color=\"dcc59f\"><div align=\"right\">".$rankings["gold"]."</div></font>";
               $listranks.="</td></tr>";
               echo($listranks);
       }
echo("</table>");
echo("<table border=\"0\" width=\"70%\"><tr><td><hr></td></tr></table>");
echo("<table border=\"0\" width=\"70%\"><tr><td>");
// Figure out the total number of results in DB:
$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM characterinfo"),0);

// Figure out the total number of pages. Always round up using ceil()
$total_pages = ceil($total_results / $max_results);

// Build Page Number Hyperlinks
echo "<font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"1\" color=\"dcc59f\"><center>Select a Page<br />";

// Build Previous Link
if($page > 1){
    $prev = ($page - 1);
    echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$prev&sortby=$sortby\"><<</a> ";
}

for($i = 1; $i <= $total_pages; $i++){
    if(($page) == $i){
        echo "<font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"1\" color=\"000000\">$i </font>";
        } else {
            echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$i&sortby=$sortby\">$i</a> ";
    }
}

// Build Next Link
if($page < $total_pages){
    $next = ($page + 1);
    echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$next&sortby=$sortby\">>></a>";
}
echo "</center></font>";
echo("</td></tr></table>");
?>
[/code]

headcutter · May 13, 2006

Thanks for the code. I edited it to suit my needs but I get an error:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource on line:
while($rankings = mysql_fetch_array($sql)){

Maybe someone will be able to find the reason for this error. Here is the code:

<?php include ("dbconfig.php") ?>
<?php
#display columns names as links, where you can sort players by
$columns ="<font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"2\" color=\"dcc59f\">Click on column name to sort list by that column.</font><br><br>";
$columns.="<table border=\"0\" width=\"70%\"><tr>";
$columns.="<td width=\"25%\"><div align=\"left\"><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"2\" color=\"dcc59f\"><a href=\"$PHP_SELF?sortby=name\">Name</a></font></div></td>";
$columns.="<td width=\"25%\"><div align=\"left\"><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"2\" color=\"dcc59f\"><a href=\"$PHP_SELF?sortby=name\">Name</a></font></div></td>";
$columns.="<td width=\"25%\"><div align=\"right\"><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"2\" color=\"dcc59f\"><a href=\"$PHP_SELF?sortby=pet\">Pet</a></font></div></td>";
$columns.="<td width=\"25%\"><div align=\"right\"><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"2\" color=\"dcc59f\"><a href=\"$PHP_SELF?sortby=pet\">Pet</a></font></div></td>";
$columns.="</tr></table>";
$columns.="<table border=\"0\" width=\"70%\"><tr><td><hr></td></tr></table>";
echo($columns);

if(!isset($_GET['page'])){
$page = 1;
} else {
$page = $_GET['page'];
}

// Define the number of results per page
$max_results = 100;

// Figure out the limit for the query based
// on the current page number.
$from = (($page * $max_results) - $max_results);

// Perform MySQL query on only the current page number's results; determine how user wants to see rank list
if(!isset($_GET['sortby'])){
$sortby = "name";
$sql = "SELECT * FROM friends ORDER BY $sortby LIMIT $from, $max_results";
}
else {
$sortby = $_GET['sortby'];
$sql = "SELECT * FROM friends ORDER BY $sortby ";
if ($sortby != 'name'){ $sql.= "DESC "; }
$sql.="LIMIT $from, $max_results";
}

mysql_query($sql) or die(mysql_error());

echo("<table border=\"0\" width=\"70%\">");
while($rankings = mysql_fetch_array($sql)){
// Build your formatted results here.
$listranks="<tr><td width=\"25%\">";
$listranks.="<div align=\"left\"><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"1\" color=\"dcc59f\"><a href=\"attack.php?targetplayer=".$rankings["name"]."\">".$rankings["name"]."</a></div></font>";
$listranks.="</td><td width=\"25%\">";
$listranks.="<div align=\"right\"><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"1\" color=\"dcc59f\">".$rankings["name"]."</font></div>";
$listranks.="</td><td width=\"25%\">";
$listranks.="<div align=\"right\"><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"1\" color=\"dcc59f\">".$rankings["pet"]."</font></div>";
$listranks.="</td><td width=\"25%\">";
$listranks.="<font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"1\" color=\"dcc59f\"><div align=\"right\">".$rankings["pet"]."</div></font>";
$listranks.="</td></tr>";
echo($listranks);
}
echo("</table>");
echo("<table border=\"0\" width=\"70%\"><tr><td><hr></td></tr></table>");
echo("<table border=\"0\" width=\"70%\"><tr><td>");
// Figure out the total number of results in DB:
$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM friends"),0);

// Figure out the total number of pages. Always round up using ceil()
$total_pages = ceil($total_results / $max_results);

// Build Page Number Hyperlinks
echo "<font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"1\" color=\"dcc59f\"><center>Select a Page<br />";

// Build Previous Link
if($page > 1){
$prev = ($page - 1);
echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$prev&sortby=$sortby\"><<</a> ";
}

for($i = 1; $i <= $total_pages; $i++){
if(($page) == $i){
echo "<font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"1\" color=\"000000\">$i </font>";
} else {
echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$i&sortby=$sortby\">$i</a> ";
}
}

// Build Next Link
if($page < $total_pages){
$next = ($page + 1);
echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$next&sortby=$sortby\">>></a>";
}
echo "</center></font>";
echo("</td></tr></table>");
?>

suttercain · February 28, 2007

[b]This is the exact same thing I would like to do on my page. [/b]

I have a table with 5 columns, DISP, FUEL, CLASS, EMISSION STD, TEST GROUP and EO NUMBER. I would like the user to be able to click one of the column titles and have it sort in ascending order alphabetically or numerically. If they click the same column title again it will arrange it in descending order.

Here is my code:
[code]<?php
//Connect to the Database via the Include File!
require ('get_connected.php');

// Perform a statndard SQL query:
$res = mysql_query("SELECT UPPER(division) AS division, sub_model, disp, fuel, veh_class, arb_std, test_group, eo FROM 2007ccvl WHERE year = '2007' ORDER BY division, sub_model ASC") or die (mysql_error());

// Convert the Array DIVISION to Full Names
$makes = array('ACUR'=>'ACURA',
'ALP'=>'ALPINA',
'ASMA'=>'ASTON MARTIN LAGONDA',
'AUDI'=>'AUDI',
'BAF'=>'FORD BAF CNG',
'BAYT'=>'GENERAL MOTORS BAYTECH CNG',
'BENT'=>'BENTLY',
'BMW'=>'BMW',
'BUIC'=>'BUICK',
'CAD'=>'CADILLAC',
'CHEV'=>'CHEVROLET',
'CHRS'=>'CHRYSLER',
'CUMM'=>'CUMMINS',
'DODG'=>'DODGE',
'FERI'=>'FERARAI',
'FORD'=>'FORD',
'GMC'=>'GMC',
'HOND'=>'HONDA',
'HUMM'=>'HUMMER',
'HYND'=>'HYUNDAI',
'INFI'=>'INFINITI',
'ISUZ'=>'ISUZU',
'JAG'=>'JAGUAR',
'JEEP'=>'JEEP',
'KI'=>'KIA',
'LAMB'=>'LABORGHINI',
'LAND'=>'LAND ROVER',
'LEXS'=>'LEXUS',
'LINC'=>'LINCOLN',
'LOTU'=>'LOTUS',
'MASE'=>'MASERATI',
'MAZD'=>'MAZDA',
'MBZ'=>'MERCEDES BENZ',
'MINI'=>'MINI',
'MITS'=>'MITSUBISHI',
'MORG'=>'MORGAN',
'MRCY'=>'MERCURY',
'NISS'=>'NISSAN',
'PONT'=>'PONTIAC',
'PORS'=>'PORSCHE',
'RPP'=>'ROUSCH',
'RR'=>'ROLLS ROYCE',
'SAAB'=>'SAAB',
'SALE'=>'SALEEN',
'SATU'=>'SATURN',
'SHLB'=>'SHELBY',
'SUBA'=>'SUBARU',
'SUZU'=>'SUZUKI',
'TOTA'=>'TOYOTA',
'VOLK'=>'VOLKSWAGEN',
'VOLV'=>'VOLVO',
);
print $makes[$row['division']];

// Convert the Array FUEL to FUEL TYPE
$fuel_types = array('GN11'=>'GASOLINE',
'NN01'=>'CNG',
'DC03'=>'DIESEL');
print $fuel_types[$row['fuel']];

//CSS for the Alternating Color Rows
$class = 'even';

//Convert DIVISION Column to out put from the MySQL instead of HTML
$current = '';
while($row = mysql_fetch_array($res)) {
if($current != $row['division']) {
$current = $row['division'];
echo "<table width='100%' border='0' bgcolor=#CCCCCC cellpadding='2' cellspacing='1'>
<tr>
<td width='135'><font size=1.5><b>$makes[$current]</b></font></td> <--COLUMN TITLES
<td width='55'><font size=1.5><b>DISP</b></font></td>
<td width='60'><font size=1.5><b>FUEL</b></font></td>
<td width='55'><font size=1.5><b>CLASS</b></font></td>
<td width='110'><font size=1.5><b>EMISSION STD</b></font></td>
<td width='90'><font size=1.5><b>TEST GROUP</b></font></td>
<td width='90'><font size=1.5><b>EO NUMBER</b></font></td>
</tr>
<br>";
}

//Continuation of CSS for the Alternating Color Rows
$class = $class == 'even' ? 'odd' : 'even';

//Trying to make it Uppercase

//Populate the Tables from the Database
echo "<tr class=\"$class\">\n";
echo strtoupper("<td width='135'><font size=1.5>$row[sub_model]</font></td>\n"); <--ECHOED DATA
echo "<td width='55'><font size=1.5>".sprintf( '%01.1f',$row[disp])." L</font></td>\n";
echo "<td width='60'><font size=1.5>".$fuel_types[$row['fuel']]."</font></td>\n";
echo "<td width='55'><font size=1.5>$row[veh_class]</font></td>\n";
echo "<td width='110'><font size=1.5>$row[arb_std]</font></td>\n";
echo "<td width='90'><font size=1.5>$row[test_group]</font></td>\n";
echo "<td width='90'><font size=1.5>$row[eo]</font></td>\n";
echo "</tr>\n";
}
?>[/code]

Can anyone suggest anything?

Thank you.

boo_lolly · February 28, 2007

this is simply an example, but you should be able to get the idea. if not just ask what you are confused about.
[code]
<?php
//Connect to the Database via the Include File!
require ('get_connected.php');

/*set orderby var*/
if(isset($_GET['orderBy'])){
if($_GET['orderBy'] == "ASC"){
$orderBy = "DESC";
}
if($_GET['orderBy'] == "DESC"){
$orderBy = "ASC";
}
}

// Perform a statndard SQL query:
$res = mysql_query("SELECT UPPER(division) AS division, sub_model, disp, fuel, veh_class, arb_std, test_group, eo
FROM 2007ccvl WHERE year = '2007' ". ((isset($_GET['orderBy']) ? ("ORDER BY ". $_GET['col'] ." ". $orderBy ."") : ("")))
OR die (mysql_error());

// Convert the Array DIVISION to Full Names
$makes = array('ACUR'=>'ACURA',
'ALP'=>'ALPINA',
'ASMA'=>'ASTON MARTIN LAGONDA',
'AUDI'=>'AUDI',
'BAF'=>'FORD BAF CNG',
'BAYT'=>'GENERAL MOTORS BAYTECH CNG',
'BENT'=>'BENTLY',
'BMW'=>'BMW',
'BUIC'=>'BUICK',
'CAD'=>'CADILLAC',
'CHEV'=>'CHEVROLET',
'CHRS'=>'CHRYSLER',
'CUMM'=>'CUMMINS',
'DODG'=>'DODGE',
'FERI'=>'FERARAI',
'FORD'=>'FORD',
'GMC'=>'GMC',
'HOND'=>'HONDA',
'HUMM'=>'HUMMER',
'HYND'=>'HYUNDAI',
'INFI'=>'INFINITI',
'ISUZ'=>'ISUZU',
'JAG'=>'JAGUAR',
'JEEP'=>'JEEP',
'KI'=>'KIA',
'LAMB'=>'LABORGHINI',
'LAND'=>'LAND ROVER',
'LEXS'=>'LEXUS',
'LINC'=>'LINCOLN',
'LOTU'=>'LOTUS',
'MASE'=>'MASERATI',
'MAZD'=>'MAZDA',
'MBZ'=>'MERCEDES BENZ',
'MINI'=>'MINI',
'MITS'=>'MITSUBISHI',
'MORG'=>'MORGAN',
'MRCY'=>'MERCURY',
'NISS'=>'NISSAN',
'PONT'=>'PONTIAC',
'PORS'=>'PORSCHE',
'RPP'=>'ROUSCH',
'RR'=>'ROLLS ROYCE',
'SAAB'=>'SAAB',
'SALE'=>'SALEEN',
'SATU'=>'SATURN',
'SHLB'=>'SHELBY',
'SUBA'=>'SUBARU',
'SUZU'=>'SUZUKI',
'TOTA'=>'TOYOTA',
'VOLK'=>'VOLKSWAGEN',
'VOLV'=>'VOLVO',
);
print $makes[$row['division']];

// Convert the Array FUEL to FUEL TYPE
$fuel_types = array('GN11'=>'GASOLINE',
'NN01'=>'CNG',
'DC03'=>'DIESEL');
print $fuel_types[$row['fuel']];

//CSS for the Alternating Color Rows
$class = 'even';

//Convert DIVISION Column to out put from the MySQL instead of HTML
$current = '';
while($row = mysql_fetch_array($res)) {
if($current != $row['division']) {
$current = $row['division'];
echo "<table width='100%' border='0' bgcolor=#CCCCCC cellpadding='2' cellspacing='1'>
<tr>
<td width='135'><font size=1.5><b>$makes[$current]</b></font></td> <--COLUMN TITLES
<td width='55'><font size=1.5><b><a href="<?php echo $_SERVER['php_self'] ."col=disp&orderBy=". $orderBy ."\">"; ?>DISP</a></b></font></td>
<td width='60'><font size=1.5><b><?php echo $_SERVER['php_self'] ."col=fuel&orderBy=". $orderBy ."\">"; ?>FUEL</a></b></font></td>
<td width='55'><font size=1.5><b><?php echo $_SERVER['php_self'] ."col=class&orderBy=". $orderBy ."\">"; ?>CLASS</a></b></font></td>
<td width='110'><font size=1.5><b><?php echo $_SERVER['php_self'] ."col=emission&orderBy=". $orderBy ."\">"; ?>EMISSION STD</a></b></font></td>
<td width='90'><font size=1.5><b><?php echo $_SERVER['php_self'] ."col=testgroup&orderBy=". $orderBy ."\">"; ?>TEST GROUP</a></b></font></td>
<td width='90'><font size=1.5><b><?php echo $_SERVER['php_self'] ."col=eoNum&orderBy=". $orderBy ."\">"; ?>EO NUMBER</a></b></font></td>
</tr>
<br>";
}

//Continuation of CSS for the Alternating Color Rows
$class = $class == 'even' ? 'odd' : 'even';

//Trying to make it Uppercase

//Populate the Tables from the Database
echo "<tr class=\"$class\">\n";
echo strtoupper("<td width='135'><font size=1.5>$row[sub_model]</font></td>\n"); <--ECHOED DATA
echo "<td width='55'><font size=1.5>".sprintf( '%01.1f',$row[disp])." L</font></td>\n";
echo "<td width='60'><font size=1.5>".$fuel_types[$row['fuel']]."</font></td>\n";
echo "<td width='55'><font size=1.5>$row[veh_class]</font></td>\n";
echo "<td width='110'><font size=1.5>$row[arb_std]</font></td>\n";
echo "<td width='90'><font size=1.5>$row[test_group]</font></td>\n";
echo "<td width='90'><font size=1.5>$row[eo]</font></td>\n";
echo "</tr>\n";
}
?>
[/code]

boo_lolly · February 28, 2007

EDIT: i forgot to add <a href=" before each <?php echo $_SERVER['php_self'] ........ ?>

Sign In

users sorting column content alphabetically

Recommended Posts

headcutter

Link to comment

Share on other sites

ober

Link to comment

Share on other sites

.josh

Link to comment

Share on other sites

headcutter

Link to comment

Share on other sites

suttercain

Link to comment

Share on other sites

boo_lolly

Link to comment

Share on other sites

boo_lolly

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information