http://www.yiiframework.com/doc/guide/1.1/en/database.dao
$connection=new CDbConnection($dsn,$username,$password);
$connection->active=true;
- SQLite:
sqlite:/path/to/dbfile
- MySQL:
mysql:host=localhost;dbname=testdb
- PostgreSQL:
pgsql:host=localhost;port=5432;dbname=testdb
- SQL Server:
mssql:host=localhost;dbname=testdb
- Oracle:
oci:dbname=//localhost:1521/testdb
'components'=>array( ...... 'db'=>array( 'class'=>'CDbConnection', 'connectionString'=>'mysql:host=localhost;dbname=testdb', 'username'=>'root', 'password'=>'password', 'emulatePrepare'=>true, // needed by some MySQL installations ), ),
$connection=Yii::app()->db;
$command=$connection->createCommand($sql);
execute(): returns thenumber of rows that are affected
query(): CDbDataReader instance from which one can traverse the resulting rows of data
queryXXX()
methods are also implemented whichdirectly return the query results.
$rowCount=$command->execute(); // execute the non-query SQL $dataReader=$command->query(); // execute a query SQL $rows=$command->queryAll(); // query and return all rows of result $row=$command->queryRow(); // query and return the first row of result $column=$command->queryColumn(); // query and return the first column of result $value=$command->queryScalar(); // query and return the first field in the first row
$dataReader=$command->query(); while(($row=$dataReader->read())!==false) { ... } foreach($dataReader as $row) { ... } $rows=$dataReader->readAll();
$transaction=$connection->beginTransaction(); try { $connection->createCommand($sql1)->execute(); $connection->createCommand($sql2)->execute(); //.... other SQL executions $transaction->commit(); } catch(Exception $e) // an exception is raised if a query fails { $transaction->rollback(); }
// an SQL with two placeholders ":username" and ":email" $sql="INSERT INTO tbl_user (username, email) VALUES(:username,:email)"; $command=$connection->createCommand($sql); // replace the placeholder ":username" with the actual username value $command->bindParam(":username",$username,PDO::PARAM_STR); // replace the placeholder ":email" with the actual email value $command->bindParam(":email",$email,PDO::PARAM_STR); $command->execute(); // insert another row with a new set of parameters $command->bindParam(":username",$username2,PDO::PARAM_STR); $command->bindParam(":email",$email2,PDO::PARAM_STR); $command->execute();
6. Binding Columns
When fetching query results, one can also bind columns with PHP variablesso that they are automatically populated with the latest data each time arow is fetched.
$sql="SELECT username, email FROM tbl_user"; $dataReader=$connection->createCommand($sql)->query(); // bind the 1st column (username) with the $username variable $dataReader->bindColumn(1,$username); // bind the 2nd column (email) with the $email variable $dataReader->bindColumn(2,$email); while($dataReader->read()!==false) { // $username and $email contain the username and email in the current row }