Problems with Sorting on my sql query

Question

function getGamesBySearch($criteria) {
try {
        $sortedBy = 'Name';
        $db = getDBConnection();
        $query = "SELECT * 
                    FROM game
                    WHERE Name LIKE :criteria
                    ORDER BY :sortBy DESC";
        $statement = $db->prepare($query);
        $statement->bindValue(':criteria', '%'.$criteria.'%');
        $statement->bindValue(':sortBy',$sortedBy);
        $statement->execute();
        $results = $statement->fetchAll();
        $statement->closeCursor();
        return $results;           // Assoc Array of Rows
    } catch (PDOException $e) {
        $errorMessage = $e->getMessage();
        include '../view/errorPage.php';
        die;
    }       
}

For some reason, my associative array always comes back in my GameID order, when I need it to be in the order of the Name? This is only bonus for the class I am taking, but help would be appreciated.

http://stackoverflow.com/questions/15990857/reference-frequently-asked-questions-about-pdo#15991422 — Your Common Sense, Apr 21 '13 at 20:03

score 1 · Accepted Answer · answered Apr 21 '13 at 20:04

1

You cannot use placeholders in SQL statements for column names.

You can only use them for values.

Your query is sorting by a literal string - which is the same for all rows.

You are effectively doing

ORDER BY 'Name'

instead of

ORDER BY Name

answered Apr 21 '13 at 20:04

fisharebest

1,300
10
16

Ok, This is a quick change to see if i could do that, how would I pass in a sortValue then? Or will i need to set a default query for every sort type. – user2305203 Apr 21 '13 at 20:07
You would need to embed the column name into the query string. e.g. "... ORDER BY `$Name` ...". Make sure you validate $Name very carefully - e.g. by whitelisting it. – fisharebest Apr 21 '13 at 20:09

Problems with Sorting on my sql query

1 Answers1