Member Avatar for everton.retweets

Hi Everyone, I am struggling to fix this stupid problem I have with pdo php insert.

I have this working on a different insert, but for some reason, this is just not working.
I keep getting the error

"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 'keys, statement, interests) values (?, ?, ?, ?)' at line 1"

I have checked, checked again, then checked again that each column name is correct, each variable is also correct.

Can someone point out where I am going wrong, or where the problem is within the code.

    if ($stmt = $mysqli->prepare("INSERT INTO S4HCandidate_Cv (uid, keys, statement, interests)  
     values (?, ?, ?, ?)")) {

    // Bind our 4 params
    $stmt->bind_param('isss', $canref, $cancvkeys, $cancvstatement, $cancvinter);

    //form values
        $canref             = "$canref";            //1
        $cancvkeys          = "$cancvkeys";         //2
        $cancvstatement     = "$cancvstatement";    //3     
        $cancvinter         = "$cancvinter";        //6     
    // Execute the prepared Statement
    $stmt->execute();           
    /* Close the statement */   
    $stmt->close(); 
    }

The really strange thing is, if I add a table name that is not in the database I get the same error,

I have also tried -

    $sql = "INSERT INTO S4HCandidate_Cv (uid, keys, statement, interests) VALUES (:cvuid,:cvkeys,:cvstatement,:cvinterests)";
    $q = $conn->prepare($sql);
    $q->execute(array(':cvuid'=>$canref,
                      ':cvkeys'=>$cancvkeys,
                      ':cvstatement'=>$cancvstatement,
                      ':cvinterests'=>$cancvinter));

and

    $stmt = $conn->prepare("INSERT INTO S4HCandidate_Cv (uid, keys, statement, interests) VALUES (?, ?, ?, ?)");
    $stmt -> bindParam(1, $canref);
    $stmt -> bindParam(2, $cancvkeys);
    $stmt -> bindParam(3, $cancvstatement);
    $stmt -> bindParam(4, $cancvinter);

With the above, I dont get any error message, but the database is not updated either.

  • 2 Contributors
  • 5 Replies
  • 450 Views
  • 1 Hour Discussion Span
  • Latest Post Latest Post by pritaeas

Dani AI

Generated

Short answer: is right — the column name keys is colliding with a MySQL keyword. The parser sees that before it checks whether a table exists, which is why changing the table name still produced the same syntax error.

A safe, long‑term fix is to rename the column so you never rely on quoting. Example (adjust the type/NULL to match your schema):

ALTER TABLE S4HCandidate_Cv
  CHANGE `keys` `keywords` VARCHAR(255) NOT NULL;

If you need a quick workaround, quote identifiers with backticks in every query. Also enable PDO exceptions so you get a clear error trace and can inspect driver error info:

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

try {
  $stmt = $pdo->prepare('INSERT INTO `S4HCandidate_Cv` (`uid`,`keys`,`statement`,`interests`) VALUES (:uid,:keys,:statement,:interests)');
  $stmt->execute([':uid'=>$canref, ':keys'=>$cancvkeys, ':statement'=>$cancvstatement, ':interests'=>$cancvinter]);
  echo 'Inserted ID: ' . $pdo->lastInsertId();
} catch (PDOException $e) {
  error_log($e->getMessage());
  print_r($stmt->errorInfo());
}

Extra troubleshooting notes: make sure you’re using the connection object you expect (don’t mix $mysqli and $conn by accident). If you use bindParam() remember it binds by reference — bindValue() or passing an array to execute() is usually simpler. After execute, check rowCount() / lastInsertId() to confirm the insert. Renaming the column is the cleanest fix to avoid future surprises.

Recommended Answers

All 5 Replies

Member Avatar for everton.retweets

Updated...

I have also tried

    $stmt = $conn->prepare('INSERT INTO S4HCandidate_Cv (uid, keys, statement, interests) 
    VALUES(:uid, :keys, :statement, :interests)');
    $stmt->bindParam(':uid', $canref, PDO::PARAM_INT);
    $stmt->bindParam(':keys', $cancvkeys, PDO::PARAM_STR);
    $stmt->bindParam(':statement', $cancvstatement, PDO::PARAM_STR);
    $stmt->bindParam(':interests', $cancvinter, PDO::PARAM_STR);    
    try {
        $stmt->execute();
        //print_r($stmt);
        //$result = $stmt->fetchAll();
    }
    catch (Exception $e) {
        echo $e;
    }

Still getting the same error message...

Member Avatar for pritaeas

Keys is a reserved word, so you need to surround it with backticks.

Member Avatar for everton.retweets

So if I change the word from Keys to kwds, that will fix the problem?

What do you mean, surround it with backticks?

Member Avatar for everton.retweets

- Well thats 48 hours I will never get back :)
Many thanks for this mate, Is there a list of reserved words I can use to look up if I come across this same issue...

Can't believe I have gone round in circles for two days trying to sort this out...

Member Avatar for pritaeas

Is there a list of reserved words

What do you mean, surround it with backticks?

$stmt = $conn->prepare('INSERT INTO S4HCandidate_Cv (uid, `keys`, statement, interests) 
VALUES(:uid, :keys, :statement, :interests)');
Edited by pritaeas
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.