Yii - DAO

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 affectedquery(): CDbDataReader instance from which one can traverse the resulting rows of dataqueryXXX() 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}