Jump to content

Allow a User to Custom Sort SQL Results on a PHP Page


eRott

Recommended Posts

Hey,

 

Ok, i am having a bit of trouble and and hoping someone will be able to help me out. I've checked google and php.net but I have not been able to find my answer. What I am looking to do is allow a user to sort the displayed database information by their choice. I am looking to allow the user to be able to sort the results by the results table column headings (which in this case i used images--but that's not important). I have found some examples with ASP pages, but none for PHP pages. Anyone have an idea how to do this?

 

Thank you.

 

Here's the code:

<?php

echo "
<table style=\"border: 1px solid #606060;\" width=\"100%\" cellspacing=\"0\" cellpadding=\"0\"><tr class=\"rosterTITLE\">
<td align=\"left\"><a href=\"roster.php?s=member\"><img src=\"lib/roster/images/member.jpg\" border=\"0\"></a></td>
<td align=\"right\"><a href=\"roster.php?s=race\"><img src=\"lib/roster/images/race.jpg\" border=\"0\"></a></td>
<td align=\"left\"><a href=\"roster.php?s=class\"><img src=\"lib/roster/images/class.jpg\" border=\"0\"></a></td>
<td align=\"center\"><a href=\"roster.php?s=level\"><img src=\"lib/roster/images/level.jpg\" border=\"0\"></a></td>
<td align=\"center\"><a href=\"roster.php?s=server\"><img src=\"lib/roster/images/server.jpg\" border=\"0\"></a></td>
<td align=\"right\"><a href=\"roster.php?s=faction\"><img src=\"lib/roster/images/faction.jpg\" border=\"0\"></a></td></tr>
";

//connect to and select db
include 'lib/roster/config.php';
include 'lib/roster/opendb.php';

// If current page number, use it
// if not, set one!

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

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

// 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

$sql = mysql_query("SELECT * FROM members ORDER BY name ASC LIMIT $from, $max_results");


while($row = mysql_fetch_array($sql)){
// formatted results here.
echo "
<tr bgcolor='#181818'>
<td align='left' style='padding-left: 5px;'><a href='#'>{$row['name']}</a></td>
<td align='right'><img src='lib/roster/images/races/{$row['race']}_{$row['sex']}.png' border='0' alt='{$row['race']}' title='{$row['race']}'></td>
<td align='left'><img src='lib/roster/images/classes/{$row['class']}.gif' border='0' alt='{$row['class']}' title='{$row['class']}'></td>
<td align='center'>{$row['level']}</td>
<td align='center'>{$row['server']}</td>
<td align='right' class='rosterLEVEL'><img src='lib/roster/images/{$row['faction']}.gif' border='0' alt='{$row['faction']}' title='{$row['faction']}'></td></tr>
";
}

// Figure out the total number of results in DB:
$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM members"),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 "<small><b>Page:</b></small> ";

// Build Previous Link
if($page > 1){
    $prev = ($page - 1);
    echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$prev\"><img src=\"images/left.gif\" border=\"0\" valign=\"middle\"></a> ";
}

for($i = 1; $i <= $total_pages; $i++){
    if(($page) == $i){
        echo "<b>$i</b> ";
        } else {
            echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$i\">$i</a> ";
    }
}

// Build Next Link
if($page < $total_pages){
    $next = ($page + 1);
    echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$next\"><img src=\"images/right.gif\" border=\"0\" valign=\"middle\"></a>";
}
echo "</center>";

include 'lib/roster/closedb.php';
?>

 

EDIT:

 

Ok, i've managed to run across a post on this forum which contained some code i think will help, but I just need some clarification on how it works. What exactly is ($_GET['form']=='r') referring to? Specifically the ['form'] part?

 

echo " Choose <a href='?form=r'> Registration Form</a> or  <a href='?form=c'>Contact Form</a>" ;

if ($_GET['form']=='r')
    display_registration_form();
elseif ($_GET['form']=='c') 
    display_contact_form('') ;

Link to comment
Share on other sites

Hmm. Well, that does make the code a little cleaner, but how would I set the default query? That would only work until someone clicks one of the headings. I've tried this, but it doesn't seem to work:

 

$s = ("name");
$sql = mysql_query("SELECT * FROM members ORDER BY {$_GET['s']} ASC LIMIT $from, $max_results");

//and a link would like like this
<a href=\"roster.php?s=name\">

Link to comment
Share on other sites

Ok, I think i will just stick with the if, else statements, however, I have a new question now. When the user clicks a header say "name", how do you get it to keep alternating sorting style? For example, click it once and it orders is by ASC, click it again and it orders it by DESC. I could probably accomplish this using a lot of if and else statements, but does anyone know of a better solution?

 

Thanks

 

<?php

echo "... <a href=\"roster.php?s=member\"><img src=\"lib/roster/images/member.jpg\" border=\"0\"></a> ...";

...

// Perform MySQL query on only the current page number's results
$sql = mysql_query("SELECT * FROM members LIMIT $from, $max_results");

if ($_GET['s']=='member') {
$sql = mysql_query("SELECT * FROM members ORDER BY name ASC LIMIT $from, $max_results");
} elseif ($_GET['s']=='race') {
$sql = mysql_query("SELECT * FROM members ORDER BY race ASC LIMIT $from, $max_results");
} elseif ($_GET['s']=='class') {
$sql = mysql_query("SELECT * FROM members ORDER BY class ASC LIMIT $from, $max_results");
} elseif ($_GET['s']=='level') {
$sql = mysql_query("SELECT * FROM members ORDER BY level ASC LIMIT $from, $max_results");
} elseif ($_GET['s']=='server') {
$sql = mysql_query("SELECT * FROM members ORDER BY server ASC LIMIT $from, $max_results");
} elseif ($_GET['s']=='faction') {
$sql = mysql_query("SELECT * FROM members ORDER BY faction ASC LIMIT $from, $max_results");
}

...
?>

Link to comment
Share on other sites

You can eliminate this huge chunk of code:

<?php

if ($_GET['s']=='member') {
$sql = mysql_query("SELECT * FROM members ORDER BY name ASC LIMIT $from, $max_results");
} elseif ($_GET['s']=='race') {
$sql = mysql_query("SELECT * FROM members ORDER BY race ASC LIMIT $from, $max_results");
} elseif ($_GET['s']=='class') {
$sql = mysql_query("SELECT * FROM members ORDER BY class ASC LIMIT $from, $max_results");
} elseif ($_GET['s']=='level') {
$sql = mysql_query("SELECT * FROM members ORDER BY level ASC LIMIT $from, $max_results");
} elseif ($_GET['s']=='server') {
$sql = mysql_query("SELECT * FROM members ORDER BY server ASC LIMIT $from, $max_results");
} elseif ($_GET['s']=='faction') {
$sql = mysql_query("SELECT * FROM members ORDER BY faction ASC LIMIT $from, $max_results");
}

?>

 

By simply replacing it with this:

$sql = mysql_query("SELECT * FROM members ORDER BY {$_GET['s']} ASC LIMIT $from, $max_results");

 

Link to comment
Share on other sites

Guess you missed the previous responses:

 

Hmm. Well, that does make the code a little cleaner, but how would I set the default query? That would only work until someone clicks one of the headings. I've tried this, but it doesn't seem to work:

 

$s = ("name");
$sql = mysql_query("SELECT * FROM members ORDER BY {$_GET['s']} ASC LIMIT $from, $max_results");

//and a link would like like this
<a href=\"roster.php?s=name\">

 

How would i go about setting the default value for 's' ?

Link to comment
Share on other sites

I'm sorry...I just don't understand what your trying to do. I read through all the posts and still don't get it. I know your trying to sort the results according to what the user chooses...but that has already been answered. I guess I just don't understand your most recent question.

 

"How would i go about setting the default value for 's' ?"

 

What would the default value be, and as opposed to what?

Link to comment
Share on other sites

Guest prozente

pocobueno1388, that is not good coding as it allows SQL injection. This is better since it checks the value first before using it

 

if(isset($_GET['s']) && preg_match('/^(?:member|race|class|level|server|faction)\z/', $_GET['s'])){

   $sql = mysql_query('SELECT * FROM members ORDER BY '.$_GET['s'].' '.(isset($_GET['dir']) && $_GET['dir'] == 'desc' ? 'desc' : 'asc' ).' LIMIT '.$from.', '.$max_results);

}else{

   die('invalid selection');

}

eRott, $_GET['s'] would be set in the url, for example having a link like

 

<a href="?s=class">Sort by Class</a>

Link to comment
Share on other sites

Sorry. I guess I am being a bit confusing. Ok, what I mean by setting the default value of s, is that in your suggestion of changing the query to:

 

$sql = mysql_query("SELECT * FROM members ORDER BY {$_GET['s']} ASC LIMIT $from, $max_results");

 

There is no initial or default value for s. So, because of that, no results will be displayed as soon as the page loads, until a value for s is set (when a user clicks one of the header titles). My question is, how do you set an initial value for s, so that the results will be displayed even if the user does not click a header titles. Hope I clarified that part.

 

As for the second question, what I am wondering, is how I would go about alternating the display order? As you know, you can display results from a-z or z-a. So, lets say a user sorts by name and it sorts from a-z. How would i get it to sort by z-a if they click the header title again?

 

I was thinking of using something along the lines of this, but it doesn't work as i want it to because the link always contains the value of ASC..

 

<?php

...

<a href=\"roster.php?s=member&d=ASC\"><img src=\"lib/roster/images/member.jpg\" border=\"0\"></a>

...

if ($_GET['s']=='member') {
$sql = mysql_query("SELECT * FROM members ORDER BY name ASC LIMIT $from, $max_results");
} elseif (($_GET['s']=='member') && ($_GET['d']=='ASC')) { {
$sql = mysql_query("SELECT * FROM members ORDER BY name DESC LIMIT $from, $max_results");
}

...

?>

 

In other words, how can i make the headers sortable so that if you click on them, the values are sorted ASC if its already DESC and vice versa?

Link to comment
Share on other sites

Well, I've finally figured everything out. Thank you all for your help. I appreciate it. And for future reference if anyone ever runs into this problem, here's the code (summary of script: pagination and header sort):

 

<?php

$strOrder = $_GET["d"] == 'desc' ? 'asc' : 'desc';

echo "<table style=\"border: 1px solid #606060;\" width=\"100%\" cellspacing=\"0\" cellpadding=\"0\"><tr class=\"rosterTITLE\"><td align=\"left\"><a href=\"roster.php?s=name&d=$strOrder\"><img src=\"lib/roster/images/member.jpg\" border=\"0\"></a></td><td align=\"right\"><a href=\"roster.php?s=race&d=$strOrder\"><img src=\"lib/roster/images/race.jpg\" border=\"0\"></a></td><td align=\"left\"><a href=\"roster.php?s=class&d=$strOrder\"><img src=\"lib/roster/images/class.jpg\" border=\"0\"></a></td><td align=\"center\"><a href=\"roster.php?s=level&d=$strOrder\"><img src=\"lib/roster/images/level.jpg\" border=\"0\"></a></td><td align=\"center\"><a href=\"roster.php?s=server&d=$strOrder\"><img src=\"lib/roster/images/server.jpg\" border=\"0\"></a></td><td align=\"right\"><a href=\"roster.php?s=faction&d=$strOrder\"><img src=\"lib/roster/images/faction.jpg\" border=\"0\"></a></td></tr>";

//connect to and select db
include 'lib/roster/config.php';
include 'lib/roster/opendb.php';

// If current page number, use it
// if not, set one!

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

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

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

if( $_GET['d'] == 'desc' )
{
    $strOrder = 'asc';
}
else
{
    $strOrder = 'desc';
}

if ((empty($_GET['s'])) && (empty($_GET['d']))) {
$sql = mysql_query('SELECT * FROM members LIMIT '.$from.', '.$max_results);
} elseif ((!empty($_GET['s'])) && (!empty($_GET['d']))) {
if(isset($_GET['s']) && preg_match('/^(?:name|race|class|level|server|faction)\z/', $_GET['s'])){
$sql = mysql_query('SELECT * FROM members ORDER BY '.$_GET['s'].' '.$_GET['d'].' LIMIT '.$from.', '.$max_results);
}else{
die('invalid selection');
}
}

while($row = mysql_fetch_array($sql)){
// Build your formatted results here.
echo "<tr bgcolor='#181818'><td align='left' style='padding-left: 5px;'><a href='#'>{$row['name']}</a></td><td align='right'><img src='lib/roster/images/races/{$row['race']}_{$row['sex']}.png' border='0' alt='{$row['race']}' title='{$row['race']}'></td><td align='left'><img src='lib/roster/images/classes/{$row['class']}.gif' border='0' alt='{$row['class']}' title='{$row['class']}'></td><td align='center'>{$row['level']}</td><td align='center'>{$row['server']}</td><td align='right' class='rosterLEVEL'><img src='lib/roster/images/{$row['faction']}.gif' border='0' alt='{$row['faction']}' title='{$row['faction']}'></td></tr>";
}

// Figure out the total number of results in DB:
$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM members"),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 "<small><b>Page:</b></small> ";

// Build Previous Link
if($page > 1){
    $prev = ($page - 1);
    echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$prev\"><img src=\"images/left.gif\" border=\"0\" valign=\"middle\"></a> ";
}

for($i = 1; $i <= $total_pages; $i++){
    if(($page) == $i){
        echo "<b>$i</b> ";
        } else {
            echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$i\">$i</a> ";
    }
}

// Build Next Link
if($page < $total_pages){
    $next = ($page + 1);
    echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$next\"><img src=\"images/right.gif\" border=\"0\" valign=\"middle\"></a>";
}
echo "</center>";

include 'lib/roster/closedb.php';
?>

Link to comment
Share on other sites

I'm glad you found a solution.  :)

 

If you're still looking to make your code more efficient, here is another method you can try.

 

The first thing you'll want to do is create a list (array) of the order($s) and sort($d) values that are allowed to be passed to your script. If an invalid value is passed, or a value is not present, you'll use a default value from the list. Add this to the top of your code:

 

// Create an array of allowed order($s_array) and sort($d_array) methods
$s_array = array("member","race","class","level","server","faction");
$d_array = array("asc","desc");

// Check if the order($_GET['s']) and sort($_GET['d'] variables were set in the URL.
// Otherwise use a default value( first array element )
$s = (isset($_GET's') && in_array($_GET['s'], $s_array)) ? $_GET['s'] : $s_array[0];
$s = (isset($_GET'd') && in_array($_GET['d'], $d_array)) ? $_GET['d'] : $d_array[0];

 

... next, we'll create the links for each header from the same arrays using a loop. In each loop, we'll check if the link we are creating is

the currently used order and sort value, and change them appropriately. Therefor, if we are ordering by "member" and sorting "ASC", then we'll

change the "member" link to sort "DESC". This will allow us to keep all other links set to "ASC" by default when they are first clicked ...

 

.. with that said, replace this ...

 

echo "
<table style=\"border: 1px solid #606060;\" width=\"100%\" cellspacing=\"0\" cellpadding=\"0\"><tr class=\"rosterTITLE\">
<td align=\"left\"><a href=\"roster.php?s=member\"><img src=\"lib/roster/images/member.jpg\" border=\"0\"></a></td>
<td align=\"right\"><a href=\"roster.php?s=race\"><img src=\"lib/roster/images/race.jpg\" border=\"0\"></a></td>
<td align=\"left\"><a href=\"roster.php?s=class\"><img src=\"lib/roster/images/class.jpg\" border=\"0\"></a></td>
<td align=\"center\"><a href=\"roster.php?s=level\"><img src=\"lib/roster/images/level.jpg\" border=\"0\"></a></td>
<td align=\"center\"><a href=\"roster.php?s=server\"><img src=\"lib/roster/images/server.jpg\" border=\"0\"></a></td>
<td align=\"right\"><a href=\"roster.php?s=faction\"><img src=\"lib/roster/images/faction.jpg\" border=\"0\"></a></td></tr>
";

 

... with this ...

 

echo '
<table style="border: 1px solid #606060;" width="100%" cellspacing="0" cellpadding="0"><tr class="rosterTITLE">';

foreach($s_array as $s_item){
// If the order($s) equals the link we are printing out, and we're sorting ASC, then switch to DESC. Otherwise, use ASC
$d_item = ($s_item == $s && $d == $d_array[0]) ? "desc" : "asc";
echo '<td align="left"><a href="roster.php?s='.$s_item.'&d='.$d_item.'"><img src="lib/roster/images/'.$s_item.'.jpg" border="0"></a></td>';
}

echo '</tr>';

 

... Last, we'll update your SQL query to take advantage of the order and sort variable we set earlier. That way you only need one query, and it

will change dynamically when these variables change ...

 

... replace this ...

 

$sql = mysql_query("SELECT * FROM members ORDER BY name ASC LIMIT $from, $max_results");

 

... with this ...

 

$sql = mysql_query("SELECT * FROM members ORDER BY $s $d LIMIT $from, $max_results");

 

... and you should be all set ...

 

=-D

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.

×
×
  • 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.