PHP PDO 'SQLSTATE[HY093]: Invalid parameter number: parameter was not defined

Question

I've been fighting this one for a couple of days now ...

Just after connection to db -

    $this->sqladdimport = $this->db->prepare('INSERT INTO `importqueue`
                    (`user_id`, `service_id`, `format`, `comment`, `data`, `status`)
                VALUES (:user, :service, :format, ":commment", :file, :status)',
                 array(PDO::ATTR_EMULATE_PREPARES=>true));

Called by -

function addImportQueue($filename, $user, $format, $service_id, $comment, $status = IMPORT_NEW ){
    $fh = fopen($filename, 'r');
    if (!$fh) {
        return false;
    }

    $params = array(    ':user' => $user['user_id'],
                        ':format' => $format,
                        ':service' => $service_id,
                        ':comment' => $comment,
                        ':status' => $status) ;
    $this->sqladdimport->bindParam(':file', $fh, PDO::PARAM_LOB);
    foreach ($params as $key => &$val){
        $this->sqladdimport->bindParam($key,$val);
    }

    $this->sqladdimport->execute();
    $id = $this->db->lastInsertId();

    return $id;
}

But I just get

PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number: parameter was not defined'

on the execute line.

In case it's relevant, the table is defined thus:

CREATE TABLE `importqueue` (
`import_id` INT(11) NOT NULL AUTO_INCREMENT,
`service_id` INT(11) NOT NULL DEFAULT '0',
`format` INT(11) NULL DEFAULT '0',
`user_id` INT(11) NULL DEFAULT '0',
`comment` TEXT NULL,
`date` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
`data` MEDIUMBLOB NULL,
`status` INT(11) NULL DEFAULT NULL,
INDEX `Index 1` (`import_id`)
)
COMMENT='Import queue/archive'
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
;

I've fairly new to PDO, and especially BLOBs, so I'm probably doing something really obviously stupid, but it's currently escaping me!

Thanks!

Edit: I note the references to the quotes around :comment in the prepare - this was an artefact of my debugging, and I have removed those again with no change.

Have also simplified the function to

    $fh = fopen($filename, 'r');
    $this->sqladdimport->bindValue(':file', $fh, PDO::PARAM_LOB);
    $this->sqladdimport->bindValue(':user', $user['user_id']);
    $this->sqladdimport->bindValue(':format', $format);
    $this->sqladdimport->bindValue(':service', $service_id);
    $this->sqladdimport->bindValue(':comment', $comment);
    $this->sqladdimport->bindValue(':status', $status);
    $this->sqladdimport->execute();

and I still get the same error (using either bindParam or bindValue)

Possible duplicate of SQLSTATE[HY093]: Invalid parameter number: parameter was not defined — aynber
– aynber, Commented Sep 13, 2018 at 17:55
Don't wrap your placeholders in quotes, PDO/mysqli will think they're just strings instead of placeholders. — aynber
– aynber, Commented Sep 13, 2018 at 17:55
aynber ah yes, that was just one of my debug attempts.. it doesn't work without the quotes either. — user2158583
– user2158583, Commented Sep 13, 2018 at 18:00
Why you define an array of bind parameters and after this you bin them in a loop? Why you do not bind them directly? — Jens
– Jens, Commented Sep 13, 2018 at 18:06
Just wonder why do you have ":commment" only in double quotes. I believe it is the culprit — Rinsad Ahmed
– Rinsad Ahmed, Commented Sep 13, 2018 at 18:06

Martin Zeitler · Accepted Answer · 2018-09-13 18:25:55Z

2

see Keywords and Reserved Words ...

you may want to rename columns comment, date, format and status.

the Invalid parameter number comes from ":comment" not being substituted, therefore the number of columns does not match the number of parameters, less one. in order to provide the prepared statement with the correct number of parameters, it should be :comment instead.

edited Sep 13, 2018 at 18:25

answered Sep 13, 2018 at 18:07

Martin Zeitler

79.6k20 gold badges169 silver badges243 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

aynber Over a year ago

They're keywords, but not reserved. The OP has them in backticks, so the usage is fine.

Martin Zeitler Over a year ago

it is still bad practice, because one must wrap them in backticks - and it might only cause confusion, when having to differ in between column name and a keyword.

user2158583 Over a year ago

Thanks, I can adjust the field names in production (it's not too late to do this) but am trying to get functionality working first..

Rinsad Ahmed · Accepted Answer · 2018-09-13 18:51:24Z

0

You have only the ":commment" placeholder in double quotes. Hence it will be treated as a string

Also if you are trying to store a binary file use

$fh = fopen($filename, 'rb');

instead of

$fh = fopen($filename, 'r');

Also, try to define the datatype for all the columns because bindValue method has a 3rd column which defaults to PDO::PARAM_STR. But you have numerical columns too

$fh = fopen($filename, 'rb');
$this->sqladdimport->bindValue(':file', $fh, PDO::PARAM_LOB);
$this->sqladdimport->bindValue(':user', $user['user_id'],PDO::PARAM_INT);
$this->sqladdimport->bindValue(':format', $format,PDO::PARAM_INT);
$this->sqladdimport->bindValue(':service', $service_id,PDO::PARAM_INT);
$this->sqladdimport->bindValue(':comment', $comment,PDO::PARAM_STR);
$this->sqladdimport->bindValue(':status', $status,PDO::PARAM_INT);
$this->sqladdimport->execute();

edited Sep 13, 2018 at 18:51

answered Sep 13, 2018 at 18:08

Rinsad Ahmed

1,9431 gold badge12 silver badges28 bronze badges

5 Comments

user2158583 Over a year ago

I changed this, and it's not sorted my problem :(

Rinsad Ahmed Over a year ago

@user2158583 your file is a binary file or text file?

user2158583 Over a year ago

Ah. It's text, but with various non-printing characters. I should treat it as binary as I don't want anything altered. Changed to 'rb', but still getting the error.

Rinsad Ahmed Over a year ago

You fixed the ":comment" placeholder too?

user2158583 Over a year ago

I did. $this->sqladdimport = $this->db->prepare('INSERT INTO importqueue` (user_id, service_id, format, comment, data, status) VALUES (:user, :service, :format, :commment, :file, :status)', array(PDO::ATTR_EMULATE_PREPARES=>true));`

user2158583 · Accepted Answer · 2018-09-13 22:22:13Z

0

Found it ....

I copied the code into a test script that I could more easily modify. Started with just the one field (which worked) and gradually added the rest one by one.

The problem turned out to be a simple typo in the prepare command... That referred to :commment. The bind was referring to :comment. I never noticed the extra 'm' ..

Thanks everybody for all the suggestions. It's been a learning experience!

answered Sep 13, 2018 at 22:22

user2158583

411 gold badge1 silver badge7 bronze badges

Collectives™ on Stack Overflow

PHP PDO 'SQLSTATE[HY093]: Invalid parameter number: parameter was not defined

3 Answers 3

3 Comments

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related