How to perform database operations in yii2.

Build a yii\db\Query object to represent different parts (e.g. SELECT, FROM) of a SELECT SQL statement.Execute a query method (e.g. all()) of yii\db\Query to retrieve data from the database. This is used for directly using a query without any model relation.

$rows = (new \yii\db\Query())
    ->select(['id', 'email'])
    ->where(['last_name' => 'sharma'])

The Yii Query Builder provides an object-oriented way of writing SQL statements. It allows developers to use class methods and properties to specify individual parts of a SQL statement.

Creating a object of query builer like:

$connection = Yii::$app->db;

Raw SQL:

$connection->createCommand('update user set status = 1 where age > 30')->execute();

Raw SQL with placeholders (to prevent SQL injection):

$connection->createCommand('update user set status = :status where age > 30')->bindValue(':status','1')->execute();

update() method:

// update user set status = 1 where age > 30
$connection->createCommand()->update('user', ['status' => 1], 'age > 30')->execute();

batchInsert() records:

$connection->createCommand()->batchInsert('tbl_user', ['name', 'status'], [['A', 1], ['B', 0], ['C', 1], ])->execute();

This is the direct method of accessing the query using query builer but apart from this you can create per table model for more superior way to manage your relation with the table and query are also simple. links for db query using model click here.

No comments:

Post a Comment

Download the eBook of react js for beginners

ReactJS basically is an open-source JavaScript library which is used for building user interfaces specifically for single page applications...