Jump to content

users sorting column content alphabetically

headcutter

By headcutter
in PHP Coding Help

 Share

Recommended Posts

ober Advanced Member

ober

Posted
Posted
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]
Link to comment
Share on other sites
.josh Advanced Member

.josh

Posted
Posted
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]
Link to comment
Share on other sites
headcutter Newbie

headcutter

Posted
  • Author
Posted
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>");
?>
Link to comment
Share on other sites
  • 9 months later...
suttercain Advanced Member

suttercain

Posted
Posted
[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.
Link to comment
Share on other sites
boo_lolly Regular Member

boo_lolly

Posted
Posted
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]
Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.