OldMauiMan OldMauiMan - 4 years ago 220
SQL Question

Prepared statements getting ‘SQLSTATE[HY093]: Invalid parameter number: parameter was not defined’ PHP

I am getting an "Invalid parameter" fault on two small tables after happily building three complicated ones. I realize this is a highly duplicated question. I have checked literally dozens of answers here and elsewhere online.

I have cut and pasted names from the DB just to make 100% sure I am not missing any typos. I have used case sensitive searches. I am not using any banned characters in place holders, I am not using any reserved words. I have triple and quadruple checked that the place holders match. I have

var_dump
ed and
echo
ed and all looks as it should be.

I have converted strings to integers although did not need to that in previous tables. I am sure this is simple but I have been stuck on this for about six hours and just cannot see where I am going wrong. As two simple tables are both having problems I restarted and rebooted but nothing. I must presume I am doing something stupid but I really cannot see it.

if ($_GET ['randomTime']== "yes") {

$sql = "INSERT INTO randomTime (
transmissionProgramID,
numberOfTimes,
minimumGap,
randomTimeType,
lastUpdate
) VALUES (
:transmissionProgramID,
:numberOfTimes,
:minimumGap,
:randomTimeType,
:lastUpdate
);";
$statement = $pdo->prepare($sql);

$numberOfTimes = intval($numberOfTimes);
$minimumGap = intval($minimumGap);
$transmissionProgramID = intval($transmissionProgramID);

var_dump($transmissionProgramID,$numberOfTimes,$randomTimeType, $minimumGap,$lastUpdate);

$statement->bindValue(':transmissionProgramID,', $transmissionProgramID);
$statement->bindValue(':numberOfTimes,', $numberOfTimes);
$statement->bindValue(':minimumGap,', $minimumGap);
$statement->bindValue(':randomTimeType,', $randomTimeType);
$statement->bindValue(':lastUpdate', $lastUpdate);
$result = $statement->execute();

$randomTimeID = $pdo->lastInsertId();
}


The table is very straightforward

CREATE TABLE `randomTime` (
`randomTimeID` INT(11) NOT NULL AUTO_INCREMENT,
`numberOfTimes` TINYINT(4) NOT NULL DEFAULT '1',
`minimumGap` SMALLINT(6) NOT NULL DEFAULT '60',
`randomTimeType` ENUM('average','fixed','maximum') NULL DEFAULT 'fixed',
`transmissionProgramID` INT(11) NOT NULL,
`lastUpdate` DATETIME NULL DEFAULT NULL,
PRIMARY KEY (`randomTimeID`),
UNIQUE INDEX `randomTimeID` (`randomTimeID`),
INDEX `FK_randomTime_transmissionProgram` (`transmissionProgramID`),
CONSTRAINT `FK_randomTime_transmissionProgram` FOREIGN KEY
(`transmissionProgramID`) REFERENCES `transmissionProgram` (`transmissionProgramID`)
)


enter image description here

The var_dump produces what it should eg:

string(3) "131" string(1) "2" string(7) "average" int(200) string(19) "2016-03-01 14:36:42"


before I did
intval
on everything and now it shows

int(137) int(2) string(7) "average" int(60) string(19) "2016-03-01 17:33:32"

Answer Source

The comma isn't part of the named parameter. That separators the placeholders/values.

Your code should be:

$statement->bindValue(':transmissionProgramID', $transmissionProgramID);
$statement->bindValue(':numberOfTimes', $numberOfTimes);
$statement->bindValue(':minimumGap', $minimumGap);
$statement->bindValue(':randomTimeType', $randomTimeType);
$statement->bindValue(':lastUpdate', $lastUpdate);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download