sort mysql column using php

Hi

I have put a sort feature using php and works perfect on all the columns apart from one column, It has values of Y and N and would be good if I can get it so all the Y's are together and the N's are together and the Y or N appear at the top first, hope that makes sense

below is the code I have

PHP Code:

$sort = (isset($_GET['sort'])) ? $_GET['sort'] : 'id'; $sort_order = 'asc'; if(isset($_GET['sort_by'])) { if($_GET['sort_by'] == 'asc') { $sort_order = 'desc'; } else { $sort_order = 'asc'; } } switch($sort) { case 'id': $order_by = 'id'; break; case 'software_name': $order_by = 'software_name'; break; case 'used': $order_by = 'used'; break; } // get the records from the database if ($result = $mysqli->query("SELECT id, software_name, software_key, used FROM software_keys ORDER BY $sort $sort_order LIMIT $start_from, $per_page")) echo "<tr> <th><a href='view-software-keys.php?sort=id&sort_by='.$sort_order.''>Software ID</a></th> <th><a href='view-software-keys.php?sort=software_name&sort_by'.$sort_order.''>Software Name</a></th> <th>Software Key</th> <th><a href='view-software-keys.php?sort=used&sort_by='.$sort_order.''>Used</a></th> <th>Actions</th> </tr>";

Sorry

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.

Hi DyDr

Thank you for the script, I put the script in but got the following error displayed

Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, string given in /home/sites/it-doneright.co.uk/public_html/admin/software-keys/view-software-keys.php on line 149

On line 149, I have the following

Code:

$total_records = mysqli_num_rows($query);

The query echoed looks ok on the page

SELECT id, software_name, software_key, used FROM software_keys ORDER BY id ASC LIMIT 0, 20

The people that take the time to read your threads and figure out what you are trying to do, do want to help you. We like solving programming problems and we would like you to learn something about programming so that you can do it on your own.

However, repeatedly, throwing together code without looking at it, not learning what your code does and not learning by fixing mistakes in it, then dumping errors on forums when it doesn't work and expect someone else to tell you what the error means and what to do to fix the problem, is not programming.

I can tell from the error that you likely didn't execute the query, but that's just a guess since you didn't post the relevant code. The line of code you did post is just where a follow-on error occurred, when php couldn't run a statement because you passed it a parameter that didn't contain an expected value, which if you would read the error, tells you exactly that. It's your job to figure out why the parameter you passed it is a string instead of a mysqli_result. Did you even try?

With all your threads on all the forums with all your different usernames over all these years, I've got to ask, do you understand the basic process of running an sql query? Here it is, four steps -

1. Make a connection to the database server, with error handling so that you know the connection worked, and won't try to use the connection if it didn't work. Make only ONE connection to run all the queries on your web page.

2. Form an sql query statement.

If you are using a prepared query with input parameters, you would have additional steps of preparing the query and binding the input parameters.

3. Run the sql query statement, with error handling so that you know the query worked, and won't try to use the result from the query if it didn't work (which is a different problem from the error you are getting now, which is why reading the error message is important.)

4. Use the result from the sql query statement, i.e. test the number of rows the query matched (or affected) and/or fetch the data that the query matched.

Check if your code is doing each of these four steps.

There's actually a web term for you - 'help vampire'. You sink your teeth into a help forum and hold on as long as you can, until the free help runs out. Don't be a help vampire. Do your part to learn the basic steps of what you are doing, learn the meaning of the php statements you are using, look at your code and errors and try to fix the problems. Fixing problems is how learning takes place. You learn what you did wrong so that you know how to do it correctly the next time. Getting others to tell you what to do to fix a problem, so that you can go onto the next thing you are trying to do, doesn't teach you anything.