Uncaught exception 'PDOException' with message 'SQLSTATE[42000

Question

I am trying to update a table from a CSV. The following error appears when trying to insert a row. Other queries go through in the same loop but it stops there on a particular one.

I've checked if all the values match exactly with the DB table and they do.

I am out of ideas on what could be the issue. Any suggestions?

The Query

INSERT INTO user_interests (member_num,ABU,ADD,ADOP,ANO,ANX,BER,BULL,COMP,CONF,CONT,CUL,DEP,DIS,DOM) VALUES (:member_num,:ABU,:ADD,:ADOP,:ANO,:ANX,:BER,:BULL,:COMP,:CONF,:CONT,:CUL,:DEP,:DIS,:DOM)

The error

Fatal error: Uncaught exception 'PDOException' with message '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 'ADD,ADOP,ANO,ANX,BER,BULL,COMP,CONF,CONT,CUL,DEP,DIS,DOM) VALUES ('5323',1,1,1,1' at line 1' in /Applications/XAMPP/xamppfiles/htdocs/ukcp/phpLibraries/users/userPopulateInts.php:45 Stack trace: #0 /Applications/XAMPP/xamppfiles/htdocs/ukcp/phpLibraries/users/userPopulateInts.php(45): PDOStatement->execute() #1 /Applications/XAMPP/xamppfiles/htdocs/ukcp/userprofile.php(14): userPopulateInts->ints1() #2 {main} thrown in /Applications/XAMPP/xamppfiles/htdocs/ukcp/phpLibraries/users/userPopulateInts.php on line 45

The Code

  if ( !empty($int) && !empty($intColon) ) {
                    $sql = 'INSERT INTO user_interests (member_num,'. implode(",", $int) .') VALUES (:member_num,'. implode(",", $intColon) .')';
                    $dbQuery = $this->db()->prepare($sql);
                    echo $sql ."<br>";
                    $dbQuery->bindValue(":member_num", $data[0], PDO::PARAM_INT);
                    foreach ($intColon as $val) {
                        $dbQuery->bindValue($val, 1, PDO::PARAM_INT);
                    }

                    $dbQuery->execute();
                }

score 4 · Answer 1 · answered Nov 07 '13 at 10:47

ADD is a reserved keyword and happens to be the name of your column. To avoid syntax error, you need to escape it using backtick. eg,

`ADD`

MySQL Reserved Keywords List

If you have the privilege to alter the table, change the column name to which is not a reserved keyword to avoid problem from occuring again.

juergen d · Accepted Answer · 2016-08-10T21:51:32.540

3

You need to escape reserved words like ADD with backticks:

INSERT INTO user_interests (member_num, ABU, `ADD`, ...

edited Aug 10 '16 at 21:51

answered Nov 07 '13 at 10:46

juergen d

201,996
37
293
362

in my case, it was 'condition'. – berserk Nov 10 '14 at 09:42

score 0 · Answer 3 · answered Nov 07 '13 at 10:48

0

ADD is a MySQL reserved keyword, as outlined here. Please either escape the table names, or pick better names that are not conflicting.

answered Nov 07 '13 at 10:48

David Houde

4,835
1
20
29

Uncaught exception 'PDOException' with message 'SQLSTATE[42000

3 Answers3