1

I brought all values from the db and showed them on a page. Now, if I click the column it should sort ascending or descending.

How do I do this?

shamittomar
  • 46,210
  • 12
  • 74
  • 78
fddsf
  • 11
  • 2
  • Does your dataset fit into one page or it will be paginated? If so - do you want to sort whole dataset or one page only? – Your Common Sense Jan 28 '11 at 06:30
  • Please go through http://stackoverflow.com/search?q=sort+column+php and after that point out what you still have problems with. – Gordon Jan 28 '11 at 08:57
  • possible duplicate of [How can I sort by column header if clicked on and then sort in reverse order when clicked on again?](http://stackoverflow.com/questions/967535/how-can-i-sort-by-column-header-if-clicked-on-and-then-sort-in-reverse-order-when) – Gordon Jan 28 '11 at 08:58

6 Answers6

4

If you want to do this solely in PHP you will need to add parameters onto the query string in the URL.

www.example.com/table.php?col=mycolumn&sort=desc

That would be in the anchor tag for that column, then in table.php you would have the logic to handle sorting.

roflwaffle
  • 29,590
  • 21
  • 71
  • 94
  • 1
    Remember not to put these values into query directly to avoid SQL injection – Your Common Sense Jan 28 '11 at 06:26
  • Now ,i have bought them in anchor tag.How to pass them in query string – fddsf Jan 28 '11 at 06:28
  • @fddsf here i posted an example: http://stackoverflow.com/questions/2993027/in-php-when-submitting-strings-to-the-db-should-i-take-care-of-illegal-characters/2995163#2995163 there is only field name mentioned but for the direction the code would be all the same – Your Common Sense Jan 28 '11 at 06:31
  • @Schrapnel this can be easily avoided by checking for `desc` and `asc` only. Even better would be to use PDO to avoid it altogether. – Alfred Jan 28 '11 at 09:40
  • @Col It has everything to do here. you started comment with SQL-injections. The best way to prevent that is to use PDO! the query-string could be unsafe(even to XSS). – Alfred Jan 28 '11 at 09:51
  • @Alfred there is one little problem. I do understand what PDO is, while you never used it yourself but just repeat things you have read here without slightest understanding. Do yourself a favor, try to solve this problem using PDO in practice (as you have not enough knowledge to find right answer in theory) and report here. You'll be surprized :) – Your Common Sense Jan 28 '11 at 09:56
  • lol I know for sure that I am a better programmer then you!! You started with that stupid comment about sql-injections. It wasn't me!! Every PHP programmer who is accessing a database should use PDO. I will keep repeating it, if users don't use PDO in the snippets. Even Rasmus Ledorf(author PHP) tells you to do so => http://toys.lerdorf.com/archives/38-The-no-framework-PHP-MVC-framework.html – Alfred Jan 28 '11 at 09:59
  • @Alfred oh. You don't even understand what I am talking about. I am wasting my time talking to a baby sucker. – Your Common Sense Jan 28 '11 at 10:11
0

You'll be using JavaScript. I suggest sorttable.

sdleihssirhc
  • 42,000
  • 6
  • 53
  • 67
  • JS has very limited use, just for one-page sized datasets – Your Common Sense Jan 28 '11 at 06:26
  • @Col. What does "one-page" mean? That there would be so many `tr` elements to keep track of, JavaScript just isn't feasible for larger datasets? – sdleihssirhc Jan 28 '11 at 06:30
  • mean like Stackoverflow. there are many pages with questions and no browser can hold them all - so, for each another page another request to the server being made. and to sort **all** questions in another direction will require another request to the server as well – Your Common Sense Jan 28 '11 at 06:34
  • @Col. I still don't understand. Why are pages an issue at all? Wouldn't the table be a single `table` element on a single page? Are you talking about a *paginated* table with lots and lots of rows in it? – sdleihssirhc Jan 28 '11 at 06:36
0

Expanding on roflwaffle's post, you will want to use query string vars. From these build a string which you add into your query.

$querySort = array();
if (isset($_GET['colName'])) {
    $s = $_GET['colName'];
    $snext = '';
    switch ($s) {
        case '':     $snext = 'desc'; break;
        case 'desc': $snext = 'asc';  break;
        case 'asc':  $snext = ''; break;
        default:     $snext = '';
    }
    if (!empty ($snext)) {
        $querySort[] = "colName $snext";
    }
    $colName_link = "colName=$snext";
    $colName_text = $snext;
}

// Further column checks

if (count($querySort) == 0) {
    // Default sort if no sort is given.
    $querySort[] = "colName asc";
}

$sort = implode (', ', $querySort);

$query = mysql_query ("SELECT * FROM table ORDER BY $sort");

So given the following query string, 

www.example.com/table.php?colName=desc

the query would look like this

SELECT * FROM table ORDER BY colName desc LIMIT 0, 15

and the category link would look like this

echo "<a href='?$colName_link'>Column Name</a> <small>$colName_text</small>";
Chris Pennycuick
  • 337
  • 1
  • 4
0

The best way to do this is by using javascript(no need to refresh page). Yahoo!'s YUI3 has a very nice datatable component. I can not make up from your question if you got the complete dataset on your PHP page but I will assume this for now. You should have a look at YUI3's Column Sorting page to get you started.

Alfred
  • 60,935
  • 33
  • 147
  • 186
0

Please take a look at THIS link.Check the Example HERE. I use it in one of my project for the same purpose , hope it will help you too.

enam
  • 1,179
  • 1
  • 10
  • 24
-3

request: www.example.com/table.php?col=mycolumn&sort=desc

//may be some check , about the column is in  the table field
//sort is in the set of (asc , desc)

$q = "select * from mytable where mycond order by addslashes($_GET['mycolumn']) addslashes($_GET['sort'])";
Mark
  • 1
  • 1