sorting order parameter

Question

I am trying to pass variables to the order by snippet of a DB::select(DB::raw('')) by doing something like this:

 ....order by :sort :order;'), array('sort' => $sort, 'order' => $order));

Is there any reason why I'm getting a syntax error on :order?

If I replace :sort with a column with say qtSTDDEV, error on :order still persist

Code

$sort  = 'qtSTDDEV';
$order = 'order';

DB::select(DB::raw('SELECT mid         as mID,
   round((x.qty_sum / x.qty_count), 5) as qtAVG,
   round(x.qty_stddev, 5)              as qtSTDDEV,
   x.qty_count                         as qtCOUNT,
   round((x.rel_sum / x.rel_count), 5) as relAVG,
   round(x.rel_stddev, 5)              as relSTDDEV,
   x.rel_count                         as relCOUNT,
FROM (SELECT mid,
         SUM( mt = 'qt' )   as qty_count,
         SUM(CASE WHEN mt = 'qt' THEN rt END)  as qty_sum,
         STD(CASE WHEN mt = 'qt' THEN rt END)  as qty_stddev,
         SUM( mt = 'rel' ) as rel_count,
         SUM(CASE WHEN mt = 'rel' THEN rel END) as rel_sum,
         STD(CASE WHEN mt = 'rel' THEN rel END) as rel_stddev
  FROM t_r r
right join t_m t on t.mid = r.mid
  GROUP BY mid
 ) x
right join m_k m on m.mid = x.mid
right join k_d k on k.kid = m.kid
order by :sort :order;'), array('sort' => $sort, 'order' => $order));

Can you post the whole line? What you posted definitely isn't valid PHP syntax — DevK, Dec 15 '18 at 23:19
Possible duplicate of [Can PHP PDO Statements accept the table or column name as parameter?](https://stackoverflow.com/questions/182287/can-php-pdo-statements-accept-the-table-or-column-name-as-parameter) — miken32, Dec 15 '18 at 23:50
If I replace `:sort` with a column name say with `qtSTDDEV`, error on `:order` still persist — hello, Dec 15 '18 at 23:51

score 2 · Accepted Answer · answered Dec 16 '18 at 00:03

The code you posted is not valid PHP, hence it is hard to provide a 100% sure answer.

However, what is for sure is that column names cannot be passed a bind parameters. You need to generated that part of the SQL query from the PHP code.

Also, column names in the order by clause need to be separated with a comma.

I guess you want to replace this :

order by :sort :order;'), array('sort' => $sort, 'order' => $order));

with :

order by ' . $sort . ', ' . $order . ';'));

sorting order parameter

1 Answers1