eRott Posted April 15, 2007 Share Posted April 15, 2007 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('') ; Quote Link to comment https://forums.phpfreaks.com/topic/47061-allow-a-user-to-custom-sort-sql-results-on-a-php-page/ Share on other sites More sharing options...
MadTechie Posted April 15, 2007 Share Posted April 15, 2007 what about this in your SQL statment "SELECT * FROM Table ORDER BY {$_GET['Field']} ASC" Quote Link to comment https://forums.phpfreaks.com/topic/47061-allow-a-user-to-custom-sort-sql-results-on-a-php-page/#findComment-229539 Share on other sites More sharing options...
eRott Posted April 15, 2007 Author Share Posted April 15, 2007 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\"> Quote Link to comment https://forums.phpfreaks.com/topic/47061-allow-a-user-to-custom-sort-sql-results-on-a-php-page/#findComment-229544 Share on other sites More sharing options...
eRott Posted April 15, 2007 Author Share Posted April 15, 2007 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"); } ... ?> Quote Link to comment https://forums.phpfreaks.com/topic/47061-allow-a-user-to-custom-sort-sql-results-on-a-php-page/#findComment-229548 Share on other sites More sharing options...
pocobueno1388 Posted April 15, 2007 Share Posted April 15, 2007 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"); Quote Link to comment https://forums.phpfreaks.com/topic/47061-allow-a-user-to-custom-sort-sql-results-on-a-php-page/#findComment-229550 Share on other sites More sharing options...
eRott Posted April 15, 2007 Author Share Posted April 15, 2007 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' ? Quote Link to comment https://forums.phpfreaks.com/topic/47061-allow-a-user-to-custom-sort-sql-results-on-a-php-page/#findComment-229553 Share on other sites More sharing options...
pocobueno1388 Posted April 15, 2007 Share Posted April 15, 2007 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? Quote Link to comment https://forums.phpfreaks.com/topic/47061-allow-a-user-to-custom-sort-sql-results-on-a-php-page/#findComment-229556 Share on other sites More sharing options...
Guest prozente Posted April 15, 2007 Share Posted April 15, 2007 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> Quote Link to comment https://forums.phpfreaks.com/topic/47061-allow-a-user-to-custom-sort-sql-results-on-a-php-page/#findComment-229558 Share on other sites More sharing options...
eRott Posted April 15, 2007 Author Share Posted April 15, 2007 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? Quote Link to comment https://forums.phpfreaks.com/topic/47061-allow-a-user-to-custom-sort-sql-results-on-a-php-page/#findComment-229560 Share on other sites More sharing options...
eRott Posted April 15, 2007 Author Share Posted April 15, 2007 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'; ?> Quote Link to comment https://forums.phpfreaks.com/topic/47061-allow-a-user-to-custom-sort-sql-results-on-a-php-page/#findComment-229568 Share on other sites More sharing options...
HeyRay2 Posted April 15, 2007 Share Posted April 15, 2007 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 Quote Link to comment https://forums.phpfreaks.com/topic/47061-allow-a-user-to-custom-sort-sql-results-on-a-php-page/#findComment-229569 Share on other sites More sharing options...
Barand Posted April 15, 2007 Share Posted April 15, 2007 Another technique is to store the sort column name and ASC/DESC as session variables. If the new sort column is the same as the stored name (the user clicked the same sort selection a second time) toggle the ASC/DESC setting Quote Link to comment https://forums.phpfreaks.com/topic/47061-allow-a-user-to-custom-sort-sql-results-on-a-php-page/#findComment-229591 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.