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_tdream, $tdream);
$query_rstdream = "SELECT * FROM albums ORDER BY `year` ASC";
$rstdream = mysql_query($query_rstdream, $tdream) or die
(mysql_error());
$row_rstdream = mysql_fetch_assoc($rstdream);
$totalRows_rstdream = 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_assoc($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/

[Michael Vilain]

In article <Xn***************************@130.133.1.4>,
Ian Rastall <id*******@gmail.com> wrote:

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_tdream, $tdream);
$query_rstdream = "SELECT * FROM albums ORDER BY `year` ASC";
$rstdream = mysql_query($query_rstdream, $tdream) or die
(mysql_error());
$row_rstdream = mysql_fetch_assoc($rstdream);
$totalRows_rstdream = 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_assoc($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

Well, you could create a control which selects the criteria for the
ORDER BY modifier of your SELECT statement above.

--
DeeDee, don't press that button! DeeDee! NO! Dee...

[Geoff Berrow]

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

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.

Well not exactly simple. What you'll have to do is to make the table
headers into links which will call the page again, but pass a different
variable each time. e.g.

<td>< a href="<?php print $_SERVER['PHP_SELF']."?order=Album ";
?>">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";


Untested.

--
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 Michael Vilain wrote:

Well, you could create a control which selects the criteria
for the ORDER BY modifier of your SELECT statement above.

Thanks, Michael. I replaced the name of the column in the SELECT
statement with a variable, set the variable initially to 'ID', and
then changed

<td>Album</td>

to

<td onclick="<?php $sort_col = 'Name' ?>">Album</td>

which doesn't work. It isn't re-drawing the table. Unfortunately,
I'm not sure how to do that (re-draw the table). (I'm very much a
newbie at PHP.) Any insight would be appreciated.

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

[Ian Rastall]

In comp.lang.php Geoff Berrow wrote:

What you'll have to do is to make the table
headers into links which will call the page again, but pass a
different variable each time.

Thanks, I used the code, and it worked! I appreciate the help.

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

[Geoff Berrow]

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

<td onclick="<?php $sort_col = 'Name' ?>">Album</td>

It won't. onclick events only work on the client. In this case, you
need to pass the query back to the server. See my other post.

--
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 using the example you gave, I end up with another question. In
the following code:

<a href="<?php print $_SERVER['PHP_SELF']."?order=Name ";
?>">

how do I specify a secondary column to sort by? For the "Year"
column, I've tried

"?order=Year ID "

and

"?order='Year''ID' "

both of which caused PHP to think I was referring to one column
called Year ID. I'm just a bit lost as to the syntax.

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

[Geoff Berrow]

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

how do I specify a secondary column to sort by? For the "Year"
column, I've tried

"?order=Year ID "

and

"?order='Year''ID' "

try
?order=Year,ID

--
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:

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

how do I specify a secondary column to sort by? For the "Year"
column, I've tried

"?order=Year ID "

and

"?order='Year''ID' "

try
?order=Year,ID

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

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

I'm using PHP 4.2.3, mySQL 4.0.0-alpha-nt on Apache 1.3.27. I'm
scouting around for the answer myself, and I'll post it if I find
it.

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

[Ian Rastall]

In comp.lang.php Ian Rastall wrote:

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

Well my problem is solved, even though I didn't figure this out.
Apparently it's doing a secondary sort on the primary key anyway,
and I don't need to ask it to do so explicitly. Thanks for your
help!

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=Year, 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_tdream, $tdream);
$query_rstdream = "SELECT * FROM albums ORDER BY $order ASC";
$rstdream = mysql_query($query_rstdream, $tdream) or die
(mysql_error());
$row_rstdream = mysql_fetch_assoc($rstdream);
$totalRows_rstdream = mysql_num_rows($rstdream);
?>

***

<table class="sort-table" id="table-1" cellspacing="0">
<thead>
<tr>
<td><a href="<?php print $_SERVER['PHP_SELF']."?order=Name";
?>">Name</a></td>
<td><a href="<?php print $_SERVER['PHP_SELF']."?order=Year,ID";
?>">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=Type";
?>">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_assoc($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&amp;direction=DESC";
?>">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_SELF;?>?order=<?=urlencode("Year 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=Album ";
?>">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=Album ";
?>">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/

[Ian Rastall]

In comp.lang.php Sean wrote:

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.

Thanks, Sean. I'll work on that tonight.

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

[Ian Rastall]

In comp.lang.php Dani CS wrote:

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.

Hi Dani. I have it like this in the code, but I'm not sure how to
change this section:

***

<td><a href="<?php print $_SERVER['PHP_SELF']."?order=Name";
?>">Name</a></td>
<td><a href="<?php print $_SERVER['PHP_SELF']."?order=Year,ID";
?>">Year</a></td>
<td><a href="<?php print $_SERVER['PHP_SELF']."?order=Era,ID";
?>">Era</a></td>
<td><a href="<?php print $_SERVER['PHP_SELF']."?order=Type,ID";
?>">Type</a></td>

***

Perhaps,

<td><a href="<?php $order='Name'; print $_SERVER['PHP_SELF'];
?>">Name</a></td>

or something similar?

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

[Sean]

On Sun, 30 Jan 2005 00:17:01 +0000, Geoff Berrow
<bl******@ckdog.co.uk> reverently intoned upon the aether:

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.

I ran some quick tests (I am tired from landscaping the yard and
hungry, so I do not trust my mind at the moment) and could not pull
off multiple SQL queries in a mysql_query call(), but that does not
rule out the potential for UNIONS and other injection techniques.

The PHP manual recommends being defensive for MySQL nonetheless.

http://php.mirrors.powertrip.co.za/m...-injection.php

Beyond that, it is a good practice. You next project could be a
Java-SQL Server 2000, a C#-Oracle, a VB Script Postgres SQL, or ...
project so it is a good practice anyway.

That said, my tests did show that with PHP 4.3.10 (last time I
checked) and MySQL a few versions back would execute a query that ends
with a semicolon even though the manual says a query should not end
with a semicolon.

http://www.php.net/manual/en/function.mysql-query.php

And even if one is not open to a DROP, one is still open to a UNION or
an expanded WHERE in a SELECT and doing this on a login page could
open your site up wide.

enjoy,

Sean (who still has much to learn)
"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

[Sean]

On Sat, 29 Jan 2005 21:52:10 +0100, Dani CS
<co*****************@yahoo.es.quita-la-merluza> reverently intoned
upon the aether:

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.

I agree. Nonetheless, I habitually write if-else ifs. The one
change I would make here is to explicitly define the values rather
than using $order. It will make next to no performance difference,
and it guarantees that no client input is sent to the database. This
greatly simplifies the SQL injection avoidance problem.

While this is not an issue with this code yet, it leaves open more
complex interpretations in the future with less code modification.
i.e.,

$sort_order = 'Album';

if( isset( $_GET['order']) ){
$order = $_GET['order'];
switch ($order) {
case "'Album'":
$sort_order = 'Album';
break;
case "Year":
$sort_order = 'Year,Album';
break;
case "Era":
$sort_order = 'Era,Year,Album';
break;
case "Type":
$sort_order = 'Type,Year,Album';
break;
default:
$sort_order = 'Album'; // the default sort order
$short = 'Illegal GET Value in ' . __FILE__;
$long = $short . ". Cause is <b>$order</b> is invalid.";
LogMessage("Error", $short, $long);
break;
}
}
$sql = 'SELECT * FROM albums ORDER BY $sort_order"

Where LogMessage(...) will log the error to the database with
appropriate data dumps to debug it and send an email to the admin if
needed. This way any hacks are logged.

Beyond that, now we have a sub-ordering by year no matter what the
primary ordering is so that no silly database imposed sub-ordering is
forced on us.

This is neither right nor wrong, and sometimes it matters more to get
the product out the door. But maintainability matters too in the long
term. :o)

all the best,

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

[Geoff Berrow]

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

I may be wrong but I thought that MySql would not allow this. Must do
some tests.

I ran some quick tests (I am tired from landscaping the yard and
hungry, so I do not trust my mind at the moment) and could not pull
off multiple SQL queries in a mysql_query call(), but that does not
rule out the potential for UNIONS and other injection techniques.

You are heading for SQL territory in which I am unfamiliar. Still I'm
glad I was right about one thing :-)

It can be very confusing to self confessed newcomers to a language to as
a simple question and then be confronted with 20 lines of unfamiliar
code and I try to keep help as simple as possible. But you are right
about not trusting user input. Rather than if/else or switch I like to
use arrays. It enables you to set up all choices on one line, but can
look a bit confusing to someone new to PHP

$sort_order=array("Year"=>"Year","Type"=>"Type"... );

then use

$sql = "SELECT * FROM albums ORDER BY $sort_order [$_GET['order']]";

Can you see any problems with that?

--
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/

[Dani CS]

Ian Rastall wrote:

In comp.lang.php Dani CS wrote:

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.

Hi Dani. I have it like this in the code, but I'm not sure how to
change this section:

***

<td><a href="<?php print $_SERVER['PHP_SELF']."?order=Name";
?>">Name</a></td>
<td><a href="<?php print $_SERVER['PHP_SELF']."?order=Year,ID";
?>">Year</a></td>
<td><a href="<?php print $_SERVER['PHP_SELF']."?order=Era,ID";
?>">Era</a></td>
<td><a href="<?php print $_SERVER['PHP_SELF']."?order=Type,ID";
?>">Type</a></td>

This code is ok.
***

Perhaps,

<td><a href="<?php $order='Name'; print $_SERVER['PHP_SELF'];
?>">Name</a></td>

or something similar?
No, you don't need to change it.

What Sean and I were talking about was about the piece of code that
recognises the $order variable and prepares the sql query.

Ian

[Sean]

On Sun, 30 Jan 2005 10:01:01 +0000, Geoff Berrow
<bl******@ckdog.co.uk> reverently intoned upon the aether:

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

I may be wrong but I thought that MySql would not allow this. Must do
some tests.
I ran some quick tests (I am tired from landscaping the yard and
hungry, so I do not trust my mind at the moment) and could not pull
off multiple SQL queries in a mysql_query call(), but that does not
rule out the potential for UNIONS and other injection techniques.

You are heading for SQL territory in which I am unfamiliar. Still I'm
glad I was right about one thing :-)

It can be very confusing to self confessed newcomers to a language to as
a simple question and then be confronted with 20 lines of unfamiliar
code and I try to keep help as simple as possible.

The flip side of which results is websites filled with SQL injections
and XSS (cross site scripting) vulnerabilities all over the net.
Sometimes doing something so it functions is not enough. Especially
where credit card data or other sensitive information is handled.
But it is also unwise when little or no backups are done.

Outsourcing hosting to a commercial host should get you daily
incrementals and weekly full backups or something similar. But can
you expect this on a free account from your ISP or a friends system?
I would not.

I should admit I am biased and have some experience doing software
engineering on fault tolerant operations systems so I have had
checking every return value and doing something about any error state
beaten into me.
But you are right about not trusting user input.
I am fond of the "users are evil" statement. I know I am a nasty
user, I expect things to work the way they should, not the way the
manual I did not read says it works.
Rather than if/else or switch I like to
use arrays. It enables you to set up all choices on one line, but can
look a bit confusing to someone new to PHP

$sort_order=array("Year"=>"Year","Type"=>"Type"... );

then use

$sql = "SELECT * FROM albums ORDER BY $sort_order [$_GET['order']]";

Can you see any problems with that?

If the GET value is invalid, then you get:

$sql = "SELECT * FROM albums ORDER BY ";

Which is likely to be invalid SQL.
Instead, you might try:

$sort_order = array("Year" => " ORDER BY YEAR", ...)

So that the SQL is embedded in the array so that an invalid input
yields:

$sql = "SELECT * FROM albums";

And of course you lost the ability to catch an error if it occurs. I
guess one could check $sql for validity in your original code to
ameliorate this.

if( $sql === "SELECT * FROM albums ORDER BY " ){
// Could be a hacking attempt, could be a request that
// was corrupted in transit. Either way, log it so we can
// diagnose the situation later.
log_message("Illegal GET value $_GET[order]");
$sql = "SELECT * FROM albums ORDER BY Album";
}

But I think simply checking the input with a conditional before
constructing the query is easier and allows you to more clearly handle
error conditions. Shorter code is not necessarily clearer or easier
to maintain. And checking the input first, rather than the query
after the fact shows in the logic that it is the input (GET value)
that is the issue rather than the query. You might just thank
yourself for separating such logic six months down the road.

some thoughts,

Sean :o)

"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

[Sean]

On 30 Jan 2005 00:17:42 GMT, Ian Rastall <id*******@gmail.com>
reverently intoned upon the aether:

In comp.lang.php Sean wrote:

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.

Thanks, Sean. I'll work on that tonight.

Ian

Not a problem. Worrying about bad input from the start makes the
learning curve steeper, but it will pay off in the long term (no
exotic bugs to hunt down in 3 months when you have forgotten what you
were thinking when you coded a page).

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

[Ian Rastall]

In comp.lang.php Sean wrote:

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";

Hi Sean. This is what I'm using right now, although it doesn't
work. The program gets hung up on the default value for the
$sort_order variable, and can't see beyond it. When I click on a
column header, the sort order doesn't change (it's always the
default). Here is the code that I'm working with:

***

$sort_order = 'Year';
if($order == 'Album'){
$sort_order = 'Album';
}else if($order == 'Year') {
$sort_order = 'Year';
}else if($order == 'Era'){
$sort_order = 'Era';
}else if($order == 'Type'){
$sort_order = 'Type';
}
mysql_select_db($database_tdream, $tdream);
$query_rstdream = "SELECT * FROM albums ORDER BY $sort_order
ASC";
$rstdream = mysql_query($query_rstdream, $tdream) or die
(mysql_error());
$row_rstdream = mysql_fetch_assoc($rstdream);
$totalRows_rstdream = mysql_num_rows($rstdream);

***

<table class="sort-table" id="table-1" cellspacing="0">
<thead>
<tr>
<td><a href="<?php print $_SERVER['PHP_SELF']."?order=Album";
?>">Album</a></td>
<td><a href="<?php print $_SERVER['PHP_SELF']."?order=Year";
?>">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=Type";
?>">Type</a></td>
</tr>
</thead>
<tbody>
<?php do { ?>
<?php
$class = ($class == 'odd') ? 'even' : 'odd';
?>
<tr class="<?php echo $class ?>">
<td><?php echo $row_rstdream['Album']; ?></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_assoc($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:

When I click on a
column header, the sort order doesn't change (it's always the
default).

Add (at the top)

$order=$_GET['order'];

--
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:

Add (at the top)

$order=$_GET['order'];

Woo-hoo! Thank you, that worked just fine. Now I think I've finally
got this part of the page done.

--
Ian

http://www.bookstacks.org/

[Positive Contrarian]

I dunno whetrher you've considered this and rejected it, but there're a
number of free client-side JS scrpts that do the sort, alternating
directions too, completely client-side, and handling strings, numerics,
dates.

Google for "sorttable.js" . I'm using it for 150-row datasets, and
getting nice performance on a dinky machine in a number of app's.
Piece of cake!

[Ian Rastall]

In comp.lang.php Positive Contrarian wrote:

Google for "sorttable.js" .

That's what I was using before, actually. I want to make
everything PHP now, as that's what I'm learning. Also, the JS
sort table didn't do secondary sorts. For instance, in this table
there are a number of times when more than one album comes out in
the same year. In order to get the JS to sort correctly, I would
add little sort characters, like "a" "b" "c" etc., to the title
and make it invisible through display: none. At least with the
PHP that's done for me, since I enter them into the database in
order, and can sort by both, say, "Year" and "ID".

If you're using zebra rows in your JS sort table, you might try
using the PHP line that I found somewhere and added in:

<?php
$class = ($class == 'odd') ? 'even' : 'odd';
?>
<tr class="<?php echo $class ?>">

then all you need is the CSS class for .even and you're set.

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

[Tony Marston]

Have a look at
http://www.tonymarston.co.uk/php-mys...faq.html#faq07 to see
how I have implemented column sorting without using javascript.

--
Tony Marston

http://www.tonymarston.net
"Ian Rastall" <id*******@gmail.com> wrote in message
news:Xn***************************@130.133.1.4...

In comp.lang.php Positive Contrarian wrote:

Google for "sorttable.js" .

That's what I was using before, actually. I want to make
everything PHP now, as that's what I'm learning. Also, the JS
sort table didn't do secondary sorts. For instance, in this table
there are a number of times when more than one album comes out in
the same year. In order to get the JS to sort correctly, I would
add little sort characters, like "a" "b" "c" etc., to the title
and make it invisible through display: none. At least with the
PHP that's done for me, since I enter them into the database in
order, and can sort by both, say, "Year" and "ID".

If you're using zebra rows in your JS sort table, you might try
using the PHP line that I found somewhere and added in:

<?php
$class = ($class == 'odd') ? 'even' : 'odd';
?>
<tr class="<?php echo $class ?>">

then all you need is the CSS class for .even and you're set.

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

[Geoff Berrow]

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

If you're using zebra rows in your JS sort table, you might try
using the PHP line that I found somewhere and added in:

<?php
$class = ($class == 'odd') ? 'even' : 'odd';

Ahh... I'd spotted that and didn't think it looked like Dreamweaver
generated code. Nice.

--
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/