Yii: php -> Cannot execute SQL command in SiteController

Question

I have some problem when submitting the _form.php to SiteController.php this error message will appear,

"CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; "

It cannot run the query. Also try with another query command, the error is same.

SiteController.php

public function actionCreate()
{
    $model = new Detail;
   if (isset($_POST['Detail'])) {
       // print_r($_POST['Detail']);
        $model->attributes = $_POST['Detail'];
        $connection = Yii::app()->db;

        $data = date('Y-m-d');
        $date = "'" . $data . "'";
        $teach_id = Yii::app()->user->getId();

        $i = 1;
        foreach ($_POST['Detail'] as $stud=>$status) 
        {
            $sql = 'INSERT INTO {{detail}} (`id`, `student_id`, `teacher_id`, `date`, `status`) VALUES 
                    (NULL, ' . $stud . ',' . $teach_id . ',' . $date . ', ' . $i . ' );';


            $command = $connection->createCommand($sql);
            $exec = $command->query();
        } 
        if ($exec) {
            $this->redirect(array('index'));
        }
    }
    $this->render('create', array('model' => $model, ));
}

score 1 · Accepted Answer · 2015-07-02 08:26:00Z

1

Try to replace {{detail}} with real table name and use double quotes for whole string and single quote for each variable

"INSERT INTO {{detail}}
    (`id`, `student_id`, `teacher_id`, `date`, `status`)
VALUES
    (NULL, '$stud', '$teach_id', '$date', '$i');"

replace

$date = "'" . $data . "'";

to

$date = $data;

and you are using the same key of $_POST array in different cases

$model->attributes = $_POST['Detail'];
...
foreach ($_POST['Detail'] as $stud => $status)

edited Jul 2, 2015 at 8:26

answered Jul 1, 2015 at 21:47

user2594803

Sign up to request clarification or add additional context in comments.

9 Comments

mrdidie Over a year ago

actually detail is my real table name and i was change the code like you do, but the same error still appear

user2594803 Over a year ago

@mrdidie check out last version of answer

mrdidie Over a year ago

if i change date = $data, it cannot retreive the date value. What do you mean {same key of $_POST } ?

mrdidie Over a year ago

now i change sql statement like this: $sql = 'INSERT INTO detail(id,student_id,teacher_id,date,status) VALUES (NULL,'.$stud.','.$teach_id.','.$date.','.$i.');'; and the error is Column not found: 1054 Unknown column 'SH1004' in 'field list'. The SQL statement executed was: INSERT INTO detail(id,student_id,teacher_id,date,status) VALUES (NULL,SH1004,CSH1001,'2015-07-02',1);

user2594803 Over a year ago

@mrdidie use my example with double quotes "INSERT and ,'$date',

|

ScaisEdge · Accepted Answer · 2015-07-01 21:44:15Z

0

I think the problem is the '{{detail}}' inside single quote. Single quote in PHP are managed as is and no evaluation of the content is performed. I think you use curly braces to isolate the name of the variable you want evaluated and then the use of single quote is a mistake. For this you need or use double quote " ......" or use string concatenation like you do in other part of the SQL instruction..

answered Jul 1, 2015 at 21:44

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

2 Comments

mrdidie Over a year ago

put a {{detail}} in a single quote ?? and the whole SQL statement in double quote right ?, still not function and CDbException still appear. I dont know what is the correct syntax to execute this statement :(

ScaisEdge Over a year ago

NO!. The SQL statement in double quote and eventual literal in single quote

Collectives™ on Stack Overflow

Yii: php -> Cannot execute SQL command in SiteController

2 Answers 2

9 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related