I have a quick question, I am hoping it is an easy answer but if I wanted a query like the following:

SELECT * FROM items ORDER BY date ASC

Is there anyway to replace the order by information with prepared statement attributes? would either of the following work?

 SELECT * FROM items ORDER BY :column :order

OR

SELECT * FROM items ORDER BY ? ?

and then use bindParam or bindValue to assign those values? If I can't do that I know I can use conditionals to have multiple queries set and then use the correct query based on if the conditional is correct or not for example:

$value = filter_input(INPUT_POST, "value", FILITER_SANITIZE_STRING);
if($value = "date1"){
    $query = "SELECT * FROM items ORDER BY date ASC";
}elseif($value = "date2"){
    $query = "SELECT * FROM items ORDER BY date DESC";
 }

I would like to use the top if at all possible as it would be much easier. Is it possible to use prepared statements that way?

Is there anyway to replace the order by information with prepared statement attributes?

Unfortunately this cannot be done with current drivers. You can use a whitelist approach, instead of rewriting the query you do:

if(in_array($value, array('asc', 'desc'))
    $query = "SELECT * FROM `items` ORDER BY `date` $value";

Besides: date is a reserved word, so if used it needs backticks, as in the above example.

So I can not use prepared statements to bind column names correct? I can bind the ASC/desc?

Also I was just using Date as an example, I would actually have various columns that I could potentially search by which is why I wanted to parametrize it. I could use your approach and just make $query equal to different columns to sort by and nest it in a conditional correct?

So I can not use prepared statements to bind column names correct?

Exactly, you cannot.

I can bind the ASC/desc?

No, the bindParam() will accept only the PDO::PARAM_* constants defined here:

When you set:

$colname = 'created_at';
$stmt = $db->prepare("SELECT * FROM `items` ORDER BY :colname")
$stmt->bindParam(':colname', $colname, PDO::PARAM_STR);

The generated query will look like:

SELECT * FROM `items` ORDER BY 'created_at'

With quotes surrounding the :colname value, so the ORDER BY clause will not work. If you try to force it with a workaround, for example by setting it as an integer (by applying PDO::PARAM_INT) then you would get:

SELECT * FROM `items` ORDER BY 0

I could use your approach and just make $query equal to different columns to sort by and nest it in a conditional correct?

Yes, you can.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.