I'm surprised your code appears to work. I suspect your test data doesn't have enough different values to actually show if the code is working. Your links are not being produced correctly (the php concatenation isn't right) and one is missing an = in it (which I just realized is the reason your used column Y/N values don't sort, which is yet another reason to validate the output your code produces.) You are also producing a variable $order_by that isn't being used in the code.
Some other operational problems -
1) You are using the current $sort_order value in all the links, this will produce an inconsistent initial sort order when moving between different sort columns (you should only put the sort order into the current sort column link.)
2) You are inverting the sort order before using it in the query, which will mean that anyone making a shortcut/bookmark to your page, that says the sort order will be one thing, will get the opposite result (you should only invert the sort order value before producing the link, the value in the link should be what you will get when you request the page the next time.)
3) You need to validate all external data before using it in your code. This is especially important since the external data is supplying a column name for the query, per the code you posted, which cannot be escaped or supplied via a prepared query. I suspect the switch/case statement and $order_by variable was intended to do this, but you are not using it in your code at all. If you are validating that a value is one of several possible choices, you should NOT write out program logic, a switch/case or if/else, that you need to edit every time you make a change to a value. Use an array and just test if the value is in the array.
4) Your date range filtering and pagination won't work in combination with this code unless you build ALL the links (date range, pagination, sorting) taking into account which existing $_GET parameters you include or exclude.
5) You should be producing the table heading dynamically, so that you don't repeat code, making it easier to add, remove, or change anything.
You need to define what you want your code to do, before writing your code. This is what I think you are trying to do -
1) When someone initially visits your page, what action do you want? They should get a default sort column (id) and a default sort order (asc).
2) How does someone know which sort column is the current one? I recommend highlighting it in some way.
3) If someone clicks on the current sort column link, the page should get requested with the sort order value that's in the link.
4) If someone clicks on a different sort column link, what do you want to happen? It should switch to that column in the ORDER BY term, but what sort order should it use? Shouldn't this always be the default ASC order?
5) If someone has selected a date range filter, it should be carried in the sorting links.
6) If there's enough data to paginate, the pagination links should carry the data range filter values and the sorting values.
See if the following (stand-a-lone, you can run it by itself) code does what you expect -
PHP Code:
<?php
// define data that the code uses to produce the dynamic part of the output and validate the dynamic input data
// list of table headings - the key is the column name (or filler info) and the value is the display legend
$headings = array('id'=>'Software ID','software_name'=>'Software Name','software_key'=>'Software Key','used'=>'Used','act'=>'Actions');
// permitted sort column choices - which of the headings are to be sort choices
$sort_choices = array('id','software_name','used');
// permitted order choices - used for validation
$sort_orders = array('ASC','DESC');
// the url to use in links
$url = ''; // empty => link to the current/same page
// condition/validate the sort column input
$sort_column = isset($_GET['sort']) && in_array($_GET['sort'],$sort_choices) ? $_GET['sort'] : 'id'; // default to 'id'
// condition/validate the sort order input
$sort_order = isset($_GET['sort_by']) && in_array($_GET['sort_by'],$sort_orders) ? $_GET['sort_by'] : 'ASC'; // default to 'ASC'
// produce the sql query statement
$query = "SELECT id, software_name, software_key, used
FROM software_keys
ORDER BY $sort_column $sort_order
LIMIT $start_from, $per_page";
// display the query to see if it contains what you expect
echo $query;
// invert the sort order choice, after it has been used in the sql query
$sort_order = $sort_order == 'ASC' ? 'DESC' : 'ASC';
echo "<table>";
echo "<tr>";
// produce the headings
foreach($headings as $key=>$label){
// is the heading a sort choice?
if(in_array($key,$sort_choices)){
// the heading is a sort choice
// the $get variable/http_build_query() logic are so that any existing $_GET parameters, such as your date range filter, will be carried in the links
$get = $_GET; // get a (fresh) copy of any existing $_GET parameters
$get['sort'] = $key; // set the sort value
// is this the current sort choice?
if($sort_column == $key){
// yes, this is the current sort choice - the heading should be emphasized in some way and use the inverted value for sort_by
$get['sort_by'] = $sort_order; // set the sort_by value
$qs = http_build_query($get,'','&'); // build the url query string - this urlencodes() the data and uses a proper & separator
echo "<th><a href='$url?$qs'>[$label]</a></th>"; // produce the link. [...] are added around the currently 'active' link.
} else {
// not the current sort choice - leave out the sort_by so that the code will use the default 'asc' value
unset($get['sort_by']); // remove any existing sort_by value
$qs = http_build_query($get,'','&'); // build the url query string - this urlencodes() the data and uses a proper & separator
echo "<th><a href='$url?$qs'>$label</a></th>"; // produce the link
}
} else {
// the heading is not a sort choice
echo "<th>$label</th>";
}
}
echo "</tr>";
echo "</table>";
Pay particular attention to the $get/http_build_query() logic in this code, since it lets you build links using existing $_GET parameters, without needing to hard-code the links with every possible value in them. It also builds a correctly formed query string part of the url with urlencoding for the data values. You would need to use this type of logic in your pagination code as well.
sort mysql column using php
Reply With Quote
Bookmarks