...

View Full Version : Sort Query By Clicking Column Header



thilss0o
03-07-2010, 09:13 PM
I want to be able to re-order the list by clicking on the respective header. This is my code


<table border="0" cellspacing="0" style="border-top:solid 1px #333;">
<tr style="border-left:solid 1px #333;border-right:solid 1px #333; border-bottom:solid 1px #333; background-color:#CCCCCC;" >
<td style="width:200px;padding:2px 2px 2px 2px;"><b>Entrant</b></td>
<td style="width:50px;padding:2px 2px 2px 2px;"><b>Points</b></td>
<td style="width:150px;padding:2px 2px 2px 2px;"><b>Choosen Winner</b></td>
<td style="width:100px;padding:2px 2px 2px 2px;"><b>View Bracket</b></td>
</tr>
</table>
<table border="0" cellspacing="0" style="background-image:url(images/table-bg.png);border-bottom:solid 1px #333;">
<?php
// make connection
mysql_connect("localhost", "root", "moreland") or die('Could not connect');
mysql_select_db("ipool") or die('Could not connect to database');

// build query
$query = mysql_query("SELECT * FROM TestTable ORDER by ID");
// display results
while ($row = mysql_fetch_array($query)) {
echo
"<tr style='border-left:solid 1px #333;border-right:solid 1px #333;'>
<td style='width:200px;padding:2px 2px 2px 2px;'>" .$row['fullname']. "</td>
<td style='width:50px;padding:2px 2px 2px 2px;'>N/A</td>
<td style='width:150px;padding:2px 2px 2px 2px;'>" .$row['pick6_1']. "</td>
<td style='width:100px;padding:2px 2px 2px 2px;'><p class='view-bracket-link'><a href='system/displays/display-bracket-id-".$row['ID'].".php'>View Bracket</a></p></td>
</tr>"

;}

?>
</table>

you can see it in action here: http://www.tomhilsee.com/ipool/standings.php

thanks

_Aerospace_Eng_
03-07-2010, 09:46 PM
Make the headers links and then in the link append a query string to the url. Use your php to get this query string. If it is set pass it through mysql_real_escape_string and then use that as the ORDER BY instead of ID.

mlseim
03-07-2010, 09:57 PM
Use cookies ...

Set two cookies, one for which column to sort, and which order ASC or DESC.

Like this:


<?php

// Assign the name of your script so it will work no matter what you named it.
$url=$_SERVER['PHP_SELF'];

// If a sort was requested, then process it.
if($action == "sort"){
$s=$_GET['s'];
$o=$_GET['o'];
setcookie("csort", $s, time()+604800);
setcookie("order", $o, time()+604800);
// after cookies are set, re-run the script.
header ("location: $url");
}

// set the default, in case there are no cookies set.
$csort="ID";
$order="DESC";

// Read the Sort Cookie (if it exists)
if (isset($_COOKIE["csort"])){
$csort=$_COOKIE["csort"];
}
if (isset($_COOKIE["order"])){
$order=$_COOKIE["order"];
}

// sanitize any variables used in the MySQL query (always sanitize the variables).
$csort = mysql_real_escape_string($csort);
$order = mysql_real_escape_string($order);
?>

<table border="0" cellspacing="0" style="border-top:solid 1px #333;">
<tr style="border-left:solid 1px #333;border-right:solid 1px #333; border-bottom:solid 1px #333; background-color:#CCCCCC;" >
<td style="width:200px;padding:2px 2px 2px 2px;"><b>Sort by: <a href="<?=$url?>?action=sort&s=fullname&s=ASC">asc</a> | <a href="<?=$url?>?action=sort&s=fullname&s=DESC">desc</a></b></td>
<td style="width:50px;padding:2px 2px 2px 2px;"><b>Sort by: asc | desc</b></td>
<td style="width:150px;padding:2px 2px 2px 2px;"><b>Sort by: <a href="<?=$url?>?action=sort&s=pick6_1&s=ASC">asc</a> | <a href="<?=$url?>?action=sort&s=pick6_1&s=DESC">desc</a></b></td>
<td style="width:100px;padding:2px 2px 2px 2px;"><b>Sort by: <a href="<?=$url?>?action=sort&s=ID&s=ASC">asc</a> | <a href="<?=$url?>?action=sort&s=ID&s=DESC">desc</a></b></td>
</tr>
<tr style="border-left:solid 1px #333;border-right:solid 1px #333; border-bottom:solid 1px #333; background-color:#CCCCCC;" >
<td style="width:200px;padding:2px 2px 2px 2px;"><b>Entrant</b></td>
<td style="width:50px;padding:2px 2px 2px 2px;"><b>Points</b></td>
<td style="width:150px;padding:2px 2px 2px 2px;"><b>Choosen Winner</b></td>
<td style="width:100px;padding:2px 2px 2px 2px;"><b>View Bracket</b></td>
</tr>
</table>
<table border="0" cellspacing="0" style="background-image:url(images/table-bg.png);border-bottom:solid 1px #333;">
<?php
// make connection
mysql_connect("localhost", "root", "moreland") or die('Could not connect');
mysql_select_db("ipool") or die('Could not connect to database');

// build query
$query = mysql_query("SELECT * FROM TestTable ORDER by $csort $order");
// display results
while ($row = mysql_fetch_array($query)) {
echo
"<tr style='border-left:solid 1px #333;border-right:solid 1px #333;'>
<td style='width:200px;padding:2px 2px 2px 2px;'>" .$row['fullname']. "</td>
<td style='width:50px;padding:2px 2px 2px 2px;'>N/A</td>
<td style='width:150px;padding:2px 2px 2px 2px;'>" .$row['pick6_1']. "</td>
<td style='width:100px;padding:2px 2px 2px 2px;'><p class='view-bracket-link'><a href='system/displays/display-bracket-id-".$row['ID'].".php'>View Bracket</a></p></td>
</tr>"

;}

?>
</table>



EDIT:
The same answer as Aerospace ... we posted at the same time.
I like to use cookies though, because the sort will remain the same when you revisit the page.
sort of like saving your sort preference.

I didn't make a way to delete the cookies, but putting a negative number in for the time will remove the cookie(s).


.

thilss0o
03-08-2010, 03:10 AM
Hey looks great thanks! Looks like you are both doing the same thing, but is it possible to do it without the cookies? I just would rather the user to be able to see the default sorting if they return.


--- and i'm also having trouble with the stuff actually sorting... the url changes, and it seems like it should work. I tried doing a manual ORDER by to see if the columns are actually sortable, and they are, but i just cant get it to do it by clicking on asc or dec

any thoughts?
http://www.tomhilsee.com/ipool/standings.php (updated with code from mlseim post)

mlseim
03-08-2010, 02:22 PM
Give us a new listing of what you actually have ...

My guess is you're missing this:
$query = mysql_query("SELECT * FROM TestTable ORDER by $csort $order");

We can use sessions instead of cookies, those go away when the user closes their browser.

thilss0o
03-08-2010, 08:46 PM
sessions would be ideal, yes. I do have the ORDER BY $scort or whatever, ill post the listing anyway tho:


<?php

// Assign the name of your script so it will work no matter what you named it.
$url=$_SERVER['PHP_SELF'];

// If a sort was requested, then process it.
if($action == "sort"){
$s=$_GET['s'];
$o=$_GET['o'];
setcookie("csort", $s, time()+604800);
setcookie("order", $o, time()+604800);
// after cookies are set, re-run the script.
header ("location: $url");
}

// set the default, in case there are no cookies set.
$csort="ID";
$order="DESC";

// Read the Sort Cookie (if it exists)
if (isset($_COOKIE["csort"])){
$csort=$_COOKIE["csort"];
}
if (isset($_COOKIE["order"])){
$order=$_COOKIE["order"];
}

// sanitize any variables used in the MySQL query (always sanitize the variables).
$csort = mysql_real_escape_string($csort);
$order = mysql_real_escape_string($order);
?>

<table border="0" cellspacing="0" style="border-top:solid 1px #333;">
<tr style="border-left:solid 1px #333;border-right:solid 1px #333; border-bottom:solid 1px #333; background-color:#CCCCCC;" >
<td style="width:200px;padding:2px 2px 2px 2px;"><b>Sort by: <a href="<?=$url?>?action=sort&s=fullname&s=ASC">asc</a> | <a href="<?=$url?>?action=sort&s=fullname&s=DESC">desc</a></b></td>
<td style="width:50px;padding:2px 2px 2px 2px;"><b>Sort by: asc | desc</b></td>
<td style="width:150px;padding:2px 2px 2px 2px;"><b>Sort by: <a href="<?=$url?>?action=sort&s=pick6_1&s=ASC">asc</a> | <a href="<?=$url?>?action=sort&s=pick6_1&s=DESC">desc</a></b></td>
<td style="width:100px;padding:2px 2px 2px 2px;"><b>Sort by: <a href="<?=$url?>?action=sort&s=ID&s=ASC">asc</a> | <a href="<?=$url?>?action=sort&s=ID&s=DESC">desc</a></b></td>
</tr>
<tr style="border-left:solid 1px #333;border-right:solid 1px #333; border-bottom:solid 1px #333; background-color:#CCCCCC;" >
<td style="width:200px;padding:2px 2px 2px 2px;"><b>Entrant</b></td>
<td style="width:50px;padding:2px 2px 2px 2px;"><b>Points</b></td>
<td style="width:150px;padding:2px 2px 2px 2px;"><b>Choosen Winner</b></td>
<td style="width:100px;padding:2px 2px 2px 2px;"><b>View Bracket</b></td>
</tr>
</table>
<table border="0" cellspacing="0" style="background-image:url(images/table-bg.png);border-bottom:solid 1px #333;">
<?php
// make connection
mysql_connect("localhost", "root", "moreland") or die('Could not connect');
mysql_select_db("ipool") or die('Could not connect to database');

// build query
$query = mysql_query("SELECT * FROM TestTable ORDER by $csort $order");
// display results
while ($row = mysql_fetch_array($query)) {
echo
"<tr style='border-left:solid 1px #333;border-right:solid 1px #333;'>
<td style='width:200px;padding:2px 2px 2px 2px;'>" .$row['fullname']. "</td>
<td style='width:50px;padding:2px 2px 2px 2px;'>N/A</td>
<td style='width:150px;padding:2px 2px 2px 2px;'>" .$row['pick6_1']. "</td>
<td style='width:100px;padding:2px 2px 2px 2px;'><p class='view-bracket-link'><a href='system/displays/display-bracket-id-".$row['ID'].".php'>View Bracket</a></p></td>
</tr>"

;}

?>
</table>
</div>

thanks.... i think its the same as your previous post tho, wasn't sure if changing anything was necessary to make it work, but it didnt seem like it as you said the sort should work regardless of what the name is...

mlseim
03-08-2010, 09:32 PM
With SESSIONS instead ...

Try this ... see if it works this time ...



<?php
session_start();

// Assign the name of your script so it will work no matter what you named it.
$url=$_SERVER['PHP_SELF'];

// If a sort was requested, then process it.
if($action == "sort"){
$s=$_GET['s'];
$o=$_GET['o'];
$_SESSION['csort'] = $s;
$_SESSION['order'] = $o;
// after sessions are set, re-run the script.
header ("location: $url");
}

// set the default, in case session is not set.
$csort="ID";
$order="DESC";

// Read the SESSION variables (if they exist)
if(isset($_SESSION['csort'])){
$csort=$_SESSION['csort'];
}
if(isset($_SESSION['order'])){
$order=$_SESSION['order'];
}

// sanitize any variables used in the MySQL query (always sanitize the variables).
$csort = mysql_real_escape_string($csort);
$order = mysql_real_escape_string($order);
?>

<table border="0" cellspacing="0" style="border-top:solid 1px #333;">
<tr style="border-left:solid 1px #333;border-right:solid 1px #333; border-bottom:solid 1px #333; background-color:#CCCCCC;" >
<td style="width:200px;padding:2px 2px 2px 2px;"><b>Sort by: <a href="<?=$url?>?action=sort&s=fullname&s=ASC">asc</a> | <a href="<?=$url?>?action=sort&s=fullname&s=DESC">desc</a></b></td>
<td style="width:50px;padding:2px 2px 2px 2px;"><b> &nbsp; </b></td>
<td style="width:150px;padding:2px 2px 2px 2px;"><b>Sort by: <a href="<?=$url?>?action=sort&s=pick6_1&s=ASC">asc</a> | <a href="<?=$url?>?action=sort&s=pick6_1&s=DESC">desc</a></b></td>
<td style="width:100px;padding:2px 2px 2px 2px;"><b>Sort by: <a href="<?=$url?>?action=sort&s=ID&s=ASC">asc</a> | <a href="<?=$url?>?action=sort&s=ID&s=DESC">desc</a></b></td>
</tr>
<tr style="border-left:solid 1px #333;border-right:solid 1px #333; border-bottom:solid 1px #333; background-color:#CCCCCC;" >
<td style="width:200px;padding:2px 2px 2px 2px;"><b>Entrant</b></td>
<td style="width:50px;padding:2px 2px 2px 2px;"><b>Points</b></td>
<td style="width:150px;padding:2px 2px 2px 2px;"><b>Choosen Winner</b></td>
<td style="width:100px;padding:2px 2px 2px 2px;"><b>View Bracket</b></td>
</tr>
</table>
<table border="0" cellspacing="0" style="background-image:url(images/table-bg.png);border-bottom:solid 1px #333;">
<?php
// make connection
mysql_connect("localhost", "root", "moreland") or die('Could not connect');
mysql_select_db("ipool") or die('Could not connect to database');

// build query
$query = mysql_query("SELECT * FROM TestTable ORDER by $csort $order");
// display results
while ($row = mysql_fetch_array($query)) {
echo
"<tr style='border-left:solid 1px #333;border-right:solid 1px #333;'>
<td style='width:200px;padding:2px 2px 2px 2px;'>" .$row['fullname']. "</td>
<td style='width:50px;padding:2px 2px 2px 2px;'>N/A</td>
<td style='width:150px;padding:2px 2px 2px 2px;'>" .$row['pick6_1']. "</td>
<td style='width:100px;padding:2px 2px 2px 2px;'><p class='view-bracket-link'><a href='system/displays/display-bracket-id-".$row['ID'].".php'>View Bracket</a></p></td>
</tr>"

;}

?>
</table>
</div>

thilss0o
03-09-2010, 12:07 AM
thanks, but sorry it still doesnt work, you can see for yourself http://tomhilsee.com/ipool/standings.php?action=sort&s=fullname&s=ASC

its the exact code you entered, should i be editing some line somewhere to fit my site? let me know, and thanks for your continued help

mlseim
03-09-2010, 12:32 AM
Oh jeez ... maybe it's as simple as missing single-quotes?

Change this line:

$query = mysql_query("SELECT * FROM TestTable ORDER by $csort $order");

To this (copy and paste this line over the old one):

$query = mysql_query("SELECT * FROM TestTable ORDER BY `$csort` $order");



.

thilss0o
03-09-2010, 01:42 AM
nah ORDER BY doesn't require the field to be in quotes anyway, but i tried it and still no luck.

do you want to mess around with the file on my server? i trust you enough lol, ill trust anyone. ill pm you the ftp info if you want to mess with it

thilss0o
03-09-2010, 01:43 AM
well if worst comes to worst i could have it link to new pages with different ORDER BY fields, but that would be pretty lame

mlseim
03-09-2010, 02:54 AM
PM me with your FTP account info ...

this is driving me nuts ... there has to be a reason, I just can't see it,
but if I experiment, I might get it. Create a temporary FTP login that you
can erase in a couple of days (then PM me).

thilss0o
03-09-2010, 03:10 AM
i just pmed you, not sure if it went through tho, my sent items are still empty, let me know if you got it

mlseim
03-09-2010, 04:38 AM
I found a few stupid mistakes on my part ... my bad.
It's OK now.

Make a safe copy of it in case you decide to do other things to it.

thilss0o
03-09-2010, 05:08 AM
yep, works great now, thanks!



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum