Why deos PDO SELECT Work but BASIC INSERT fails?-CodePudding

I have connected to the db and able to update a record. I have a variable named "action" that is either "update" or "add".

I use it in a switch statement to set my query to either "SELECT" or "INSERT".

SELECT statement works. INSERT statement does not.

I get this error on $pdo->execute($data).

PHP Fatal error:  Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter number: parameter was not defined in ...

PDOStatement->execute(Array)

The error is thrown by the PDOStatement

Here is what I have tried, seems pretty straight-forward, but i'm struggling with it.

$data = [
    'firstName'=> $firstName,
    'lastName'=> $lastName,
    'badge'=> $badge,
    'department'=> $department,
    'image'=> $image,
    'active'=> $active,
    'stars'=> $stars,
    'email'=> $email,
    'primary_key'=> $primaryKey,
    ];

$sql = "INSERT INTO `team`
(`primary_key`,`firstName`, `lastName`, `badge`, `department`,  `image`, `active`, `stars`, `email`)
VALUES
(NULL, :firstName, :lastName, :badge, :department, :image, :active, :stars, :email)";


$pdo->prepare($sql);
$pdo->execute($data);   <- error is here

When I simply echo my $data array to see if there is something odd. I don't see anything based off all the sites, I've read.

//$data array DATA

primary_key =   
firstName = test
lastName = test
badge = 9000
department = marketing
image = 9000.jpg
active = 1
stars = 0
email = [email protected]

primary_key in db is auto-increment primary_key is $_post[] on update query and NULL insert query (auto increment db column)

Any errors that would prevent this INSERT query from working that you can see? I'm stuck. I know it the array has 9 variables, there are 9 fields to insert, and 9 values listed.

CodePudding user response：

I know it the array has 9 variables, there are 9 fields to insert, and 9 values listed.

Count the parameters. There are 8 of them. The array includes a value called primary_key for which there is no parameter in the query.

primary_key in db is auto-increment

Then don't insert a value for it:

$sql = "INSERT INTO `team`
(`firstName`, `lastName`, `badge`, `department`,  `image`, `active`, `stars`, `email`)
VALUES
(:firstName, :lastName, :badge, :department, :image, :active, :stars, :email)";

And remove primary_key from the $data array.