...

View Full Version : Sort by hyperlinks ?



Badfish
01-08-2006, 07:56 AM
Is there some way that a query can be made so it will sort by hyperlinks in a table header ?

Ive noticed this a lot on other sites like this but have no idea where to start.

pagename.php?&sort=name&order=desc

firepages
01-08-2006, 09:04 AM
alter your SQL based on the url data..

$sql = "
SELECT blah
FROM table
ORDER BY '{$_GET['sort']}'
".strtoupper($_GET['order']);

ok DONT do exactly that, you have to clean $_GET['sort'] etc to make sure noone has put any dodgy code in there , but thems the basics.

Badfish
01-08-2006, 09:45 AM
alter your SQL based on the url data..

$sql = "
SELECT blah
FROM table
ORDER BY '{$_GET['sort']}'
".strtoupper($_GET['order']);

ok DONT do exactly that, you have to clean $_GET['sort'] etc to make sure noone has put any dodgy code in there , but thems the basics.


Ok thanks I'll give it a try although I dont understand this part ".strtoupper"

Velox Letum
01-08-2006, 10:08 AM
It's more for semantics. It'll change asc or deSc into ASC and DESC respectively so that the query looks correct. It'd work either way, but its always good form to create queries that are properly formatted. Same goes for code.

Badfish
01-08-2006, 10:10 AM
when I use the full piece of code like you suggested.

ORDER BY '{$_GET['sort']}'
".strtoupper($_GET['order']);

I get a parse error ?

but if I use just this part on the end of the query
ORDER BY '{$_GET['sort']}'

and use a link like

page.php?sort=fieldname

this then works but only in desc order ?

Badfish
01-08-2006, 10:13 AM
thanks Velox could you give me an example without using the strtoupper function ?

As long as there not a Psycho Semantic it's ok with me

Velox Letum
01-08-2006, 10:14 AM
It isn't his query, I just ran it through PHP and it didn't throw any errors. Here's the query formatted in my style...try it.


$sql = 'SELECT blah FROM table ORDER BY "' . $_GET['sort'] . '" ' . strtoupper($_GET['order']);

Badfish
01-08-2006, 10:22 AM
No its still giving me parse errors and it must be me causing them :rolleyes:

here is the line with the query im trying to use it on.


$result = mysql_query("SELECT oamembers.surname, oamembers.firstname, oamembers.nickname, oamembers.email, DATE_FORMAT(oamembers.signup, '%d %b %Y') AS signup, oamembers.mobile, oamembers.bay, oamembers.aisle, oamembers.seat, oamembers.oa_id, oamembers.club_member, oamembers.seasontix, oamembers.anchor FROM oamembers ORDER BY oamembers.surname, oamembers.firstname");

Velox Letum
01-08-2006, 10:34 AM
$result = mysql_query('SELECT oamembers.surname, oamembers.firstname, oamembers.nickname, oamembers.email, DATE_FORMAT(oamembers.signup, "%d %b %Y") AS signup, oamembers.mobile, oamembers.bay, oamembers.aisle, oamembers.seat, oamembers.oa_id, oamembers.club_member, oamembers.seasontix, oamembers.anchor FROM oamembers ORDER BY "' . $_GET['sort'] . '" ' . strtoupper($_GET['order']));

Badfish
01-08-2006, 10:53 AM
Thanks Velox your a champ :thumbsup:

Just a final question though it is possible to sort by to fields using this method?.

etc
page.php?sort=fieldname1,fieldname2&order=desc

is this correct ?

Velox Letum
01-08-2006, 11:50 AM
All but the last field would be sorted as ascending, but it'd work. If you wanted pairs, you could explode the variables by , and use them in a foreach to generate the ORDER BY.

Badfish
01-08-2006, 02:06 PM
Thanks Velox

I also tried using that end piece of code on other pages to do the same thing and are getting errors ? all I tried to do was paste this piece of code on the end of the query.


ORDER BY "' . $_GET['sort'] . '" ' . strtoupper($_GET['order']));

and then replaced the " at the start of the line with a ' like you did with the copy you edited for me so I must be missing something there or was that for use with only that query.

Element
01-08-2006, 08:34 PM
there can't already be those operators in the query. the query must be formated like:

SELECT * FROM table WHERE whatever=whatever ORDER BY whatever ASC

See how that is formed? Check that against your query and make sure the operators are in that order. Operators should be capital.

firepages
01-09-2006, 03:29 AM
Velox is right , the uppercasing of DESC etc is just semantics and not required in MySQL and I think my original snippet was missing a space :o

just posting to note that if you are only selecting from 1 table then there is no reason to prefix the fieldnames (even in a join you don't always need them)


<?php
$sort = mysql_real_escape_string($_GET['sort']);
$order='';
if(strtolower($_GET['order'])=='desc'){
$order='DESC';
}
$result = mysql_query("
SELECT surname, firstname, nickname, email,
DATE_FORMAT(signup, '%d %b %Y') AS signup,
mobile, bay, aisle, seat, oa_id, club_member, seasontix, anchor
FROM oamembers
ORDER BY $sort
$order
");
?>

Badfish
01-17-2006, 07:51 AM
Velox is right , the uppercasing of DESC etc is just semantics and not required in MySQL and I think my original snippet was missing a space :o

just posting to note that if you are only selecting from 1 table then there is no reason to prefix the fieldnames (even in a join you don't always need them)


<?php
$sort = mysql_real_escape_string($_GET['sort']);
$order='';
if(strtolower($_GET['order'])=='desc'){
$order='DESC';
}
$result = mysql_query("
SELECT surname, firstname, nickname, email,
DATE_FORMAT(signup, '%d %b %Y') AS signup,
mobile, bay, aisle, seat, oa_id, club_member, seasontix, anchor
FROM oamembers
ORDER BY $sort
$order
");
?>



Ive tested your piece of code in a few other pages Im usingand it works fine and I want to know if its possible using this snippet to sort by two fields instead of just one?..

example using one field to sort by
php?sort=bay&order=desc

how could I use this so it would sort by two fields for example the field bay then sort by aisle as well.
so if I had 5 records like below which were sorted just by the bay field

bay 1 aisle 3
bay 1 aisle 2
bay 1 aisle 1
bay 1 aisle 4
bay 1 aisle 2

how could I use this to make them sort by the bay field first then the aisle field ?

bay 1 aisle 1
bay 1 aisle 2
bay 1 aisle 3
bay 1 aisle 4
bay 1 aisle 5

I have tried using the link below without any success.

php?sort=bay,aisle&order=desc



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum