Sort direction ASC/DESC not being handled using PDO (MySQL)

Question

I am trying to retrieve a set of of results by column using PDO (mysql). For some reason, the ASC/DESC mysql keywords aren't being processed.

Example Query:

"SELECT itemid, itemgroup, added_date FROM items WHERE itemgroup = 'book' ORDER BY added_date DESC LIMIT 5"

This is handled perfectly using only MySql. Within the code I set the sort direction literally, since PDO doesn't provide us a proper way to do this. I've read somewhere on here it isn't possible to use PDO->bindParam on a MySQL keyword.

$q = 'SELECT itemid, itemgroup FROM items WHERE itemgroup = :itemgroup ';
$q .= (isset($orderby)) ? 'ORDER BY :orderby DESC ' : '';

Now, whether i use ASC or DESC in my code, it seems to have no bearing on the ordering direction. Also, while trying to debug this I wasn't able to find a way to view the actual query being passed to DB, except for PDO->debugDumpParams, which is rather implicit. I'm rather new at PDO, am I making a junior mistake here? Debugging incorrectly?

Full code:

        $db = DB_MySQL::init();

        if (!isset($itemgroup)) {
            $sth = $db->prepare('SELECT itemid, itemgroup FROM items');
        } else {
            $sort = 'ASC'; // For when the damn thing works.

            $q = 'SELECT itemid, itemgroup FROM items WHERE itemgroup = :itemgroup ';
            $q .= (isset($orderby)) ? 'ORDER BY :orderby DESC ' : '';
            $q .= (isset($limit)) ? 'LIMIT :limit' : '';

            $sth = $db->prepare($q);
            $sth->bindParam(':itemgroup',$itemgroup,PDO::PARAM_STR);
            if (isset($orderby)) {$sth->bindParam(':orderby',$orderby,PDO::PARAM_STR);}
            if (isset($limit)) {$sth->bindValue(':limit',$limit,PDO::PARAM_INT);}
        }
        $sth->execute();
        echo $sth->debugDumpParams();
        $sth->setFetchMode(PDO::FETCH_ASSOC);

        if ($sth->rowCount() == 0) {
            throw new Exception ('No items found</br>' . var_dump($sth->errorInfo(). '</br>' . $sth->debugDumpParams()));
        } else {
            $result = $sth->fetchAll();
            foreach ($result as $key => $val) {
                $list[] = call_user_func(ucwords($itemgroup) . 'ItemMapper::fetchSpecificItem', $val['itemid']);
            } 
            return $list;
        }

Stumbled on this question by searching for a similar problem I had and found that if you use `$q .= (isset($orderby)) ? 'ORDER BY DESC ' :'';` it could work for you by taking out the `:orderby` — Funk Forty Niner, Sep 07 '13 at 17:47

score 1 · Answer 1 · answered Dec 12 '11 at 21:22

1

I think you have an error in your sql syntax, you should use ORDER BY :orderby DESC instead of DSC

answered Dec 12 '11 at 21:22

haynar

5,961
7
33
53

Ah typo while posting the code, edited. Problem still persists though – Ronald Mokens Dec 13 '11 at 13:12

score 0 · Answer 2 · answered Jan 10 '16 at 22:30

Every operator and identifier must be hardcoded in the prepared statement so you cannot use :orderby in $q .= (isset($orderby)) ? 'ORDER BY :orderby DESC ' : ''; and then the bind the value with $sth->bindParam(':orderby',$orderby,PDO::PARAM_STR).

You have to use variable interpolation in the prepared statement instead: $q .= (isset($orderby)) ? 'ORDER BY $orderby DESC ' : '';

Sort direction ASC/DESC not being handled using PDO (MySQL)

2 Answers2