CODEWITHSUNDEEP
phphtmlmysqlpostprepared-statement
Ibrahim Hafiji
Ibrahim Hafiji

Reputation: 150

Error: SQLSTATE[42000]: Syntax error or access violation: 1064 - PHP MYSQL

I understand this is a duplicate of previously asked questions. However, I have followed previous answers and still getting no results.

I am using a prepared statement to take a comment from a html <form> with the method post. the comment along with the unique id in the session is being passes to the page addComment.php

This is the contents of "addComment.php"

<?php
    session_start();
    $servername = "localhost";
    $username = "root";
    $password = "root";
    $dbname = "somedatabase";
    try {
        $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
        // set the PDO error mode to exception
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        // prepare sql and bind parameters
        $stmt = $conn->prepare("INSERT INTO patients( comments ) VALUES ( :comment ) WHERE unique_id = :unique_id");
        $stmt->bindParam( ':comment',           $comment    );
        $stmt->bindParam( ':unique_id',         $unique_id  );
        $comment          =   $_POST[      'comment'     ];
        $unique_id        =   $_SESSION[   'unique_id'        ];
        $stmt->execute();
        //header('Location: newMedicine.php');
    }
    catch(PDOException $e){
        echo "Error: " . $e->getMessage();
    }
    $conn = null;
?>

I have done an echo on 

 $comment          =   $_POST[      'comment'     ];
 $unique_id        =   $_SESSION[   'unique_id'        ];

and both of them print fine.

The error I am getting is

Error: 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 'WHERE unique_id = 'JohnDoe20RG2018-01-23 11:43:' at line 13

The unique_id field in the patients table in the database has the same value

JohnDoe20RG2018-01-23 11:43:17

I don't see where I am going wrong. I have used multiple prepared statements with Selects and Inserts throughout my project, and they all work fine.

Any help would be appreciated.

Upvotes: 0

Views: 477

Answers (1)

Nigel Ren
Nigel Ren

Reputation: 57131

If you are creating a new record with the id and comment, then use...

$stmt = $conn->prepare("INSERT INTO patients ( unique_id, comments ) 
                    VALUES ( :unique_id, :comment ) ");

If it's an existing record - 

$stmt = $conn->prepare("UPDATE patients SET comments=:comment
                    WHERE unique_id = :unique_id");

Upvotes: 1

Related Questions