I have quite a large form I'm trying to use as data for an insert operation. Since I'm not really sure about what is going wrong except from this error message:
PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: parameter was not defined in... on row...
Things I'm wondering about that might be the problem:
Since I use auto increment on the primary key I think I don't have to insert any data for the column in that row. Am I right about that? and if so, am I doing it right?
I have a foreign key in the table that I don't want to insert by using the html form data. I have a "test-input" input for that in the reference table with the value 1. I'm trying to do something for that beneath the first comment in the php code.
All I found out from Google is that the problem often occurs if you don't have the right match between the parameters and the value AND OR the correct amount (From db I miss idInlog (auto increment) and patient (which I don't want to collect from the html form)
This is the result in the browser when I print out the post array: (if you need the html I will update the post)Array ( [firstName] => Jefferie [middleName] => Arne [lastName] => Lidebo [alder] => 27 [variationsNamn] => Blindhet [diagnosNamn] => Hjärtfel [harBehandling] => ja [medicinNamn] => Ipren [harLångHistoria] => ja [harNedsattImmun] => ja [missbrukNamn] => Morfin [adress] => Östra Långgatan 8, 1002 [postnr] => 47537 [ort] => Skövde [teleNummer] => 000-000-0000 [eMail] => jefferielidebo@gmail.com [notisSätt] => sms [kommentar] => a comment written by me [submit] => send )
My MySQL code looks like this:
create table forstaInloggning(
idInlog INT NOT NULL AUTO_INCREMENT,
firstName VARCHAR(32),
middleName VARCHAR(32),
lastName VARCHAR(32),
variationsNamn VARCHAR(64),
missbrukNamn VARCHAR(64),
diagnosNamn VARCHAR(64),
medicinNamn VARCHAR(64),
harNedsattImmun BOOLEAN NOT NULL DEFAULT 0,
harLångHistoria BOOLEAN NOT NULL DEFAULT 0,
harBehandling BOOLEAN NOT NULL DEFAULT 0,
adress VARCHAR(32),
postnr CHAR(5),
ort VARCHAR(32),
teleNummer VARCHAR(12),
eMail VARCHAR(32),
notisSätt VARCHAR(32),
kommentar VARCHAR(400),
patient INT,
primary key(idInlog),
foreign key(patient) references Patient(idPat)
)engine=innodb;
This is my php code:
$pdo = new PDO('mysql:dbname=MyDbname;host=Myhost','Myusername', 'Mypassword');
if(isset($_POST['firstName'])){
print_r($_POST);
$insertquery='
INSERT INTO forstaInloggning
(firstName,middleName,lastName,alder,variationsNamn,
diagnosNamn,harBehandling,medicinNamn,harLångHistoria,
harNedsattImmun,missbrukNamn,adress,postnr,ort,
teleNummer,eMail,notisSätt,kommentar,patient)
VALUES
(:firstName,:middleName,:lastName,:alder,:variationsNamn,
:diagnosNamn,:harBehandling,:medicinNamn,:harLångHistoria,:harNedsattImmun,
:missbrukNamn,:adress,:postnr,:ort,:teleNummer,:eMail,:notisSätt,:kommentar,
:patient);';
$stmt = $pdo->prepare($insertquery);
$stmt->bindParam(':firstName',$_POST['firstName']);
$stmt->bindParam(':middleName',$_POST['middleName']);
$stmt->bindParam(':lastName',$_POST['lastName']);
$stmt->bindParam(':alder',$_POST['alder']);
$stmt->bindParam(':variationsNamn',$_POST['variationsNamn']);
$stmt->bindParam(':diagnosNamn',$_POST['diagnosNamn']);
$stmt->bindParam(':harBehandling',$_POST['harBehandling']);
$stmt->bindParam(':medicinNamn',$_POST['medicinNamn']);
$stmt->bindParam(':harLångHistoria',$_POST['harLångHistoria']);
$stmt->bindParam(':harNedsattImmun',$_POST['harNedsattImmun']);
$stmt->bindParam(':missbrukNamn',$_POST['missbrukNamn']);
$stmt->bindParam(':adress',$_POST['adress']);
$stmt->bindParam(':postnr',$_POST['postnr']);
$stmt->bindParam(':ort',$_POST['ort']);
$stmt->bindParam(':teleNummer',$_POST['teleNummer']);
$stmt->bindParam(':eMail',$_POST['eMail']);
$stmt->bindParam(':notisSätt',$_POST['notisSätt']);
$stmt->bindParam(':kommentar',$_POST['kommentar']);
/*The following 2 rows is just a test to see if i needed to insert an extra parameter because it
exist in the database but not in the form*/
$patient = 1;
$stmt->bindParam(':patient',$patient);
$stmt->execute(); /*THIS ROW IS WHERE THE ERROR MESSAGE POINTS TO*/
}