[FIXED] Confirm SQLbuilder query was successful in Yii

Issue

Firstly, I apologise if this is a really stupid question.

I had a question about dealing correctly with SQL statements within Yii. I'll make a small example code.

    public function actionCreate($id) {


    $cmd = Yii::app()->db->createCommand();
    $cmd->insert('table_1',array(
    'user_id'=> (int) $id,
    ),'id=:id', array(':id'=>$id)); 
}

What's the correct way to confirm this query worked? Is it try/catch blocks?

The reason I ask is that could fail if it's passed a bad parameter, but on a couple of tables I have DB constraints that could also result in a failure, so I wanted to try and make sure I handled everything properly rather than blanket handle them.

Solution

From official document

1. Executing SQL Statements Once a database connection is established, SQL statements can be executed using CDbCommand. One creates a CDbCommand instance by calling CDbConnection::createCommand() with the specified SQL statement:

$connection=Yii::app()->db;   // assuming you have configured a "db" connection
// If not, you may explicitly create a connection:
// $connection=new CDbConnection($dsn,$username,$password);
$command=$connection->createCommand($sql);
// if needed, the SQL statement may be updated as follows:
// $command->text=$newSQL;

A SQL statement is executed via CDbCommand in one of the following two ways:

And here it is

execute(): performs a non-query SQL statement, such as INSERT, UPDATE and DELETE. If successful, it returns the number of rows that are affected by the execution.

Btw, insert() is a low level method that's used internally by Active Record (AR). Why don't you simply use AR instead

By Yii gii, you automatically get model for table_1, and you can find, insert, update, delete from that. Example:

$model = new Table1ModelName;
$model->user_id= $id;
$model->name= $user_name;
...
$model->save();

There still has many workarounds and interesting things which you would like to study about Yii Working Active Record

Answered By - Telvin Nguyen