Prepared queries in yii2?

Question

Yii

Hello! There is this code:

$where = ''; 
       if(!empty($filter_time)) $where =" and date_created > now()-Interval <b>$filter_time </b>          minute ";
       if(!empty($filter_category))
       $where.= "and path LIKE '<b>$filter_category</b>%' "; 
      
       $sql = Yii::$app->db->createCommand("SELECT COUNT(*) FROM Pages
       JOIN categories ON categories = id_categories WHERE status = 1 $where")->queryScalar();

How to spell correctly bindValues().

Answer 1

Try like this:

$where = ''; 
if(!empty($filter_time)) $where =" and date_created > now()-Interval :filter_time minute ";
if(!empty($filter_category))
$where.= "and path LIKE ':filter_category%' "; 

$sql = Yii::$app->db->createCommand("SELECT COUNT(*) FROM Pages
JOIN categories ON categories = id_categories WHERE status = 1 $where")
  ->bindValue(':filter_time', $filter_time)
  ->bindValue(':filter_category', $filter_category)
  ->queryScalar();

Answer 2

Why are you working with the Command class? by doing this, you only create difficulties for yourself ... to create complex queries in yii there is a Query class that perfectly solves your problem, for example like this:

$query = new \yii\db\Query();
$query->from('Pages')
    ->leftJoin('categories', ['categories' => 'id_categories'])
    ->where(['status' => 1])
    ->andFilterWhere(['like', 'path', $filter_category]);
if (! empty($filter_time)) {
    $expression = new \yii\db\Expression('now() - interval :filter_time minute', [
        ':filter_time' => $filter_time
    ]);
    $query->andWhere(['>', 'date_created', $expression]);
}

Answer 3

Есть такой код
kill whoever wrote it.
Why use a framework if you don't use AR and its other goodies?
Example from the docs:

$post = Yii::$app->db->createCommand('SELECT * FROM post WHERE id=:id AND status=:status')
           ->bindValue(':id', $_GET['id'])
           ->bindValue(':status', 1)
           ->queryOne();

link to docs in Russian: https://nix-tips.ru/yii2-api-guides/guide-ru-db-da...
link to docs in English: www.yiiframework.com/doc-2.0/guide- db-dao.html