Home > Archive > PHP SQL > September 2004 > Sortable Links from MySQL row headers
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Sortable Links from MySQL row headers
|
|
|
| I've tried searching for this, but not sure I'm searching properly. This is
sort of a 2-part question.
What I would like to do is show all my data from a table with the data being
displayed under column headers representing field names. (That part is easy)
The part I'm having a tricky time with is making the columns sortable with a
link. I know how to do ("SELECT * FROM table ORDER BY col1, col2 ASC") I
just don't know how to use that query to create a hypertext link so a user
can sort that data by any number of fields.
So if I have fields of name, phnumber, address, city, state, zipcode - a
user could sort by any one of those fields at any time.
Part II: If I have a lot of users and wanted to display them in accordance
to zipcode, would I be able to do that with a single loop referring back to
a query? Or should I do ("SELECT * FROM table GROUP BY zipcode"), followed
by a loop that may then use (.... ORDER BY name) to sort under a specific
zipcode.
Example using name&zipcode:
Bob 11111 Neal 44444
Rob 22222 Squeal 22222
Nob 33333 Feal 11111
Throb 4444 Weal 33333
Then sorted I could display:
11111 22222 33333 44444
Bob Rob Nob Neal
Feal Squeal Weal Throb
Thanks people... some great stuff here has really helped.
-Scott
| |
|
|
For the first question...
When displaying the contents place links in the header like so...
<th><a href="showtable.php?sortcol=col1&sortdir=ASC">A</a> Col1 <a
href="showtable.php?sortcol=col1&sortdir=DESC">D</a></th>
<th><a href="showtable.php?sortcol=col2&sortdir=ASC">A</a> Col2 <a
href="showtable.php?sortcol=col2&sortdir=DESC">D</a></th>
This allows your user to specify the sort column and sort direction.
Build your query dynamically based on the user's selection...
// showtable.php
....
// [...should be validation here - don't trust user input...]
$strSortCol = isset( $_GET[ 'sortcol' ] ) ? $_GET[ 'sortcol' ] :
'col1';
$strSortDir = isset( $_GET[ 'sortdir' ] ) ? $_GET[ 'sortdir' ] :
'ASC';
$strQuery =
"SELECT col1, col2 FROM mytable ORDER BY $strSortCol
$strSortDir";
....
For the second question, if you want the names in a simple list...
SELECT zipcode, name ORDER BY zipcode, name
then process the results in a loop, and check when zipcode changes...
$strCurrentZipcode = '';
while( $datRow = mysql_fetch_array( $t_datResult, MYSQL_ASSOC ) )
{
if( $datRow[ 'zipcode' ] != $strCurrentZipcode )
{
print "\n"; // new zipcode
$strCurrentZipcode = $datRow[ 'zipcode' ];
print "$strCurrentZipcode\n";
}
print $datRow[ 'name' ] . "\n";
}
If you want the names as you showed them, with zipcodes horizontally
and names underneath, faster to save the output in an array and then
rearrange it for printing (rather than make two queries).
---
Steve
| |
|
| Steve - you da man!
I'll try and give that a shot tonight.
| |
|
| | For the second question, if you want the names in a simple list...
|
| SELECT zipcode, name ORDER BY zipcode, name
I'm stoopid. 8-)
I'm fighting with your example and can't get it to work properly...
(newbie's pay attention)
I was using:
-----------------------<SNIP-----------------------
$result = mysql_query("SELECT zipcode, name FROM table ORDER BY zipcode,
name");
..
..
..
while( $datRow = mysql_fetch_array( $t_datResult, MYSQL_ASSOC ) )
-----------------------<SNIP-----------------------
The key... $result and $t_datResult should be THE SAME
My mistake on that! But now it works like a beaut! Thanks Steve!
-Scott
|
|
|
|
|