CODEWITHSUNDEEP
phpmysqlyii2
Toma Tomov
Toma Tomov

Reputation: 1654

Yii2 error on sql execute

I am trying to save message in MySQL but failure is what i get. 

public function actionChat()
    {
        $message = \Yii::$app->request->get('message');
        $user_id = \Yii::$app->user->identity->id;
        \Yii::$app->db->createCommand("INSERT INTO chat(message_content, user_id) VALUES ($message, $user_id)")->execute();

        return $message;
    }

When i write the same query in phpmyadmin it works fine but not in my controller. I also tried to put some string in the query like

\Yii::$app->db->createCommand("INSERT INTO chat(message_content, user_id) VALUES ('first message', $user_id)")->execute();

but result was the same. Can you give me advice where is my mistake? Thank you! AJAX: 

$('body').on('click', '#chat-button', function () {
        var message = $('#chat-message').val();
        $.ajax({
            method : 'GET',
            url : '../site/chat?message=' + message,
            dataType : 'text',
            success : function ( data ) {
                alert(data);
            }
        }) ;
    });

Upvotes: 2

Views: 1137

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133360

If $message is string value you should use single queto in you sql statement ( and if $user_id is a string then add quote to this var too)

 \Yii::$app->db->createCommand("INSERT INTO chat(message_content, user_id)
           VALUES ('$message', $user_id)")->execute();

and be carefull the use of $var in SQL statements can implies sqlijnjection .. you should use param bindig for assign values

Upvotes: 0

Pedro del Sol
Pedro del Sol

Reputation: 2841

Another approach would be to use a parameterized query; avoiding any quoting issues.

\Yii::$app->db->createCommand(
     "INSERT INTO chat(message_content, user_id)
      VALUES (:content, :userID)", 
      [':content' => $message, ':userID' => $user_id])->execute();

Upvotes: 2

Related Questions