Change database sort order with select box

INeedAGig · April 22, 2011

Hey guys, I am having trouble figuring this out. I currently have a database that automatically sorts itself by the id of the data. But, I am wanting the user to be able to change the sort order via a select box. I already have the select box programmed with the options, but I am not sure how to go about coding the options to change the sort order of the displayed data. What would the best way to go about programming this be?

Thank you very much ahead of time!!

Drummin · April 22, 2011

Just add the $orderby where you would normally write it in and send the GET info by form or link. Change as needed.

 
IF ($_GET['ln']){//Last Name
$orderby=" ORDER BY lastname DESC";
} 
IF ($_GET['t']){//Title
$orderby=" ORDER BY title DESC";
}

Drummin · April 22, 2011

...And you can add the variables as well.

IF ($_GET['ln']=='d'){//Last Name DESC
$orderby=" ORDER BY lastname DESC";
} 
[code]IF ($_GET['ln']=='a'){//Last Name ASC
$orderby=" ORDER BY lastname ASC";
}

INeedAGig · April 23, 2011

What would I apply to the actual options in the select box? (code wise) so it automatically changes when you select one of the options without having to hit a submit button...

thanks!!

Fadion · April 24, 2011

What would I apply to the actual options in the select box? (code wise) so it automatically changes when you select one of the options without having to hit a submit button...

thanks!!

That would need Javascript. Basically you can attach a "onchange" event handler to the select box that submits the form automatically. Or you can go a step forward and use AJAX (yet again in Javascript) to update data asynchronously without even needing to submit the form. That would need more work, but there are lots of tutorials out there. Take a look at jQuery, which will help a lot in daily Javascript coding and most importantly, AJAX calls.

Drummin · April 24, 2011

As GuiltyGear said. Here's parts of what I'm using that applies the change with GET.

In the head

<SCRIPT language=JavaScript type="text/javascript">
function reload(form)
{
var val=form.cat.options[form.cat.options.selectedIndex].value;
self.location='pagename.php?cat=' + val ;
}

</script>

Add the "onchange to the SELECT of the Form

<SELECT NAME=cat onchange="reload(this.form)">

INeedAGig · April 24, 2011

Alright, cool. I forgot about the onChange event handler...didnt' even think about that...

Thanks for your replies guys, I am going to test out your suggestions and see how it plays out. I appreciate your help!

INeedAGig · April 25, 2011

Okay, I have a small issue. The database page is automatically refreshing when one of the options is selected and echoing the selected option properly in the address bar after the url as well but it is not physically changing the order in which the database is displayed, it is just staying with the main sort order I programmed to begin with. I have attached some code snippets from my main database page to help you help me.

These are the first PHP functions before the html aspect of displaying the database

<?php
session_start();
if (!isset($_SESSION['username']) ||
(trim($_SESSION['username'])=='')) {
header("location: login.php");
exit();
}
//Connect to Database
$db_username="removed_for_this_post";
$password="removed_for_this_post";
$database="removed_for_this_post";

mysql_connect("removed_for_this_post",$db_username,$password); //Connection to Database
@mysql_select_db($database) or die("ALERT! Database not found!"); //Selection of Database

$query="SELECT * FROM leads ORDER by id DESC"; //This is my default sort order from my table 'leads'
$result=mysql_query($query);
$num=mysql_numrows($result);
if ($_GET['name']){//This is to order it by the name field in the database
$orderby="SELECT * FROM leads ORDER by name DESC";
} 
if ($_GET['age']){//This is to order it by the age field in the database
$orderby="SELECT * FROM leads ORDER by age DESC";
}
mysql_close();
?>

This is the Javascript for the select box control

<head>
<link href="css/db_style.css" rel="stylesheet" type="text/css" media="screen" />
<script language="javascript" type="text/javascript">
function reload(form)
{
var val=form.sort.options[form.sort.options.selectedIndex].value;
self.location='mypd_interface.php?sort=' + val ;
}
</script>
</head>

This is the code for the select box

<form method="get" name="sort">
<select name="sort" id="sort" onchange="reload(this.form)"> 
<option value="">--Select--</option> 
<option value='Name'>Name</option>
<option value='Age'>Age</option>
</select>
</form>

Thanks guys!

INeedAGig · April 25, 2011

**bump**

Psycho · April 25, 2011

Look at your PHP code - you are always RUNNING the default query. It seems you only create the variables for the other queries based upon the selected sort order - but you don't run them. Plus, your field name is "sort" and you are using $_GET['name'] and $_GET['age'] to test the submitted value.

<?php
session_start();
if (!isset($_SESSION['username']) || (trim($_SESSION['username'])==''))
{
    header("location: login.php");
    exit();
}

//Connect to Database
$db_username = "removed_for_this_post";
$password    = "removed_for_this_post";
$database    = "removed_for_this_post";
mysql_connect("removed_for_this_post", $db_username ,$password); //Connection to Database
@mysql_select_db($database) or die("ALERT! Database not found!"); //Selection of Database

//Determine sort field
switch(strtolower($_GET['sort']))
{
    case 'name':
    case 'age':
        $sort_field = strtolower($_GET['sort']);
        break;
    default:
        $sort_field = 'id';
}

//Create and run query
$query="SELECT * FROM leads ORDER by {$sort_field} DESC";
$result = mysql_query($query);
$num = mysql_numrows($result);

//Display results

mysql_close();
?>

INeedAGig · April 25, 2011

Thank you for your response MJ, it's much appreciated. I made changes using your suggestions but now none of the data is being displayed...

Psycho · April 25, 2011

You didn't show the code that displays the results. I *assumed* you were using the DB result contained in $result variable, but apparently you were running another query using the string from the $orderby variable.

So, either the query is failing or you are referencing the wrong result variable. Either way you would have had an error on your page and you should have stated that instead of just saying "none of the data is being displayed". Add error handling to your query and ensure you are referencing the correct variable when you output the results

INeedAGig · April 26, 2011

Apologies.

This is the code I have written that displays the results. Using the suggestions you provided to change the sort order makes it so the page doesn't display any of the data in the tables like it normally does without the sort order coding in place. I have attached the snippet of coding below that I wrote to display the data in the database.

<th bgcolor="#01337f"><font face="Arial, Helvetica, sans-serif" size="2" color="#FFFFFF">Name</font></th>
<th bgcolor="#01337f"><font face="Arial, Helvetica, sans-serif" size="2" color="#FFFFFF">E-Mail</font></th>
<th bgcolor="#01337f"><font face="Arial, Helvetica, sans-serif" size="2" color="#FFFFFF">Age</font></th>
<th bgcolor="#01337f"><font face="Arial, Helvetica, sans-serif" size="2" color="#FFFFFF">Gender</font></th>
<th bgcolor="#01337f"><font face="Arial, Helvetica, sans-serif" size="2" color="#FFFFFF">Location</font></th>
<th bgcolor="#01337f"><font face="Arial, Helvetica, sans-serif" size="2" color="#FFFFFF">Home Phone</font></th>
<th bgcolor="#01337f"><font face="Arial, Helvetica, sans-serif" size="2" color="#FFFFFF">Other Phone</font></th>
<th bgcolor="#01337f"><font face="Arial, Helvetica, sans-serif" size="2" color="#FFFFFF">Best Time to Reach</font></th>
<th bgcolor="#01337f"><font face="Arial, Helvetica, sans-serif" size="2" color="#FFFFFF">Referrer</font></th>
<th bgcolor="#01337f"><font face="Arial, Helvetica, sans-serif" size="2" color="#FFFFFF">--- Options ---</font></th>
</tr>
</div>
<div id="footer">
<div class="footer_content">
<font class="footer_header">
Data removed from here for this post
</div>
</div>
<?php
$i=0;
while ($i < $num) {
$id=mysql_result($result,$i,"id"); //Unique ID Field
$name=mysql_result($result,$i,"name"); //Name
$email=mysql_result($result,$i,"email"); //EMail Address
$age=mysql_result($result,$i,"age"); //Age
$gender=mysql_result($result,$i,"gender"); //Gender
$location=mysql_result($result,$i,"location"); //City of Residence
$homephone=mysql_result($result,$i,"homephone"); //Home Phone Number
$otherphone=mysql_result($result,$i,"otherphone"); //Secondary Phone Number
$besttime=mysql_result($result,$i,"besttime"); //Best Time to Reach
$referrer=mysql_result($result,$i,"referrer"); //Referrer
?>
<tr>
<td align="center" bgcolor="#ebf4fb"><font class="lead_txt"><? echo $name; ?></font></td>
<td align="center" bgcolor="#ebf4fb"><font face="Arial, Helvetica, sans-serif" size="2" class="lead_txt"><? echo $email; ?></font></td>
<td align="center" bgcolor="#ebf4fb"><font face="Arial, Helvetica, sans-serif" size="2" class="lead_txt"><? echo $age; ?></font></td>
<td align="center" bgcolor="#ebf4fb"><font face="Arial, Helvetica, sans-serif" size="2" class="lead_txt"><? echo $gender; ?></font></td>
<td align="center" bgcolor="#ebf4fb"><font face="Arial, Helvetica, sans-serif" size="2" class="lead_txt"><? echo $location; ?></font></td>
<td align="center" bgcolor="#ebf4fb"><font face="Arial, Helvetica, sans-serif" size="2" class="lead_txt"><? echo $homephone; ?></font></td>
<td align="center" bgcolor="#ebf4fb"><font face="Arial, Helvetica, sans-serif" size="2" class="lead_txt"><? echo $otherphone; ?></font></td>
<td align="center" bgcolor="#ebf4fb"><font face="Arial, Helvetica, sans-serif" size="2" class="lead_txt"><? echo $besttime; ?></font></td>
<td align="center" bgcolor="#ebf4fb"><font face="Arial, Helvetica, sans-serif" size="2" class="lead_txt"><? echo $referrer; ?></font></td>
<td bgcolor="#01337f""><a href="db_edit.php?id=<?php echo $id; ?>"><img src="images/edit.png" border="0" width="25" height="25" alt="Edit"></a>   <a href="db_remove.php?id=<?php echo $id; ?>"><img src="images/delete.png" border="0" width="25" height="25" alt="Delete"></a>   <a href="email_lead.php?id=<?php echo $name, $email; ?>"><img src="images/email.png" border="0" width="25" height="25" alt="E-Mail"></a></td>
</tr>
</center>
</font>
</body>
</html>
<?php
$i++;
}
echo "</table>";
?>

Psycho · April 26, 2011

That looks pretty messy to use mysql_result() for each and every field. You should be using one of the mysql_fetch functions instead. In any event you need to add some debugging code to help find errors.

Change

$result = mysql_query($query);

To

$result = mysql_query($query) or die("Query: $query<br>Error: " . mysql_error());
echo "The query "$query" returned " . mysql_num_rows($result) . " records.<br />";

This will tell you if the query is running and, if so, how many records are being returned.

As for your display code you REALLY need to stop using FONT tags - those were deprecated YEARS ago. Instead use a defined class name for the font characteristics. Also, your display code is creating invalid HTML. For instance you are creating "</html>" at the end of each record within the loop and you have non-table code within the table. Anyway, the code to display the content could look like this:

while ($row = mysql_fetch_assoc($result))
{
    echo "<tr>
    <td align=\"center\" bgcolor=\"#ebf4fb\" class=\"lead_txt\">{$row['name']}</td>\n
    <td align=\"center\" bgcolor=\"#ebf4fb\" class=\"fontClass\">{$row['email']}</td>
    <td align=\"center\" bgcolor=\"#ebf4fb\" class=\"fontClass\">{$row['age']}</td>
    <td align=\"center\" bgcolor=\"#ebf4fb\" class=\"fontClass\">{$row['gender']}</td>
    <td align=\"center\" bgcolor=\"#ebf4fb\" class=\"fontClass\">{$row['location']}</td>
    <td align=\"center\" bgcolor=\"#ebf4fb\" class=\"fontClass\">{$row['homephone']}</td>
    <td align=\"center\" bgcolor=\"#ebf4fb\" class=\"fontClass\">{$row['otherphone']}</td>
    <td align=\"center\" bgcolor=\"#ebf4fb\" class=\"fontClass\"></td>
    <td align=\"center\" bgcolor=\"#ebf4fb\" class=\"fontClass\">{$row['referrer']}</td>
    <td bgcolor=\"#01337f\">
      <a href=\"db_edit.php?id={$row['id']}\">
      <img src=\"images/edit.png\" border=\"0\" width=\"25\" height=\"25\" alt=\"Edit\">
      </a>
         
      <a href=\"db_remove.php?id={$row['id']}\">
      <img src=\"images/delete.png\" border=\"0\" width=\"25\" height=\"25\" alt=\"Delete\">
      </a>
         
      <a href=\"email_lead.php?id={$row['name']} {$row['email']}\">
      <img src=\"images/email.png\" border=\"0\" width=\"25\" height=\"25\" alt=\"E-Mail\">
      </a>
    </td>
    </tr>";
}

Sign In

Change database sort order with select box

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information