PHP/MySQL Sorting

[jordanste]

hey guys,

i have a mysql table of tasks with fields for id, name, comments, priority, status(incomplete, complete), etc.. i am using php to output a list of the tasks and their attributes. i would like to able to sort them by name, date, priority, status, etc... so this is an excerpt of what i have set up.

html select to determine sort key.

	<select name="s" id="s">
		<option value="">--</option>
		<option value="name-1">[ASC] Task Name</option>
		<option value="name-2">[DSC] Task Name</option>
		<option value="">--</option>
		<option value="priority_code-1">[ASC] Priority</option>
		<option value="priority_code-2">[DSC] Priority</option>
		<option value="status_code-1">[ASC] Status</option>
		<option value="status_code-2">[DSC] Status</option>
	</select>

and php to grab the sort key and inject into mysql query.

if(isset($_GET['s'])&&$_GET['s']!="") {
	$s=explode("-",$_GET['s']);
	$sortkey=$s[0];
	$order=$s[1];
	if($order==1) {
		$gettasks_resc=mysql_query("SELECT * FROM tasks ORDER BY '$sortkey' ASC");
	} elseif($order==2) {
		$gettasks_resc=mysql_query("SELECT * FROM tasks ORDER BY '$sortkey' DESC");
	} else {
		$gettasks_resc=mysql_query("SELECT * FROM tasks ORDER BY name ASC");
	}
} else {
	$gettasks_resc=mysql_query("SELECT * FROM tasks ORDER BY name ASC");
}

while it appears that i am using a valid sql query, it doesnt seem to work... does anyone see something that im not catching that would cause this not to work.

thanks for your help.

oh by the way, the value fields for each select option are identical to the column names in my table with the exception of the -1 or -2. for example

<option value="priority_code-1">

would explode to "priority_code" (the name of the priority column in my table) and "1" which is used to determine ASC or DESC in the sql query.

 

[angelwatt]

You shouldn't need the ' around the $sortkey, and when using that you should use ` (the one above the tab key) not ' (next to return key). Have you checked to see what the query looks like after the variable is processed to make sure it looks like what you want it to. When sorting by multiple columns it'll have a format like,

SELECT * FROM tbl ORDER BY col1 DESC, col2 DESC;

 

[SrWebDeveloper]

Explain, specifically, "it doesn't seem to work"?

Here is your same code saved as test.php with the proper form and some debugging code added, quote fixed (re: angelwatt) -- no actual query is made but you can see what query would have been run in the output:

    <form action="/test.php" method="get" name="test">
    
    <select name="s" id="s">
        <option value="">--</option>
        <option value="name-1">[ASC] Task Name</option>
        <option value="name-2">[DSC] Task Name</option>
        <option value="">--</option>
        <option value="priority_code-1">[ASC] Priority</option>
        <option value="priority_code-2">[DSC] Priority</option>
        <option value="status_code-1">[ASC] Status</option>
        <option value="status_code-2">[DSC] Status</option>
    </select>
    
    <input type="submit" value="Go" />
    
    
    </form>
    

<?php


    
if(isset($_GET['s'])&&$_GET['s']!="") {
    $s=explode("-",$_GET['s']);
    $sortkey=$s[0];
    $order=$s[1];
    
    print "sortkey=$sortkey, order=$order<br />";
    
    
    if($order==1) {
        $sql="SELECT * FROM tasks ORDER BY $sortkey ASC";
    } elseif($order==2) {
        $sql="SELECT * FROM tasks ORDER BY $sortkey DESC";
    } else {
        $sql="SELECT * FROM tasks ORDER BY name ASC";
    }
} else {
    $sql="SELECT * FROM tasks ORDER BY name ASC";
}  
    
        print "Query=$sql";
    
?>

Comments:

I select all options and checked the query, and each worked as expected. I suggest you run my code and you'll see what I mean. So this means either some other part of your code isn't working, i.e. database connectivity issue or the field names are different than what shows up in the query using your code. You didn't include the database schema so you need to check that, and I will also assume you know how to connect to a MySQL database, send a query, and check for error.

Beyond that, I don't mean to nit pick, but the whole method is rather unusual. Simply put, consider adding into your form another drop down that handles ascending vs. descending. Then you don't need to split the $_GET variable and less HTML as well.

On a side note, you are seriously prone to SQL injection issues, be sure to clean that up in your final version. The method you used to parse the $_GET to determine field name and sort order into the query string is extremely prone! This issue has been discussed in depth many other times on this forum.

-jim