PDO Error: " Invalid parameter number: parameter was not defined"

Question

I am trying to use a simple MySQL insert query with the parameters in array form. It keeps telling me the number of parameters are wrong. I have tried the following, all producing the same error:

$stmt3 = $link->prepare('INSERT INTO messages VALUES(null, :room, :name, :message, :time, :color)');
$stmt3->execute(array(':room' => $Clean['room'],':name' => $Clean['name'],':message' => $Clean['message'],':time' => $time,':color:' => $Clean['color']));

and

$stmt3 = $link->prepare('INSERT INTO messages VALUES(:null, :room, :name, :message, :time, :color)');
$stmt3->execute(array(':null' => null, ':room' => $Clean['room'],':name' => $Clean['name'],':message' => $Clean['message'],':time' => $time,':color:' => $Clean['color']));

as well as declaring the columns specifically to avoid the null insert:

$stmt3 = $link->prepare('INSERT INTO messages (room, name, message, time, color) VALUES(:room, :name, :message, :time, :color)');
$stmt3->execute(array(':room' => $Clean['room'],':name' => $Clean['name'],':message' => $Clean['message'],':time' => $time,':color:' => $Clean['color']));

This is my first time using PDO (I normally use mysqli, but my current shared host does not have the mysqlnd plugin, preventing me from using prepare(), so any insight from that point of view is appreciated.

score 21 · Accepted Answer · edited Oct 24 '14 at 21:21

21

The problem - and you will kick yourself - is with :color.

The array key for the value you are passing for that marker when calling execute() is named :color:. Remove the trailing : (I'm guessing this was just a typo anyway).

$stmt3->execute(array(
    ':room' => $Clean['room'],
    ':name' => $Clean['name'],
    ':message' => $Clean['message'],
    ':time' => $time,
    ':color' => $Clean['color'],
    ));

edited Oct 24 '14 at 21:21

Tomasz Kowalczyk

10,472
6
52
68

answered Jun 15 '12 at 10:37

DaveRandom

87,921
11
154
174

1

OH MY GOD. I hate myself right now. I can't believe I stared at this for an hour and a half and didn't see it... – Morgan Jun 15 '12 at 10:40
8

It took me a couple of minutes. Sometimes you just need fresh eyes, that's what we're here for ;-) – DaveRandom Jun 15 '12 at 10:40
2

hehehe +1 for the fresh eyes! Nice catch Dave – Adi Jun 15 '12 at 10:43
1

@Dave you're exactly right. I was too focused on it to catch something that small. Thanks a lot! – Morgan Jun 15 '12 at 10:44
1

Just my 5 cents. I had the same issue with this generic error. Found the solution after nearly 1h in the naming convention. The parameter names can not have a hyphen (-) in it. After removing the hyphens everything is working as expected! – metamagikum Nov 05 '13 at 23:50

score -2 · Answer 2 · answered Jun 15 '12 at 10:31

-2

I might be wrong here, but as far as I know you need to do this:

$stmt3->bindParam(':room', $Clean['room']);
$stmt3->bindParam(':name', $Clean['name']);
//and so on

But as a personal preference, I've always done it like this

$stmt3 = $link->prepare('INSERT INTO messages VALUES(null, ?, ?, ?, ?, ?)');
$stmt3->execute(array($Clean['room'], $Clean['name'], $Clean['message'], $time, $Clean['color']))

answered Jun 15 '12 at 10:31

Adi

5,089
6
33
47

That is an optional way of doing it, according to http://www.php.net/manual/en/pdostatement.execute.php the array method I'm using should work. – Morgan Jun 15 '12 at 10:36
Your second example is how I was doing it with mysqli, but I'd really like to do it like this for readability, and I'm so invested in the error I'd really like to know what's causing it now. – Morgan Jun 15 '12 at 10:38

PDO Error: " Invalid parameter number: parameter was not defined"

2 Answers2

Linked

Related