public function actionTestDb(){ // return a set of rows. each row is an associative array of column names and values. // an empty array is returned if the query returned no results $users = Yii::$app->db->createCommand('SELECT * FROM user LIMIT 5') ->queryAll(); var_dump($users); echo '<hr/>'; // return a single row (the first row) // false is returned if the query has no result $user = Yii::$app->db->createCommand('SELECT * FROM user WHERE id=1') ->queryOne(); var_dump($user);echo '<hr/>'; // return a single column (the first column) // an empty array is returned if the query returned no results $userName = Yii::$app->db->createCommand('SELECT name FROM user') ->queryColumn(); var_dump($userName); echo '<hr/>'; // return a scalar value // false is returned if the query has no result $count = Yii::$app->db->createCommand('SELECT COUNT(*) FROM user') ->queryScalar(); var_dump($count); }
public function actionTestDb() { $firstUser = Yii::$app->db->createCommand('SELECT * FROM user WHERE id = :id') ->bindValue(':id', 1) ->queryOne(); var_dump($firstUser);echo '<hr/>'; $params = [':id' => 2, ':name' => 'User2']; $secondUser = Yii::$app->db->createCommand('SELECT * FROM user WHERE id = :id AND name = :name') ->bindValues($params) ->queryOne(); var_dump($secondUser);echo '<hr/>'; //another approach $params = [':id' => 3, ':name' => 'User3']; $thirdUser = Yii::$app->db->createCommand('SELECT * FROM user WHERE id = :id AND name = :name', $params) ->queryOne(); var_dump($thirdUser); }
在上面的程式碼中 -
bindValue() ? 係結單個引數值
bindValues() ? 係結多個引數值
public function actionTestDb() { // INSERT (table name, column values) Yii::$app->db->createCommand()->insert('user', [ 'name' => 'My New User', 'email' => '[email protected]', ])->execute(); $user = Yii::$app->db->createCommand('SELECT * FROM user WHERE name = :name') ->bindValue(':name', 'My New User') ->queryOne(); var_dump($user);echo '<hr/>'; // UPDATE (table name, column values, condition) Yii::$app->db->createCommand()->update('user', ['name' => 'My New User Updated'], 'name = "My New User"')->execute(); $user = Yii::$app->db->createCommand('SELECT * FROM user WHERE name = :name') ->bindValue(':name', 'My New User Updated') ->queryOne(); var_dump($user);echo '<hr/>'; // DELETE (table name, condition) Yii::$app->db->createCommand()->delete('user', 'name = "My New User Updated"')->execute(); $user = Yii::$app->db->createCommand('SELECT * FROM user WHERE name = :name') ->bindValue(':name', 'My New User Updated') ->queryOne(); var_dump($user); }
第2步 - 在瀏覽器開啟URL地址:http://localhost:8080/index.php?r=site/test-db,會看到輸出如下圖所示。