PDO with LEFT JOIN and ORDER BY

Question

I have this code

$query_search = $this->db->prepare("SELECT* FROM table1 LEFT JOIN table2 ON (table1.id=table2.id) WHERE table1.nome LIKE ?  ORDER BY ? DESC");
if($query_search->execute(array($cliente_procura."%",'table2.'.$ordem)))
{
   //code
}

But I'm having some problems with the ORDER BY clause. How can I use PDO and make sure my tables are in the order I want?

What are the problems you are getting? Any errors ? – Hugo Dozois Mar 23 '13 at 03:10 — Hugo Dozois, Mar 23 '13 at 03:10
You can't bind field names like that. – hjpotter92 Mar 23 '13 at 03:13 — hjpotter92, Mar 23 '13 at 03:13

score 2 · Accepted Answer · answered Mar 23 '13 at 03:18

Binding the column names is not possible with prepared statements.

You need to use the age-old method of binding them in strings like this:

$query_search = $this->db->prepare(" SELECT * 
        FROM table1 
        LEFT JOIN table2 
            ON (table1.id=table2.id) 
        WHERE table1.nome 
        LIKE ?  
        ORDER BY table2." . $ordem . " DESC");
if( $query_search->execute( array($cliente_procura."%") ) )

score 0 · Answer 2 · edited May 23 '17 at 10:30

From the Stackoverflow PDO tag wiki - https://stackoverflow.com/tags/pdo/info

PDO Prepared statements and identifiers.

PDO has no placeholder for identifiers, so a developer must manually format them. To properly format an identifier, follow these two rules:

*Enclose identifier in backticks.
*Escape backticks inside by doubling them.

see - Can PHP PDO Statements accept the table or column name as parameter?
or - Which tokens can be parameterized in PDO prepared statements?

see also this comment/example from the php manual - http://us3.php.net/manual/en/book.pdo.php#69304

PDO with LEFT JOIN and ORDER BY

2 Answers2