Exception on execute() with prepared statements

Question

This really looks like simple task but still, kicking my head against the wall last few hours and searching the web for any clue to solve this didn't help as well.

Error:

In short I'm getting exception on first execute() call inside foreach loop.

Error while execute data (User1, Group1)

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 'group)
VALUES ('User1', 'Group1')' at line 2' in C:\wamp\www\dbo-test-transactions.php:32
Stack trace: #0 C:\wamp\www\dbo-test-transactions.php(32):
PDOStatement->execute(Array) #1 {main}

What I use?

WAMP 2.4 on Windows; Database host, username and password have default values ("localhost", "root" and ""). I have created database test (colation: utf8_general_ci) with table users (two columns user_name and group). Both columns are VARCHAR(50) and user_name column has PRIMARY KEY. Database engine is InnoDB.

enter image description here

What have I tried?

I've already tried to execute queries directly using query() and "real" mysql query, with no transactions, and it works well. But when I try to use prepared statements it keeps failing. Also, I've tried using different approach with bindParam() or bindValue() but final result is always the same - it throws exception.

Have already checked a lot of resources, mostly here on StackOverflow, PHP.net and MySQL.com, read all, letter by letter, checked all examples, comments, etc.

Relevant code

My database connection has been established using this piece of code.

try {
  $pdo = @new PDO("mysql:host=localhost;dbname=test;charset=utf8", "root", "");
  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
  }
catch (PDOException $ex) {
  die ("<b>Connection Error!</b><br /><br />{$ex}");
  }

My query looks like this.

$sql = "INSERT INTO users (user_name, group) VALUES (?, ?)";

All data I want to insert into database-table are stored in an array.

$data = array();
$data[] = array("User1", "Group1");
$data[] = array("User2", "Group2");
$data[] = array("User3", "Group3");

At the end, what I'm trying to do is here:

$pdo->beginTransaction();

$res = $pdo->prepare($sql);
foreach ($data as $values) {
  try {
    $res->execute($values);
    }
  catch (PDOException $ex) {
    $pdo->rollBack();
    die ("<b>Error while execute data ({$values[0]}, {$values[1]})</b><br /><br />{$ex}");
    }
  }

$pdo->commit();

I'm sure that answer is somewhere above in the code but still have no clue where's the problem. Yep, looks like beginners problem and that really drives me crazy the most.

If you need any more info or updates, let me know. Hope I have provided all relevant code samples and information.

score 1 · Accepted Answer · edited May 23 '17 at 11:57

1

Found the answer. According to this link, the solution is to encapsulate your field names. PHP PDO Syntax error or access violation: 1064 on insert

In the previous link, "desc" was a reserved keyword. In your case, "group" is a reserved word. http://dev.mysql.com/doc/refman/5.6/en/reserved-words.html

Try this query instead:

$sql = "INSERT INTO `users` (`user_name`, `group`) VALUES (?, ?)";

edited May 23 '17 at 11:57

Community

1
1

answered Dec 31 '14 at 00:57

MingShun

287
3
11

Oh God. Never thought about using backticks because of I actually never found usage case where they're needed, all worked well without them before. But actually problem was reserved word **GROUP** and it also works well if I change column-name to **'user_group'** (backticks aren't needed than). Thanks a lot, you saved my day trying to figure out that obvious syntax mistake. Well, your first link confirms that but I never realized it was a problem. – Wh1T3h4Ck5 Dec 31 '14 at 01:28

Exception on execute() with prepared statements

1 Answers1