Yii Framework 2 : SQL Select Query

Note

//Before use the Query Class, We have to add the following namespace

use yii\db\Query;
$connection = \Yii::$app->db;

findBySql

Sample 1:

$sql = 'SELECT * FROM tbl_user';
$model = User::findBySql($sql)->all();
    

Sample 2:

$sql = 'SELECT * FROM tbl_user';
$model = User::findBySql($sql)->one();
    

queryAll

$model = $connection->createCommand('SELECT * FROM tbl_user');
$users = $model->queryAll();

queryOne

$model = $connection->createCommand("SELECT * FROM tbl_user where userid=$id");
$users = $model->queryOne();
    

queryColumn

$model = $connection->createCommand('SELECT username FROM tbl_user');
$users = $model->queryColumn();
    

queryScalar

$model = $connection->createCommand('SELECT COUNT(*) FROM tbl_user');
$users_count = $model->queryScalar();
    

Query With Prepared Statements()

$user = $connection
	->createCommand('SELECT * FROM tbl_user WHERE userid=:userid');
$user->bindValue(':userid', $_GET['userid']);
$model = $user->query();
    

Query Class

Sample 1:

$query = new Query;
$query->select('userid, username')
    ->from('tbl_user')
    ->limit(10);
$command = $query->createCommand();
$data = $command->queryAll();
    

Sample 2:

$query = new Query;
$columns = [];
$columns[] = 'userid';
$columns[] = 'username';

$query->select($columns)
    ->from('tbl_user')
    ->limit(10);
$command = $query->createCommand();
$data = $command->queryAll();