PHP开发框架Yii Framework教程(24) 数据库-DAO示例

$connection=new CDbConnection($dsn,$username,$password);
// 建立连接。你可以使用 try...catch 捕获可能抛出的异常
$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=chinook',
'username'=>'root',
'password'=>'password',
'emulatePrepare'=>true, // needed by some MySQL installations
),
),

class DataModel
{
public $employeeId;
public $firstName;
public $lastName;
public $title;
public $address;
public $email;
}

public function
actionIndex(){
$model = array();$sql='SELECT * FROM Employee';
// 假设你已经建立了一个 "db" 连接$connection=Yii::app()->db;
// 如果没有，你可能需要显式建立一个连接：
// $connection=new CDbConnection($dsn,$username,$password);
$command=$connection->createCommand($sql);
// 如果需要，此 SQL 语句可通过如下方式修改：
// $command->text=$newSQL;
$dataReader=$command->query();
// each $row is an array representing a row of dataforeach($dataReader as $row){$employee = new DataModel();
$employee->employeeId=$row['EmployeeId'];
$employee->firstName=$row['FirstName'];
$employee->lastName=$row['LastName'];
$employee->title=$row['Title'];
$employee->address=$row['Address'];
$employee->email=$row['Email'];
$model[]=$employee;}
$this->render('index', array('model' => $model,));
}

$rowCount=$command->execute(); // 执行无查询 SQL
$dataReader=$command->query(); // 执行一个 SQL 查询
$rows=$command->queryAll(); // 查询并返回结果中的所有行
$row=$command->queryRow(); // 查询并返回结果中的第一行
$column=$command->queryColumn(); // 查询并返回结果中的第一列
$value=$command->queryScalar(); // 查询并返回结果中第一行的第一个字段

$dataReader=$command->query();
// 重复调用 read() 直到它返回 false
while(($row=$dataReader->read())!==false) { ... }
// 使用 foreach 遍历数据中的每一行
foreach($dataReader as $row) { ... }
// 一次性提取所有行到一个数组
$rows=$dataReader->readAll();

foreach($model as $employee)
{
echo 'EmployeeId:' . $employee->employeeId . '
';
echo 'First Name:' . $employee->firstName . '
';
echo 'Last Name:' . $employee->lastName . '
';
echo 'Title:' . $employee->title . '
';
echo 'Address:' . $employee->address . '
';
echo 'Email:' . $employee->email . '
';
echo '----------------------
';
}
?>

$transaction=$connection->beginTransaction();
try
{
$connection->createCommand($sql1)->execute();
$connection->createCommand($sql2)->execute();
//.... other SQL executions
$transaction->commit();
}
catch(Exception $e) // 如果有一条查询失败，则会抛出异常
{
$transaction->rollBack();
}

// 一条带有两个占位符 ":username" 和 ":email"的 SQL
$sql="INSERT INTO tbl_user (username, email) VALUES(:username,:email)";
$command=$connection->createCommand($sql);
// 用实际的用户名替换占位符 ":username"
$command->bindParam(":username",$username,PDO::PARAM_STR);
// 用实际的 Email 替换占位符 ":email"
$command->bindParam(":email",$email,PDO::PARAM_STR);
$command->execute();
// 使用新的参数集插入另一行
$command->bindParam(":username",$username2,PDO::PARAM_STR);
$command->bindParam(":email",$email2,PDO::PARAM_STR);

$sql="SELECT username, email FROM tbl_user";
$dataReader=$connection->createCommand($sql)->query();
// 使用 $username 变量绑定第一列 (username)
$dataReader->bindColumn(1,$username);
// 使用 $email 变量绑定第二列 (email)
$dataReader->bindColumn(2,$email);
while($dataReader->read()!==false)
{
// $username 和 $email 含有当前行中的 username 和 email
}

$sql='SELECT * FROM {{user}}';$users=$connection->createCommand($sql)->queryAll();