douzi8112
2017-04-12 01:05
浏览 26
已采纳

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

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:

screenshot of unordered items

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

Thanks in advance for you help.

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • douchuxun4162 2017-04-12 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];
        }
    }
    
    已采纳该答案
    打赏 评论

相关推荐 更多相似问题