1

I'm having problem with binding a parameter in an ORDER BY clause within a PDO statement. "orderBy" doesn't seems to be passed to the query as results are not ordered as they're suppose to be. When I use a column name such as price in the query rather than a parameter, the results are sorted by that column. The code is:

class Products {
    const ORDER_BY_NAME='name';
    const ORDER_BY_PRICE_PER_UNIT='price_per_unit';
    const ORDER_BY_PRICE='price';
    const ORDER_BY_MINIMUM_QUANTITY='minimum_quantity';

    // function returns array of all products

    public function getAllProducts($orderBy) { 
        $db=Registry::getVariable('db');
        $pdoStatement=$db->prepare("SELECT name, minimum_quantity, price_per_unit, price, id FROM products ORDER BY :orderBy;");
        $pdoStatement->bindParam(':orderBy', $orderBy, PDO::PARAM_STR);
        $pdoStatement->execute();
        return $pdoStatement->fetchAll(PDO::FETCH_ASSOC);
    }
}

Later on I call:

 $products=new Products();

 echo $products->getAllProducts(Products::ORDER_BY_PRICE);

Why doesn't the :orderBy parameter seem to be used in query?

outis
  • 75,655
  • 22
  • 151
  • 221
spirytus
  • 10,726
  • 14
  • 61
  • 75

1 Answers1

7

Parameter binding is intended to be used with values. ORDER BY is actually followed by a field name, not a string.

nuqqsa
  • 4,511
  • 1
  • 25
  • 30
  • See http://stackoverflow.com/questions/2542410/how-do-i-set-order-by-params-using-prepared-pdo-statement – nuqqsa May 25 '10 at 19:15
  • Right, the problem here is that pdo is inserting the column name with quotes around it. – ryeguy May 25 '10 at 19:15