PDO + ORDER BY with prepared statements!

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);
$stmt->execute();

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


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

Can any one explain what I'm doing wrong?

Thanks in advance.

On Thu, 28 Feb 2008 13:06:39 +0100, João Morais <jcsmorais@gmail.com>
wrote:

> 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);
> $stmt->execute();
>
> while($row = $stmt->fetch(PDO::FETCH_NUM))
> method_to_display($row);
>
>
> When statement gets executed it returns true, although while I'm
> listing query results they aren't sorted by field1 with specified
> order_type.
>
> Can any one explain what I'm doing wrong?

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