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/

[Michael Vilain]

In article <Xn************ *************** @130.133.1.4>,
Ian Rastall <id*******@gmai l.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_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

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


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=Nam e ";
?>">

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

"?order=Yea r 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=Yea r 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=Yea r 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/