SQL Update Statement throws SQLSTATE[42000]: Syntax error or access violation: 1064

Question

When trying to update my database I get a SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':summary, wentWell:wentWell, ' error. Would really appreciate some help since I have been trying to solve this problem for days now.

This is the code I use to update the database (journal.php):

    public function updateJournal($array) {
      $query = "UPDATE journal SET
                                  summary:summary, 
                                  wentWell:wentWell, 
                                  doBetter:doBetter, 
                                  ideas:ideas, 
                                  mood:mood, 
                                  motivation:motivation, 
                                  concentration:concentration, 
                                  tranquility:tranquility, 
                                  physical:physical WHERE id=:journalId";
      $stmt = $this -> connection -> prepare($query);
      $stmt -> execute();
    }

I use an array (updateJournal.php) to send the data to the updateJournal-Function (journal.php):

        $checkJournal -> updateJournal([
            "summary" => $summaryField,
            "wentWell" => $wentWellField,
            "doBetter" => $doBetterField,
            "ideas" => $ideasField,
            "mood" => $sliderValueMood,
            "motivation" => $sliderValueMotivation,
            "concentration" => $sliderValueConcentration,
            "tranquility" => $sliderValueTranquility,
            "physical" => $sliderValuePhysical,
            "journalId" => $journalId
        ]);

This is my Database:

Database

score 0 · Accepted Answer · answered Sep 25 '21 at 08:07

0

first in your set query the syntax is wrong, you need to replace : with = . Then no variable of the array is passed in your query you would need something like :

 $query = "UPDATE journal SET summary=".$array['summary'].",
                              wentWell=".$array['wentWell'].",...

I recommend you to look for sql update doc and maybe some php help

answered Sep 25 '21 at 08:07

Victorbzh

84
1
1
6

Thank you verry much! That helped a lot :) :) – Sarcanrye Sep 25 '21 at 09:46
no problem :), if you could validate my answer sir – Victorbzh Sep 25 '21 at 09:53
This is not how you use prepared statements, though! You want to use bound parameters, not concatenate strings (they might contain SQL injection attacks). – Enno Oct 03 '21 at 17:22
true but one problem at a time – Victorbzh Oct 04 '21 at 09:14

score 0 · Answer 2 · answered Oct 03 '21 at 17:28

The correct and safe way to do this is to use bound parameters like so (you were almost there):

    public function updateJournal($array) {
      $query = "UPDATE journal SET
                                  summary=:summary, 
                                  wentWell=:wentWell, 
                                  doBetter=:doBetter, 
                                  ideas=:ideas, 
                                  mood=:mood, 
                                  motivation=:motivation, 
                                  concentration=:concentration, 
                                  tranquility=:tranquility, 
                                  physical=:physical WHERE id=:journalId";
      $stmt = $this->connection->prepare($query);
      $stmt->execute($array);
    }

You were only missing the equal signs in the assignements, which is what caused the syntax error.

SQL Update Statement throws SQLSTATE[42000]: Syntax error or access violation: 1064

2 Answers2