PDO insert not inserting with multiple values

Question

So I have some data that I'm trying to enter with PDO. Here's what my code looks like

$DATA = $con->prepare("INSERT INTO users (key, ip) VALUES (:key, :ip)");
$DATA->bindValue(':ip', $ip, PDO::PARAM_STR);
$DATA->bindValue(':key', $key, PDO:PARAM_STR);
$DATA->execute();

The issue is it won't enter into the DB. It only works when I do one value like this

$DATA = $con->prepare("INSERT INTO users (key) VALUES (:key)");

Is this normal? Do I do individual statements for each variable?

My DB

+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| key   | varchar(255)     | YES  |     | NULL    |                |
| ip    | varchar(255)     | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+

I get 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 'key, ip) VALUES ('53a786577de99', 'E6pdpExv6q363baea9cba210afac6d7a556fca596e30c' at line 1' in /Users/mike/Desktop/Mail/index.php:37 Stack trace: #0 /Users/mike/Desktop/Mail/index.php(37): PDOStatement->execute() #1 {main} thrown in /Users/mike/Desktop/Mail/index.php on line 37

I didn't notice that. But just fixed it. And it still doesn't work... @Chris — , Jun 23 '14 at 01:30
Nope. Also the database has all the rows.When I try to do an `echo` after the code I posted it won't return a result. When I remove `PDO:PARAM_STR` I can run code after it. @zerkms — , Jun 23 '14 at 01:37
@user302975: "nope" --- how do you know that? I don't see you're checking for any PDO errors — zerkms, Jun 23 '14 at 01:38
I think we need a little additional information about your schema, and what's in the variables to see what exactly is wrong here... @zerkms is right, though - if it's not inserting, it should be throwing an error. — FrankieTheKneeMan, Jun 23 '14 at 01:38
I'm using `error_reporting(-1); ini_set('display_errors', 'On');` Is this enough? @zerkms — , Jun 23 '14 at 01:38
@user302975: it's not unless your PDO is set up to throw errors. Which presumable isn't the case here. Check http://stackoverflow.com/a/2104519/251311 — zerkms, Jun 23 '14 at 01:39
`KEY` is a [reserved word](http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html) and must be surrounded by backticks. — Ja͢ck, Jun 23 '14 at 01:45
It's already been answered in [this question](http://stackoverflow.com/questions/23446377/syntax-error-due-to-using-a-reserved-word-as-a-table-or-column-name-in-mysql). — Ja͢ck, Jun 23 '14 at 01:49
As already stated, yet not just `key` but `keys` as well, should you decide to plurarize it in wanting to get away with it. Use a different word altogether, or use backticks around it. http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html — Funk Forty Niner, Jun 23 '14 at 01:52

score 3 · Accepted Answer · answered Jun 23 '14 at 01:47

3

I think KEY is a reserved word if I recall... try back ticks.

answered Jun 23 '14 at 01:47

TalkingHeads

130
2
11

PDO insert not inserting with multiple values

1 Answers1