[FIXED] How the query used in Yii2

Issue

Can you help me. i want to like example but on my source code it becomes empty emty. What is the query or source code in my project? thank You.

in Controller

public function actionView($id)
{
    $con = Yii::$app->db;
    $sql = $con->createCommand("SELECT * FROM track where collecting_id=$id ORDER BY collecting_id desc");
    $posts = $sql->query();
    return $this->render('view', [
        'model' => $this->findModel($id),
        'posts' => $posts,
    ]);
}

in View

<div class="timeline__items">
<?php
foreach($posts as $row)
{
?>
<div class="timeline__item">
   <div class="timeline__content">
   <h2><?php echo $row['status']; ?></h2>
   <p><?php echo $row['tanggal']; ?></p>
</div>
</div>
<?php
}
?>
</div>

if the $id on the query is replaced with 'PMUEI' the result is result

Use ActiveDataProvider

public function actionView($id)
    {
        $model = $this->findModel($id);
        $hotel = Track::find()->where(['collecting_id' => $model->collecting_id]);
        $posts = new ActiveDataProvider([
            'query' => $hotel,
        ]);
        // $con = Yii::$app->db;
        // $sql = $con->createCommand(
        // "SELECT * FROM track where collecting_id=:collecting_id ORDER BY collecting_id desc",
        // [':collecting_id' => '$id']
        // );
        // $posts = $sql->queryAll();
        return $this->render(
        'view', [
            'model' => $this->findModel($id),
            'posts' => $posts,
        ]);
    }

the result is error .

Solution

Its always good to bind parameters when comparing columns with any such input that is provided by the user or can be edited by the user as in your case is the $id that you are passing as a parameter to the actionView(). And then you need to use queryAll() or queryOne() in case you want multiple or single rows returned.

So you should change your query to the following

public function actionView($id)
{
    $con = Yii::$app->db;
    $sql = $con->createCommand(
        "SELECT * FROM track where collecting_id=:collecting_id ORDER BY collecting_id desc",
        [':collecting_id' => $id]
    );
    $posts = $sql->queryAll();
    return $this->render(
        'view', [
            'model' => $this->findModel($id),
            'posts' => $posts,
        ]
    );
}

---

Apart from the above, you should use ActiveRecord. Active Record provides an object-oriented interface for accessing and manipulating data stored in databases. Read Here to make your life easier.

Answered By - Muhammad Omer Aslam