PDO + ORDER BY with prepared statements!

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
Hi there guys,

I've been working with PDO for a couple of days now.. and now I got
into this problem:

$order_by    = 'field1';
$order_type = 'DESC';

$query  = 'SELECT field1, field2, field3 ';
$query .= 'FROM table ';
$query .= 'ORDER BY :order_by :order_type ';

$stmt   = $dbh->prepare($query);
$stmt->bindParam(':order_by', $order_by, PDO::PARAM_STR);
$stmt->bindParam(':order_type', $order_type, PDO::PARAM_STR);

while($row = $stmt->fetch(PDO::FETCH_NUM))

When statement gets executed it returns true, although while I'm
listing query results they aren't sorted by field1 with specified

Can any one explain what I'm doing wrong?

Thanks in advance.

Re: PDO + ORDER BY with prepared statements!


Quoted text here. Click to load it

AFAIK, you cannot do this (but to be honest, haven't really tried, bur  
there's no such thing like a PDO::PARAM_COLUMN_NAME).

The reason your result is not sorted, but has no error, is this, the  
resulting query is:
    SELECT field1, field2, field3
    FROM table
    ORDER BY 'field1';
and NOT:
    SELECT field1, field2, field3
    FROM table
    ORDER BY field1;
... so the query isn't sorted by a field, but rather by a fixed string,  
which would result in all rows being exactly equal in the sort order, so  
the database will just send the rows as they appear in its storage.

In this case, contrary to unpredictable user supplied data, a columnname  
to sort by is easily whitelisted, so I'd use simple string building to get  
a correct statement.
Rik Wasmus

Site Timeline