Yii Framework 2.0 Tutorial

Yii Framework 2.0 Select Query : SQL Queries

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();	
  • 01011

    When using Query class, it is not necessarily to call createCommand() by yourself, $query->all() do it for you.

  • Luis M

    Hi, i need help As complete a combobox with data from the db?

    • m bala

      Try this
      <?php
      $countries=Country::find()->all();
      $listData=ArrayHelper::map($countries,’code’,’name’);
      echo $form->field($model, ‘name’)->dropDownList($listData, [‘prompt’=>’Choose…’]);
      ?>

      • Luis M

        ty im use field($model, ‘atributename’)->dropDownList(ArrayHelper::map(model::find()->all(), ‘valatributemodel1’, ‘valatributemodel2’)) ?>

  • Saravanan Samidurai

    how to print values

  • Vipin Kumar

    aaaaaaaa

  • shiv

    hi guys
    i am in problome fetch two or more tables data in a single page in yii2

  • Nikhilesh Gupta

    SELECT(
    SELECT t1.name
    FROM table1 t1
    WHERE t1.id = t2.table1_id
    )
    FROM table2 t2
    WHERE t2.number =67
    how to write this query in active record???

    • Maksim

      /**
      * @return array
      */
      public static function getReferrerList()
      {
      $subQuery = (new Query())
      ->select(‘COUNT(*)’)
      ->from(self::tableName() . ‘ t1’)
      ->where(‘”t1″.”referrer_user_id” = “‘.self::tableName().'”.”id”‘)
      ->groupBy(‘referrer_user_id’);
      $query = (new Query())
      ->select([
      ‘id’,
      ‘username’,
      ‘count’ => $subQuery,
      ])
      ->from(self::tableName())
      ->orderBy([
      ‘count’ => SORT_DESC,
      ])
      ->indexBy(‘id’);
      // list ($sql, $params) = self::getDb()->queryBuilder->build($query);
      $result = $query->all();
      return $result;
      }

  • tds

    very thx, it’s working:
    $connection = Yii::$app->db;
    $date=date(‘Y-m-d’);
    $model = $connection->createCommand(“SELECT QUARTER(‘$date’)”);

    $users_count = $model->queryScalar();

  • kk

    join query for two tables.

  • Ajith

    how to use subqueries in yii2?

  • parthi

    how to show a particular tabledata in database using yii2 framework.

  • Masdi

    Hi, how to generate query like this:
    “SELECT fieldName FROM tableName WHERE fieldName LIKE ‘ADX%LZU’ “

  • Vinai Raj U

    how to insert multiple commands into a prepared statement?
    any one please Help me to do this