issues with mysqli prepare

问题:

I have issues with $mysqli->prepare with the following code:

if (!($stmt = $mysqli->prepare("INSERT INTO `Orders` (OrderID,IP.Email.File,Cat,Price,Discount,Size,Scaleby,Emailed,Downloaded,Payment,DateTime) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)"))) {
echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

Current code:

if (!($stmt = $mysqli->prepare("INSERT INTO `Orders` (OrderID,IP,Email,File,Cat,Price,Discount,Size,Scaleby,Emailed,Downloaded,Payment,DateTime) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)"))) {
echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

Error message:

Prepare failed: (1136) Column count doesn't match value count at row 1

code used to make table:

if ($mysqli->query('CREATE TABLE IF NOT EXISTS `Orders` (
ID BIGINT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(ID),
OrderID CHAR(40),
IP CHAR(40),
Email VARCHAR(254),
File VARCHAR(30),
Cat VARCHAR(30),
Price DEC(5,2),
Discount DEC(3,2),
Size VARCHAR(30),
Scaleby DEC(3,2),
Emailed BOOL,
Downloaded BOOL,
Payment VARCHAR(30),
DateTime DATETIME)') === False){
printf("Error: %s\n", $mysqli->error);
}

I have tried removing (...) from INSERT INTO... in an attempt to fix the error but that did not work. I also tried simplifying it to 3 ? marks but it still did not work.

The ? marks are placeholders in a prepared statement

回答1:

The problem isn't the number of columns in the table, it's that there's a typo in the insert statement. You've got "IP.Email.File" instead of "IP,Email,File", so the DB engine thinks you have a different number of columns than literals specified in the insert statement.

INSERT INTO `Orders`

-- 11 columns here, because "IP.Email.File" parses as one column
(OrderID,IP.Email.File,Cat,Price,Discount,Size,Scaleby,Emailed,Downloaded,Payment,DateTime) 

-- 13 values here
VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)