Yii 11.17 database related operation instructions

The content of this article is about Yii 11.17 database related operation instructions, which has certain reference value. Now I share it with you. Friends in need can refer to it

Example:

$result = array(
    'id'=>null,
    'val'=>0
);
$row1 = Yii::app()->db->createCommand()->insert('test1', $result);
$id   = Yii::app()->db->getLastInsertID();
$row2 = Yii::app()->db->createCommand()->update('test1', array('val'=>$id) , 'id=:id',array(':id'=>$id));
echo $id;
exit;

#1. To establish a database connection, you can use try...catch to catch exceptions that may be thrown
#$connection=new CDbConnection($dsn,$username,$password);
# DSN format
# 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
#Modify the alias in the configuration file

array(
    'components'=>array(
        'db'=>array(
            'class'=>'CDbConnection',
            'connectionString'=>'mysql:host=localhost;dbname=testdb',
            'username'=>'root',
            'password'=>'password',
            'emulatePrepare'=>true,  // needed by some MySQL installations
        ),
    ),
)

# Then use this method to establish a connection, and we can access the database connection through

$connection = Yii::app()->db

#. It is automatically activated unless we specifically configure
# CDbConnection::autoConnect to false.
# In this way, this single DB connection can be shared in many places in our code.
# If not, you may need to explicitly establish a connection:
# $connection=new CDbConnection($dsn,$username,$password);

$connection->active=true;  # 建立链接之后active为true;

$connection->active=false;    # 关闭连接

#Run SQL

$command=$connection->createCommand($sql);

#SQL modification
$command->text = $newSQL;

execute() method is used to execute INSERT, UPDATE and DELETE.
If successful, it will return the number of rows affected by this execution.
#query() method executes a SQL statement that returns several rows of data, such as SELECT. #If successful, it returns a CDbDataReader instance through which the resulting rows of data can be traversed. #For the sake of simplicity, (Yii) also implements a series of queryXXX() methods to directly return query results
#If an error occurs in SQL, an exception will be thrown. .

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

Get query results
#After generating CDbDataReader
#Repeatedly call CDbDataReader::read()

#Also CDbDataReader can be used in the foreach language structure

#For example


#

$dataReader = $command->query();

#Method 1 Repeatedly call read() until it returns false

while
( ($row = $dataReader->read()) !== false) { ... }

#Method 2 Use foreach to traverse each row in the data

foreach

($dataReader as $row) { ... }

#Method 3 Extract all rows into an array at once$rows = $dataReader->readAll();#Note:
#All queryXXX() methods will return data directly
#query() will not, but will return an instance of CDbDataReader
#Use transactions

$transaction= $connection->beginTransaction();
try
{
    $connection->createCommand($sql1)->execute();
    $connection->createCommand($sql2)->execute();
    #其他
    $transaction->commit();
}
catch(Exception $e) # 如果有一条查询失败，则会抛出异常
{
    $transaction->rollBack(); #回滚
}

#####Preprocessing (parameter binding) avoids injection and improves the efficiency of repeated executions####Placeholders can be named (appearing as a unique mark) or unnamed (appearing as a question mark). Placeholders will be replaced with actual parameters. ####Call CDbCommand::bindParam() or CDbCommand::bindValue() to replace these placeholders with actual parameters. ####These parameters do not need to be enclosed in quotes: the underlying database driver will handle this for you. This parameter binding must be completed before the SQL statement is executed. ####Example####A SQL with two placeholders ":username" and ":email"######

$sql="INSERT INTO tbl_user (username, email) VALUES(:username,:email)";
$command = $connection->createCommand($sql);

####Replace the placeholder "with the actual username": username"######

$command->bindParam(":username", $username, PDO::PARAM_STR);

####Replace the placeholder ":email" with the actual Email"######

$command->bindParam(":email",$email,PDO::PARAM_STR);
$command->execute();

####When executing the same logic repeatedly####Use the new one Parameter set inserts into another row######

$command->bindParam(":username",$username2,PDO::PARAM_STR);
$command->bindParam(":email",$email2,PDO::PARAM_STR);
$command->execute();

####bindParam() and bindValue() are very similar. ####The only difference is that the former uses a PHP variable to bind parameters, ####and the latter uses a value. For those parameters with large data blocks in memory, for performance reasons, the former should be used first. ################################################ ##############Bind columns######

$sql="SELECT username, email FROM tbl_user";
$dataReader=$connection->createCommand($sql)->query();

####Use the $username variable to bind the first column (username)######

$dataReader->bindColumn(1,$username);

### #Use the $email variable to bind the second column (email)######

$dataReader->bindColumn(2,$email);

######while###($dataReader->read()!==false)###{## # #$username and $email contain the username and email in the current line### #There is no need to assign values ​​to two variables every time###}############### #################################################use Table prefix####Configure the CDbConnection::tablePrefix property to the desired table prefix. ######

array(
    'components'=>array(
        'db'=>array(
            'class'=>'CDbConnection',
            'connectionString'=>'mysql:host=localhost;dbname=testdb',
            'username'=>'root',
            'password'=>'password',
            'emulatePrepare'=>true,  // needed by some MySQL installations
            'tablePrefix'=>"表前缀_"
        ),
    ),
)

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

#############################

$user = Yii::app()->db->createCommand()
        ->select('username, password')
        ->from('tbl_user')
        ->where('id=:id', array(':id'=>1))
        ->queryRow();

###where() method (available since v1 .1.6) ###public CDbCommand where(mixed $conditions, array $params=array())###$conditions mixed conditions placed in the WHERE part. ###$params         array                                                         use   with                                            . ###This method requires a $conditions parameter and a $params parameter, specifying the value to be bound to the query. ###############

$conditions parameter can be a string (such as 'id=1') or an array.

If it is the latter, it must be in the format array(operator, operand1, operand2, ...), the operator can be one of the following, and the possible operands depend on the corresponding operator:
and: The operands should be connected using AND.
For example:
array('and', 'id=1', 'id=2')
will generate
'id=1 AND id=2'. If an operand is an array, it is converted to a string using the same rules described here. For example:
array('and', 'type=1', array('or', 'id=1', 'id=2'))
will generate
'type=1 AND ( id=1 OR id=2)'
This method will not do any quoting or escaping.
or: Similar to the and operator, except that the operands are connected using OR.
in: Operand 1 should be a column or DB expression, operand 2 should be an array, indicating the range in which the value of the corresponding column or DB expression should be.
For example:
array('in', 'id', array(1,2,3))
will generate 'id IN (1,2,3)'
This method will be correct The quoted column name and the escaped value in the range.
not in: Similar to in, except IN is replaced with NOT IN when generating conditions.
like: Operand 1 should be a column or a DB expression, operand 2 is a string or an array indicating the value of the column or DB expression that should be like.
For example:
array('like', 'name', '%tester%')
will generate
"name LIKE '%tester%'"
When the value range is given As an array, multiple LIKE predicates will be generated and connected using AND.
For example:
array('like', 'name', array('%test%', '%sample%'))
will generate
"name LIKE '%test%' AND name LIKE '%sample%'"
This method will correctly quote escaped values ​​in column names and ranges.
not like: Similar to like, except that NOT LIKE is used instead of LIKE when generating conditions.
or like: Similar to like, except OR is used to connect LIKE predicates.
or not like: Similar to not like, except OR is used to connect NOT LIKE predicates.

Related recommendations:

Details of yii framework

Yii multi-application multi-module

Yii2 configuration basic concepts

The above is the detailed content of Yii 11.17 database related operation instructions. For more information, please follow other related articles on the PHP Chinese website!