Sorting Data…

@paradoxperfectJan 23.2007

Hello,

I have a table full of data. I want to sort it by ascending AND descending. I wrote a switch/case statement to do this. But I’m having problems with something. For example if I were to go to eBay and click on a title of the table to sort by (for example Price… and I don’t mean the drop down box) it would first sort in an ascending order (and show the down arrow) then, if I clicked on “price” again, it would sort descending. How can I accomplish the multiple options when the link/whatever is clicked?

Just some basic input would be appreciated. ?

to post a comment

PHP

11 Comments(s) _↴

@NightShift58Jan 23.2007 — #This should give the basic idea - as I would do it...

[code=php]<?php
 $getSORT = (isset($_GET['sort']) AND $_GET['sort'] <> "") ? $_GET['sort'] : "ASC";
 
 IF ($getSORT == "ASC") :
 $nextSORT = "DESC";
 ELSE :
 $nextSORT = "ASC";
 ENDIF;
 
 $sql = "SELECT * FROM mytable ORDER BY myfield $getSORT";
 $qry = mysql_query($sql) or die("SQL Error: $sql<br>" . mysql_error());
 
 print "<a href='{$_SERVER['PHP_SELF']}?sort=$nextSORT'>Sort $nextSORT</a>";
 
 WHILE ($row=mysql_fetch_array($qry)) :
 print $row['myfield1'] . "<br>";
 ENDWHILE;
 ?>[/code]

@paradoxperfectauthorJan 23.2007 — #But won't that only sort ONCE when I click on sort. I won't have any way of going "back" to the previous sort... know what I mean?

EDIT here's the switch/case statement:

[code=php]if(isset($_GET['sort'])){
 switch($_GET['sort']){
 case 0:
 $result = mysql_query("SELECT * FROM images ORDER BY pc_type, pc_tag_num ASC"); 
 break;
 case 1:
 $result = mysql_query("SELECT * FROM images ORDER BY pc_type ASC, pc_tag_num DESC");
 break;
 case 2:
 $result = mysql_query("SELECT * FROM images ORDER BY pc_type DESC, pc_tag_num ASC");
 break;
 case 3:
 $result = mysql_query("SELECT * FROM images ORDER BY pc_type DESC, pc_tag_num DESC");
 break;
 case 4:
 $result = mysql_query("SELECT * FROM images ORDER BY pc_model ASC");
 break;
 case 5:
 $result = mysql_query("SELECT * FROM images ORDER BY pc_model DESC");
 break;
 case 6:
 $result = mysql_query("SELECT * FROM images ORDER BY pc_img ASC");
 break;
 case 7:
 $result = mysql_query("SELECT * FROM images ORDER BY pc_img DESC");
 break;
 case 8:
 $result = mysql_query("SELECT * FROM images ORDER BY pc_owner ASC");
 break;
 case 9:
 $result = mysql_query("SELECT * FROM images ORDER BY pc_owner DESC");
 break;
 default: $result = mysql_query("SELECT * FROM images ORDER BY pc_type, pc_tag_num ASC");
 }
 }[/code]

I know how to do normal links just fine...but that's only to ONE sorting method per link...

@NightShift58Jan 23.2007 — #The example I posted gives you an example for one sort field. You can apply that basic logic to as many fields as you want.

The important thing is that you need to select from the table based on the GET information received and - since it only goes up or down - prepare the next GET for the opposite direction.

If you're going to sort on many different columns/fields, you're GET will have to consist of at least 2 arguments: field to sort and direction to sort.

@pcthugJan 23.2007 — #NightShift that code is [B][U]very[/U][/B] insecure, what happens if I pass [FONT="Courier New"]; DROP TABLE mytable;[/FONT] as my sort variable?

@paradoxperfectauthorJan 23.2007 — #NightShift, I see what you mean now... but as pcthug said, it is very insecure by allowing to pass anything into the query.

So, pcthug, how would you recommend doing it?

@NightShift58Jan 23.2007 — #what happens if I pass ; DROP TABLE mytable; as my sort variable?[/quote]Well, that would definitely solve the sorting problem, wouldn't it? ?

But the intent of my post was not to provide a turnkey application but the basic algorithm for the OP, dealing with two-way sorts.

Nonethless, "DROP TABLE" wouldn't make it past this:

[code=php]IF ($getSORT == "ASC") :
 $nextSORT = "DESC";
 ELSE :
 $nextSORT = "ASC";
 ENDIF; [/code]

No matter what comes in, it get translated to eiher ASC or DESC. My guess is that it would pass your safety test. But your point should be taken about the implicit dangers of allowing too much user control over GET and POST.

@paradoxperfectauthorJan 23.2007 — #Thanks for the input NightShift...and actually I get the idea now. I appreciate your help! ?

@NightShift58Jan 23.2007 — #You're welcome!

@pcthugJan 23.2007 — #Nonethless, "DROP TABLE" wouldn't make it past this:

[code=php]IF ($getSORT == "ASC") :
 $nextSORT = "DESC";
 ELSE :
 $nextSORT = "ASC";
 ENDIF; [/code]

[/QUOTE]Yes it would, the [font="courier new"]nextSORT[/font] variable would be assigned a value of [font="courier new"]ASC[/font] and the DROP TABLE statement would be passed right on. Pass something incriminating to your script and look at the output:

[code=php]<?php 
 $getSORT = (isset($_GET['sort']) AND $_GET['sort'] <> "") ? $_GET['sort'] : "ASC"; 
 
 IF ($getSORT == "ASC") : 
 $nextSORT = "DESC"; 
 ELSE : 
 $nextSORT = "ASC"; 
 ENDIF; 
 
 echo "SELECT * FROM mytable ORDER BY myfield $getSORT"; 
 [/code]

@NightShift58Jan 23.2007 — #You're right! I'm protected the next time around but not this round.

And it would lead to an unnecessary MySQL error, because MySQL doesn't like two queries in a single mysql_query().

To prevent that:

[code=php]IF ($getSORT == "ASC") :
 $nextSORT = "DESC";
 ELSEIF ($getSORT == "DESC") :
 $nextSORT = "ASC";
 ELSE :
 $getSORT  = "ASC";
 $nextSORT = "ASC";
 ENDIF; 
 [/code]

@pcthugJan 23.2007 — #Fortunately, if you use MySQL, the mysql_query() function does not permit query stacking, or executing multiple queries in a single function call. If you try to stack queries, the call fails.

However, other PHP database extensions, such as SQLite and PostgreSQL, happily perform stacked queries, executing all of the queries provided in one string and creating a serious security problem

Also in #PHP _↴

$_GET for including a file.getting the correct directory Writing data from a form to a table

Success!

Help @paradoxperfect spread the word by sharing this article on Twitter...

Tweet This

Sorting Data…

11 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

Sorting Data…

11 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

11 Comments(s) _↴

Also in #PHP _↴