Problem using a PHP variable inside a MySQL query

PHP
1

I am using a PHP variable inside a MySQL query but only to ORDER BY. I usually just enclose the variable in single quotes and it works.

Like this:
$sorter = $_GET[‘sorter’];
mysql_select_db($database_mtt, $mtt);
$query_rs_leaders = "SELECT * FROM hfleaders ORDER BY ‘$sorter’ ";
$rs_leaders = mysql_query($query_rs_leaders, $mtt) or die(mysql_error());
$row_rs_leaders = mysql_fetch_assoc($rs_leaders);
$totalRows_rs_leaders = mysql_num_rows($rs_leaders);

But in this case it only works first time round - when the page reloads I get the error

*** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘’ at line 1 ***

I have tried using {$sorter} instead but the same thing happens - it works once then not al all.

Can anyone explain why because I just can’t figure it out.

2

try
$query_rs_leaders = “SELECT * FROM hfleaders ORDER BY '”.$sorter."’ ";

3

Interesting.
I don’t get the error now. But it doesn’t sort.
Hmmmm?

4

Keep in mind ASC or DESC sort ordering.

5

Make sure the variable $sorter has a value in it when the page refreshes.

You may want to write:


echo "<br>sql=" . $query_rs_leaders;
echo "<br>sorter=" . $sorter;

underneath the variable above to “see” what the query is returning.

Since it works on the first page load it should work on the refresh --depending on how the data is passed back to it. Maybe you are clicking on a link or button for form…I don’t know.

6

It would appear as if $_GET[‘sorter’] is not being passed to the page by your browser. You might want to check that out.

Also you really need a bit of validation going on there:


$allowedSortingColumns = array('date', 'enter', 'allowed', 'columns', 'in', 'here');
$sorter = 'ID';
$direction = 'ASC';
if(isset($_GET['sorter']) && in_array($_GET['sorter'], $allowedSortingColumns)){
    $sorter = $_GET['sorter'];
    if(isset($_GET['direction']) && strtoupper($_GET['direction']) == 'DESC'){
        $direction = 'DESC';
    }
}
mysql_select_db($database_mtt, $mtt);
$query_rs_leaders = "SELECT * FROM hfleaders ORDER BY '{$sorter}' {$direction}";
$rs_leaders = mysql_query($query_rs_leaders, $mtt) or die(mysql_error());
$row_rs_leaders = mysql_fetch_assoc($rs_leaders);
$totalRows_rs_leaders = mysql_num_rows($rs_leaders);
7

Isn’t the problem that the value for ORDER BY should not be quoted? It should be a column name. Backquotes (`) are okay, but not single quotes.