Making dynamic table sortable

[Ian Rastall]

I'm doing a small project right now where I create a table from a
database. This is the code, which Dreamweaver, for the most part,
has written:

***

<?php
mysql_select_db ($database_tdre am, $tdream);
$query_rstdream = "SELECT * FROM albums ORDER BY `year` ASC";
$rstdream = mysql_query($qu ery_rstdream, $tdream) or die
(mysql_error()) ;
$row_rstdream = mysql_fetch_ass oc($rstdream);
$totalRows_rstd ream = mysql_num_rows( $rstdream);
?>

***

<table cellspacing="0" >
<thead>
<tr>
<td>Album</td>
<td>Year</td>
<td>Era</td>
<td>Type</td>
</tr>
</thead>
<tbody>
<?php do { ?>
<?php
$class = ($class == 'odd') ? 'even' : 'odd';
?>
<tr class="<?php echo $class ?>">
<td><?php echo $row_rstdream['Name']; ?></td>
<td><?php echo $row_rstdream['Year']; ?></td>
<td><?php echo $row_rstdream['Era']; ?></td>
<td><?php echo $row_rstdream['Type']; ?></td>
</tr>
<?php } while ($row_rstdream = mysql_fetch_ass oc($rstdream));
?>
</tbody>
</table>

***

Sorry for the length of the examples. I have a basic
understanding of what the code is doing, so I should be able to
modify it if I knew I was heading in the right direction. My
object is to make this table sortable, by clicking on the column
headers. I have an additional column in my database table which
is an auto_increment counter. Would anyone have some ideas or
code that could help me figure this out? I'm not sure how to
approach it, and was hoping that there was a simple answer to the
problem.

TIA
Ian
--
http://www.bookstacks.org/

[Geoff Berrow]

I noticed that Message-ID: <Xn************ *************** @130.133.1.4>
from Ian Rastall contained the following:

Thanks. I just tried it, but I ended up getting an error:

Unknown column 'Year,ID' in 'order clause'

Yes, because it would have to be either `Year`,`ID` or just Year, ID

Delete the back ticks from the ORDER BY bit and it will work.

Sheesh, you give up quick..LOL.

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[Ian Rastall]

In comp.lang.php Geoff Berrow wrote:

it would have to be either `Year`,`ID` or just Year, ID
I tried the second already, but hadn't given the first one a
shot. When I did, I got this error:

You have an error in your SQL syntax near 'Year`,`ID`` ASC' at
line 1

It's written in my document like:

"?order=`Year`, `ID`"
Delete the back ticks from the ORDER BY bit and it will work.

When I write it like:

"?order=Yea r, ID"

The error is:

Unknown column 'Year, ID' in 'order clause'

Ian
--
http://www.bookstacks.org/

[Ian Rastall]

In comp.lang.php Geoff Berrow wrote:

Delete the back ticks from the ORDER BY bit and it will work.
Oh, um, I did what this actually says, deleted the back ticks
from around the variable at the ORDER BY bit, and it works. Thank
you!
Sheesh, you give up quick..LOL.

Well, I'm trying not to take up too much of everyone's time.
There's another question, actually ... which is how to alternate
between ascending and descending sorts. I think possibly putting
an if/then statement at the top of the file, and a variable in
the ORDER BY section to take the place of ASC. I'm not sure,
though, what to do at the top to tell the browser to switch to
descending and back again.

The code is:

***

<?php
$order="Year";//declare the default value once, at the top.
if (isset($_GET['order'])){
$order=$_GET['order'];
}
mysql_select_db ($database_tdre am, $tdream);
$query_rstdream = "SELECT * FROM albums ORDER BY $order ASC";
$rstdream = mysql_query($qu ery_rstdream, $tdream) or die
(mysql_error()) ;
$row_rstdream = mysql_fetch_ass oc($rstdream);
$totalRows_rstd ream = mysql_num_rows( $rstdream);
?>

***

<table class="sort-table" id="table-1" cellspacing="0" >
<thead>
<tr>
<td><a href="<?php print $_SERVER['PHP_SELF']."?order=Nam e";
?>">Name</a></td>
<td><a href="<?php print $_SERVER['PHP_SELF']."?order=Year,I D";
?>">Year</a></td>
<td><a href="<?php print $_SERVER['PHP_SELF']."?order=Era ";
?>">Era</a></td>
<td><a href="<?php print $_SERVER['PHP_SELF']."?order=Typ e";
?>">Type</a></td>
</tr>
</thead>
<tbody>
<?php do { ?>
<?php
$class = ($class == 'odd') ? 'even' : 'odd';
?>
<tr class="<?php echo $class ?>">
<td><?php echo $row_rstdream['Name']; ?></td>
<td><?php echo $row_rstdream['Year']; ?></td>
<td><?php echo $row_rstdream['Era']; ?></td>
<td><?php echo $row_rstdream['Type']; ?></td>
</tr>
<?php } while ($row_rstdream = mysql_fetch_ass oc($rstdream));
?>
</tbody>
</table>

***

TIA
Ian
--
http://www.bookstacks.org/

[Geoff Berrow]

I noticed that Message-ID: <Xn************ *************** @130.133.1.4>
from Ian Rastall contained the following:

I think possibly putting
an if/then statement at the top of the file, and a variable in
the ORDER BY section to take the place of ASC. I'm not sure,
though, what to do at the top to tell the browser to switch to
descending and back again.

Well, to toggle it, ie to change the order each time you press the
column header will require the page to remember its state and as html is
stateless, this means passing the state back to the script somehow
possibly as a session variable.

Alternatively, why not have a pair of radio buttons and get the sort
order from them?

If you always want the same order each column you can pass the variable
in the link just like you did with the order.

<td><a href="<?php print
$_SERVER['PHP_SELF']."?order=Type&a mp;direction=DE SC";
?>">Type</a></td>

The direction of sort will then be contained in $_GET['direction']

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[Geoff Berrow]

I noticed that Message-ID: <Xn************ *************** @130.133.1.4>
from Ian Rastall contained the following:

Sheesh, you give up quick..LOL.

Well, I'm trying not to take up too much of everyone's time.

Just kidding.
--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[Ian Rastall]

In comp.lang.php Geoff Berrow wrote:

Alternatively, why not have a pair of radio buttons and get
the sort order from them?

Thanks for the advice, Geoff. I'll try using radio buttons, since
it would be a breeze to set the variable.

Ian
--
http://www.bookstacks.org/

[ramiro]

Try:

a href="<?=$PHP_S ELF;?>?order=<? =urlencode("Yea r DESC, ID ASC");?>"

By the way, the sorting problem might be the table field name, because
for mysql, the name of the field is case sensitive, let's say that the
name of your field is: Joe, if you use a SQL command like this: ORDER
BY joe ASC it gives you an error, because it should be like this: ORDER
BY Joe ASC.
Regards,
Ramiro Varandas Jr

[Sean]

On Sat, 29 Jan 2005 08:20:28 +0000, Geoff Berrow
<bl******@ckdog .co.uk> reverently intoned upon the aether:

<td>< a href="<?php print $_SERVER['PHP_SELF']."?order=Alb um ";
?>">Album</a></td>

I assume the column header is the same as your field header. Remember
it is case sensitive.

Then at the top of your script you do

$order="year";//declare the default value once, at the top.

if (isset($_GET['order'])){
$order=$_GET['order'];
}

finally include that variable in the SELECT

$query_rstdream = "SELECT * FROM albums ORDER BY `$order` ASC";

I would suggest passing $order to a set of if-else if statements.

$sort_order = 'Album';
if($order == 'Year'){
$sort_order = 'Year';
}else if($order == 'Era'){
$sort_order = 'Era';
}else if($order == 'Type'){
$sort_order = 'Type';
}

$query_rstdream = "SELECT * FROM albums ORDER BY $sort_order ASC";

or else a client can enter

http://wherever.net/somefile.php?order=Year; DROP TABLE albums;

or something similar.

This is called a "SQL Injection". Drop that into google and you will
find lots of info.

Also, other data validation techniques exist, you could use
preg_match(..) and log an error if the data is wrong. What the above
if-else does though is guarantee that you are never passing client
input to the database which is much simpler than validating the data.

Whatever you do, just getting it to work on good input, does not imply
it will do what you expect on bad input. You should also note that a
website on the net is wide open to the world.

enjoy,

Sean
"In the End, we will remember not the words of our enemies,
but the silence of our friends."

- Martin Luther King Jr. (1929-1968)

Photo Archive @ http://www.tearnet.com/Sean
Last Updated 29 Sept. 2004

[Dani CS]

Sean wrote:

On Sat, 29 Jan 2005 08:20:28 +0000, Geoff Berrow
<bl******@ckdog .co.uk> reverently intoned upon the aether:

<td>< a href="<?php print $_SERVER['PHP_SELF']."?order=Alb um ";
?>">Album</a></td>

I assume the column header is the same as your field header. Remember
it is case sensitive.

<snip>

I would suggest passing $order to a set of if-else if statements.

$sort_order = 'Album';
if($order == 'Year'){
$sort_order = 'Year';
}else if($order == 'Era'){
$sort_order = 'Era';
}else if($order == 'Type'){
$sort_order = 'Type';
}

I like to write it like this:

switch ($order) {
case "Year":
case "Era":
case "Type":
$sort_order = $order;
break;

default:
$sort_order = "Year"; // the preferred sort order
break;
}

This way one can see all the valid choices at a glance, and it's plain
easy to add a new valid choice.

$query_rstdream = "SELECT * FROM albums ORDER BY $sort_order ASC";

or else a client can enter

http://wherever.net/somefile.php?order=Year; DROP TABLE albums;

or something similar.

This is called a "SQL Injection". Drop that into google and you will
find lots of info.

Good advice.

[Geoff Berrow]

I noticed that Message-ID: <29************ *************** *****@4ax.com>
from Sean contained the following:

or else a client can enter

http://wherever.net/somefile.php?order=Year; DROP TABLE albums;

or something similar.

This is called a "SQL Injection". Drop that into google and you will
find lots of info.

I may be wrong but I thought that MySql would not allow this. Must do
some tests.
--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/