How to sort by time with prepared statements?

Question

I recently managed to stumble across a problem with PDO prepared statements in PHP. I am trying to fetch some results from a MySQL table and sort them by the time entered in a column called start_time.

Here's the code that I'm running now:

class DatabaseTable {
  private $table;
  private $pdo;
  public function __construct($pdo, $table) {
    $this->pdo = $pdo;
    $this->table = $table;
  }

  public function findMany2ordered($field, $value, $field2, $value2, $order, $direction) {
    $stmt = $this->pdo->prepare('SELECT * FROM ' . $this->table . ' WHERE '.$field.' = :value AND '.$field2.' = :value2 ORDER BY :order :direction' );
    $criteria = [
        'value' => $value,
        'value2' =>$value2,
        'order' =>$order,
        'direction' =>$direction
    ];
    //$stmt->execute($criteria);
    //return $stmt->fetch();

    if($stmt->execute($criteria)){
        return $stmt->fetchAll();
    }else{
        return $stmt->errorInfo()[2];
    }
  }
}

After this, I instantiate the class:

$slots = new DatabaseTable($pdo, 'slots');

and then I try and query the values and sort them by the time:

$timeline = $slots->findMany2ordered('user_id', $_SESSION['user_id'], 'slot_date', $_POST['timelinedate'], 'start_time', 'ASC');

then I have a foreach loop that iterates throught them and echo the results on the page:

foreach ($timeline as $slot){
            $taskDetails = $tasks->find('task_id', $slot['task_id']);
            //var_dump($taskDetails);
            echo'<div class="slot"';
                echo'<h3>'. $taskDetails['task_title']. '<h3>';
                echo'<span> start time:'.$slot['start_time'].' </span>';

            echo'</div>';
        }

The results are still unordered while printed on the page:

Has anyone stumbled across this before? Is there a solution?

Thanks in advance for you help.

PDO can't be used for parameterising table and column names. Please see this [post](http://stackoverflow.com/q/182287/2298301) for more info — Dhruv Saxena, Apr 12 '17 at 01:13
Can this 'order by' be done in PDO in other way then? I've tried changing to: $stmt = $this->pdo->prepare('SELECT * FROM ' . $this->table . ' WHERE '.$field.' = :value AND '.$field2.' = :value2 ORDER BY '.$order.' :direction' ) and now receive an SQL syntax error — liviu blidar, Apr 12 '17 at 01:28
Well, obviously it can't be done with PDO Parameters. Maybe this [answer](http://stackoverflow.com/a/16305689/2298301) in the post linked previously holds a clue for that. So, you'll have to perhaps make a hybrid method for yourself which uses PHP variables for table and column names and PDO Parameters for the values being passed to the query. Does that sound ok? — Dhruv Saxena, Apr 12 '17 at 01:41
That's what I've done by replacing 'ORDER BY :order' with ORDER BY '.$order.' . I now get an SQL syntax error: Check for the right syntax to use near ''ASC'' . — liviu blidar, Apr 12 '17 at 01:45
I have not seen binding that way. I've done $stmt->bindParam(":value", $value); and I've done ':value' => $value including the colon. — Vbudo, Apr 12 '17 at 01:51
PHP cares about lazy people as myself. Works with or without the colon. — liviu blidar, Apr 12 '17 at 02:34

score 1 · Accepted Answer · answered Apr 12 '17 at 01:53

Since PDO parameters can't be used for table and column names, it would be best to write your query string in such a way that table / column names and sort order are specified as PHP variables and any literals that may be used for the values are used as placeholders / bounded parameters.

Therefore, your function would look something like:

public function findMany2ordered($field, $value, $field2, $value2, $order, $direction) {
    $stmt = $this->pdo->prepare('SELECT * FROM  ' . $this->table 
               . ' WHERE '.$field.'  =  :value  '
               . ' AND   '.$field2.' =  :value2 '
               . ' ORDER BY '.$order .' '. $direction );
    $criteria = [
        ':value'  => $value,
        ':value2' => $value2
    ];

    if($stmt->execute($criteria)){
        return $stmt->fetchAll();
    }else{
        return $stmt->errorInfo()[2];
    }
}

Right so 'ASC' can't be bound either. This is probably where I got the sql syntax error. Many thanks — liviu blidar, Apr 12 '17 at 02:17

How to sort by time with prepared statements?

1 Answers1