1. Home
  2. Forums Index
  3. Server Side / PHP Server Side Scripting 11:39 am May 1, 2026

Forum Moderators: coopster

Message Too Old, No Replies

Sorting options for MySQL table via Select List

My biggest headache ever!

         

proteus9

3:26 am on Nov 21, 2008 (gmt 0)

10+ Year Member



Hello all. This is my first time posting here. I hope somebody can help me out with this little problem I'm having with this page I'm trying to get together. It displays results from a MySQL table and sorts the results by 'title_name' by default. I would like to give my users the option to refine the sorting by either 'title_mpaa' or 'title_format'. I would like to give them a further option to view by descending or ascending order as well.
I've been playing with this for some time now and all I seem to accomplish doing is giving myself a headache.
Here's what I have already. 


<?php
require_once "../../maincore.php";
require_once THEMES."templates/header.php";
include INFUSIONS."movie_collection/infusion_db.php";
if (file_exists(INFUSIONS."movie_collection/locale/".$settings['locale'].".php")) 
{
include INFUSIONS."movie_collection/locale/".$settings['locale'].".php";
} 
else {
include INFUSIONS."movie_collection/locale/English.php";
}
 
$rowmax = 40;
$id = "title_id";
$name = "title_name";
$mpaa = "title_mpaa";
$date = "title_date";
$length = "title_length";
$format = "title_format";
$url = "title_url";
$asc = "ASC";
$desc = "DESC";
$temp = Array ("$name","$mpaa","$format");
$null = "";
opentable($locale['MMDb_101']);
if (iMEMBER) 
{
if (!isset($_GET['sortby']) ¦¦ !preg_match("/^[0-9A-Z]$/", $_GET['sortby'])) $_GET['sortby']= "all";
$orderby = ($_GET['sortby'] == "all" ? "" : " WHERE $temp[0] LIKE '".stripinput($_GET['sortby'])."%'");
$result = dbquery("SELECT * FROM ".$db_prefix."movies".$orderby."");
$rows = dbrows($result);
$moviecount = dbquery("SELECT count($id) FROM ".$db_prefix."movies");
if (!isset($_GET['rowstart']) ¦¦ !isNum($_GET['rowstart'])) $_GET['rowstart'] = 0;
if ($rows != 0) 
{
 echo "<table width='100%' cellspacing='20' cellpadding='10' class='textbox'><tr><td valign='top' colspan='2' align='center'><span class='tbl' padding='10px'>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;".$locale['MMDb_102']."\n"; 
 echo "<B>".dbresult($moviecount, 0)."</B> ".$locale['MMDb_103']."&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span\n";
// SELECT LIST START
$search = array("1","2","3","4","5","6","7","8","9","A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R", "S","T","U","V","W","X","Y","Z","0");
 // $i=0;$i < 36!="";$i++;
 echo "<TABLE width='100%'><TR><TD width='20%' align='left' valign='top'><IMG 
 SRC='images/clapboard.png' border='0' ALT=''>";
 
 echo "</TD><TD>
 <form name='MyForm' action='index.php' method='post' style='display:inline;'>
 <div style='text-align:right; float:right; width:70%'><fieldset class='textbox'>
 <legend class='textbox'>&nbsp;Browse Options&nbsp;</legend><BR>
 <div align='left' style='margin-left: 43px;'><B><U>View By:</U></B></div>
 <BR><label for='".$name."'>Name:</label>
 <select name='".$name."' class='textbox' style='width: 125px; margin-top: -3px;' onChange=\"if(document.MyForm.title_name.selectedIndex != 0) document.location = '".FUSION_SELF."?sortby=' + document.MyForm.title_name.options[document.MyForm.title_name.selectedIndex].value\">
 <option name='".$name."' value='".$search."'>Choose...</option>
 <option name='".$name."' value='".FUSION_SELF."?sortby=all' >&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<B>All</B></option>";
for ($i=0;$i < 36!="";$i++) 
{
echo "<option name='".$name."' value='".$search[$i]."'> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;".$search[$i]."</option>\n";
}
echo "</select>&nbsp;&nbsp;&nbsp;<BR>";
$search = array("DVD","HD-DVD","Blu-Ray");
$i=0;$i < 3!="";$i++;
 echo "<label for='".$format."'>Format:</label>
 <select name='".$format."' class='textbox' style='width: 125px; margin-top: 3px;' onChange=\"if(document.MyForm.title_format.selectedIndex != 0) document.location = '".FUSION_SELF."?sortby=' + document.MyForm.title_format.options[document.MyForm.title_format.selectedIndex].value\"><option name='".$name."' value='".$search."'>Choose...</option>
 <option name='".$format."' value='".FUSION_SELF."?sortby=".$format."'> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;All</option>";
for ($i=0;$i < 3!="";$i++) 
{
echo "<option name='".$format."' value='".$search[$i]."'> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;".$search[$i]."</option>\n";
}
echo "</select>&nbsp;&nbsp;&nbsp;";
$search = array("G","PG","PG-13", "R", "NC-17", "X", "TV-PG", "TV-14", "TV-MA", "NR");
 $i=0;$i < 10!="";$i++;
echo "<br /><label for='".$mpaa."'>Rating:</label>
 <select name='".$mpaa."' class='textbox' style='width: 125px; margin-top: 3px;' onChange=\"if(document.MyForm.title_mpaa.selectedIndex != 0) document.location = '".FUSION_SELF."?sortby=' + document.MyForm.title_mpaa.options[document.MyForm.title_mpaa.selectedIndex].value\"><option name='".$name."' value='".$search."'>Choose...</option>
 <option name='".$mpaa."' value='".FUSION_SELF."?sortby=".$mpaa."'> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;All</option>";
 
 for ($i=0;$i < 10!="";$i++) 
{
echo "<option name='".$mpaa."' value='".$search[$i]."'> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;".$search[$i]."</option>\n";
}
echo "</select>&nbsp;&nbsp;&nbsp;";
echo "<p /><label for='orderby'><B>Order by:&nbsp;</B></label><select name='order' class='textbox' style='width: 66px; margin-top: 3px;' onChange=\"if(document.MyForm.order.selectedIndex != 0) document.location =
 '".FUSION_SELF."?sortby=' + document.MyForm.order.options[document.MyForm.order.selectedIndex].value\"><option name='order' value='".$name."' >Name</option>
 <option name='order' value='".$format."' >Format</option><option name='order' value='".$mpaa."' >Rating</option>
 </select> 
 <select name='order_way' class='textbox' style='margin-top:3px;width: 55px;' onChange=\"if(document.MyForm.order_way.selectedIndex != 0) document.location = '".FUSION_SELF."?sortby=' + document.MyForm.order_way.options[document.MyForm.order_way.selectedIndex].value\">
 <option name='order_way' value='asc' selected>ASC</option>
 <option name='order_way' value='desc' >DEC</option>
 </select>";
echo "&nbsp;&nbsp;&nbsp;<BR><img src='".IMAGES."clear.gif' width='2px' height='12px' border='0' alt=''>
 </fieldset></div></form><br style='clear:both;' /><br /></TD></TR></TABLE>";
 
// SELECT LIST END
if ($rows > $rowmax) 
{ 
echo "<div align='center' style='margin:10px;'>".makepagenav($_GET['rowstart'], $rowmax, $rows, 3, 
 FUSION_SELF."?sortby=".$_GET['sortby']."&amp;")."</div>\n"; 
}
echo "<table width='100%' cellspacing='0' cellpadding='3' class='mov'><tr>
 <td class='sub-header'>".$locale['MMDb_106']."</td><td class='sub-header'>".$locale['MMDb_107']."</td><td class='sub-header'>".$locale['MMDb_108']."</td><td class='sub-header'>".$locale['MMDb_109']."</td><td class='sub-header'>".$locale['MMDb_110']."</td><td class='sub-header' align='right'>".$locale['MMDb_111a']."</td></tr>";
$result = dbquery("SELECT * FROM ".$db_prefix."movies".$orderby." ORDER BY $name LIMIT ".$_GET['rowstart'].", ".$rowmax);
while ($data = dbarray($result)) 
{ 
 echo "<TR onMouseOver=\"this.style.backgroundColor='#bdf8bd'\"; 
 onMouseOut=\"this.style.backgroundColor='transparent'\"><TD width='50%' style=\"border-bottom: 1px solid #000\"><B>$data[title_name]</B></TD><TD align='left' width='10%' style=\"border-bottom: 1px solid #000\"><b>$data[title_mpaa]</b>&nbsp;</TD><TD width='10%' align='left' style=\"border-bottom: 1px solid #000\"><b>$data[title_date]</b>&nbsp;</TD><TD width='10%' align='left' style=\"border-bottom: 1px solid #000\"><b>&nbsp;&nbsp; $data[title_length]</b>&nbsp;</TD>";
if( $data[$url] != $null ) 
{
 echo "<TD width='10%' align='left' style=\"border-bottom: 1px solid #000\"><b>&nbsp;&nbsp; $data[title_format]</b>&nbsp;</TD>
 <TD align='right' style=\"border-bottom: 1px solid #000\"><A HREF='$data[$url]' target='new'><IMG SRC='images/imdb.png' WIDTH='31' HEIGHT='15' BORDER='0' ALT='Internet Movie DataBase Description'></A></TD></TR>";
}
elseif ( $data[$url] > $null ) 
{
 echo "<TD width='10%' align='left' style=\"border-bottom: 1px solid #000\"><b>&nbsp;&nbsp; $data[title_format]</b>&nbsp;</TD>
 <TD align='right' style=\"border-bottom: 1px solid #000\"><A HREF='$data[$url]' target='new'><IMG SRC='images/imdb.png' WIDTH='31' HEIGHT='15' BORDER='0' ALT='Internet Movie DataBase Description'></A></TD></TR>";
}
else
{
echo "<TD width='10%' align='left' style=\"border-bottom: 1px solid #000\"><b>&nbsp;&nbsp; $data[title_format]</b>&nbsp;</TD>
 <TD align='right' style=\"border-bottom: 1px solid #000\">&nbsp;</TD></TR>";
}
}
}
 echo "</table>\n"; 
} 
if ($rows < 1) 
echo " <table width='100%' cellspacing='0' cellpadding='3'><tr><td align='center' cellpadding='23' >
 <P><br><P><b>".$locale['MMDb_112']." <p><form method='post' action='".FUSION_SELF."?sortby=all'><input class='button' type='submit' value='".$locale['MMDb_113']."'></form><P><br><P></td></tr></table>\n";
closetable();
if ($rows > $rowmax) 
{ 
echo "<div align='center' style='margin-top:5px;'>".makepagenav($_GET['rowstart'], $rowmax, $rows, 3, 
 FUSION_SELF."?sortby=".$_GET['sortby']."&amp;")."</div>\n"; 
}
require_once THEMES."templates/footer.php";
?>

Any help with this and I, as well as my wife, God bless her patient heart, will be so very, very greatful.

andrewsmd

2:42 pm on Nov 21, 2008 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



I didn't read all of your code (because I'm a programmer and we are lazy) but I have done something of the sort before. The way I implemented it is to use templates. I didn't actually display the dropdown until the user clicked a button to. You could display it as a default and let the user change it on a click though. If you haven't used templates check out the HTML_Template/IT.php file and its corresponding stuff. It's really nice. You have to get the input from the user on how they want to sort. I could help you a lot more if you could just post a test file that only had the dropdown with some code to add stuff to it and how you want to let the user sort it.

proteus9

4:08 pm on Nov 21, 2008 (gmt 0)

10+ Year Member



Here you go. I've cleaned it up so as to show only the needed parts so as to have a better understanding of what I'm working with.

About that HTML_Template/IT.php file you were talking about, uhm... perhaps I'm just slow, but where am I supposed to be looking for this at exactly?

Here's my code so far: 


$rowmax = 40;
$id = "title_id";
$name = "title_name";
$mpaa = "title_mpaa";
$date = "title_date";
$length = "title_length";
$format = "title_format";
$url = "title_url";
$asc = "ASC";
$desc = "DESC";
$temp = Array ("$name","$mpaa","$format");
$null = "";
if (!isset($_GET['sortby']) ¦¦ !preg_match("/^[0-9A-Z]$/", $_GET['sortby'])) $_GET['sortby']= "all";
$orderby = ($_GET['sortby'] == "all" ? "" : " WHERE $temp[0] LIKE '".stripinput($_GET['sortby'])."%'");
$result = dbquery("SELECT * FROM ".$db_prefix."movies".$orderby."");
$search = array("1","2","3","4","5","6","7","8","9","A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R", "S","T","U","V","W","X","Y","Z","0");
 
 echo "</TD><TD>
  <form name='MyForm' action='index.php' method='post' style='display:inline;'>
  <div style='text-align:right; float:right; width:70%'><fieldset class='textbox'>
  <legend class='textbox'>&nbsp;Browse Options&nbsp;</legend><BR>
 <div align='left' style='margin-left: 43px;'><B><U>View By:</U></B></div>
  <BR><label for='title_name'>Name:</label>
  <select name='".$name."' class='textbox' style='width: 125px; margin-top: -3px;' onChange=\"if(document.MyForm.title_name.selectedIndex != 0) document.location = 'index.php?sortby=' + document.MyForm.title_name.options[document.MyForm.title_name.selectedIndex].value\">
 <option name='title_name' value='".$search."'>Choose...</option>
 <option name='title_name' value='index.php?sortby=all' >All</option>";
$search = array("DVD","HD-DVD","Blu-Ray");
$i=0;$i < 3!="";$i++;
 echo "<label for='title_format'>Format:</label>
  <select name='title_format' class='textbox' style='width: 125px; margin-top: 3px;' onChange=\"if(document.MyForm.title_format.selectedIndex != 0) document.location = 'index.php?sortby=' + document.MyForm.title_format.options[document.MyForm.title_format.selectedIndex].value\"><option name='".$name."' value='".$search."'>Choose...</option>
 <option name='title_format' value='index.php?sortby=title_format'>All</option>";
$search = array("G","PG","PG-13", "R", "NC-17", "X", "TV-PG", "TV-14", "TV-MA", "NR");
 $i=0;$i < 10!="";$i++;
echo "<br /><label for='title_mpaa'>Rating:</label>
  <select name='title_mpaa' class='textbox' style='width: 125px; margin-top: 3px;' onChange=\"if(document.MyForm.title_mpaa.selectedIndex != 0) document.location = 'index.php?sortby=' + document.MyForm.title_mpaa.options[document.MyForm.title_mpaa.selectedIndex].value\"><option name='".$name."' value='".$search."'>Choose...</option>
 <option name='title_mpaa' value='index.php?sortby=title_mpaa'>All</option>";
 
 for ($i=0;$i < 10!="";$i++) 
{
echo "<option name='title_mpaa' value='".$search[$i]."'>".$search[$i]."</option>\n";
}
echo "</select>";
echo "<p /><label for='orderby'><B>Order by:&nbsp;</B></label><select name='order' class='textbox' style='width: 66px; margin-top: 3px;' onChange=\"if(document.MyForm.order.selectedIndex != 0) document.location =
 'index.php?sortby=' + document.MyForm.order.options[document.MyForm.order.selectedIndex].value\"><option name='order' value='".$name."' >Name</option>
 <option name='order' value='".$format."' >Format</option><option name='order' value='".$mpaa."' >Rating</option>
 </select> 
 <select name='order_way' class='textbox' style='margin-top:3px;width: 55px;' onChange=\"if(document.MyForm.order_way.selectedIndex != 0) document.location = 'index.php?sortby=' + document.MyForm.order_way.options[document.MyForm.order_way.selectedIndex].value\">
 <option name='order_way' value='asc' selected>ASC</option>
 <option name='order_way' value='desc' >DEC</option>
 </select>";
echo "</fieldset></div></form><br style='clear:both;' /><br /></TD></TR></TABLE>";
 
$result = dbquery("SELECT * FROM ".$db_prefix."movies".$orderby." ORDER BY $format LIMIT ".$_GET['rowstart'].", ".$rowmax);
while ($data = dbarray($result)) 
{ 
 echo "<TR onMouseOver=\"this.style.backgroundColor='#bdf8bd'\"; 
 onMouseOut=\"this.style.backgroundColor='transparent'\"><TD width='50%' style=\"border-bottom: 1px solid #000\"><B>$data[title_name]</B></TD><TD align='left' width='10%' style=\"border-bottom: 1px solid #000\"><b>$data[title_mpaa]</b>&nbsp;</TD><TD width='10%' align='left' style=\"border-bottom: 1px solid #000\"><b>$data[title_date]</b>&nbsp;</TD><TD width='10%' align='left' style=\"border-bottom: 1px solid #000\"><b>&nbsp;&nbsp; $data[title_length]</b>&nbsp;</TD>";

andrewsmd

4:40 pm on Nov 21, 2008 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



Your not slow, I should have been more specific sorry. The template file is available for download just google download html_template_IT for php. You also have to have PEAR installed for that if you don't already. If you just maintain one site then let's skip all of that. You seem to know what your doing. Is this page up on the web so I can look at the front end? No offense, but my mind is not a web browser and I cannot parse all of that in my head. As far as some pseudo for this, get the input from the user, then write your query dynamically based on what they selected like this keep in mind this is pseudo
You have a dropdown like this
<html>
<form name ='form' method = 'post'>
<select name = "sortSelect">
<option value = "theNameOfYourColum">What you want to display</option.
</select>
<input type = 'submit' name = 'sortSubmit'>
</form>

<?php

//if they clicked the submit button
if(isset($_POST['sortSubmit'])){

//the selection from the dropdown
//if you set the values equal to your column names
//then you can just set this to the select box
//vlaue and insert it straight into your query
$orderSelect = $_POST['sortSelect'];

$query = "select * from yourTable where yourConditions order by {$orderSelect};";

echo("<select name = 'orderedSelect'>");

while(there are rows matching your query){

echo("<option value = 'rowInQuery'>rowInQuery</option>");

}//while
echo("</select>");

}//if isset

?>
</html>
repeat the options for all of your columns you want them to be able to sort by
this is why I reccomend template implementation because you can put your php code at the end and then just set the template. Let me know if I can help more.

proteus9

5:27 pm on Nov 21, 2008 (gmt 0)

10+ Year Member



andrewsmd,
I've sent you a stickymail with the address and some login info that you will need to view the front-end. I appreciate the help big-time!
P.S. My wife says you're probably going to save our marriage.
(eh...?)

BTW: follow the 'Movie List' link in the menu.

andrewsmd

5:43 pm on Nov 21, 2008 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



Sent you a message. I'm glad to help, this place has saved my butt more times than I would like to admit (or even be able to count).

Anyango

8:40 am on Nov 22, 2008 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



Just on a lighter note,

proteus9 Please don't let your marriage be spoiled by a simple code!

I would give away programming, or anything for that matter, to keep My (Future) wife Happy ;)

[edited by: Anyango at 8:41 am (utc) on Nov. 22, 2008]

 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

  1. Home
  2. Forums Index
  3. Server Side / PHP Server Side Scripting 11:39 am May 1, 2026