如何用准备好的陈述按时间排序？

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.

Answer 1

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];
    }
}