Column count doesn't match value count at row 1

[knight fyre]

I keep getting this error but I've check and done everything that I can think of. The code is below.

Code that generates the error

$reservationNum = $_POST['resNum'];
$guestRoomNum = $_POST['guestNum'];

$guestObj = $mysql->query("SELECT * FROM admissiondata
WHERE
reservationNum = $reservationNum") or die ($mysql->error);

$guestData = $guestObj->fetch_object();

$roomObj = $mysql->query("SELECT * FROM roomdata
WHERE
roomNum = $guestData->guestRoomNum") or die ($mysql->error);

$roomData = $roomObj->fetch_object();

$mysql->query( "INSERT INTO guestHistory
VALUES
($guestData->reservationNum),
($guestData->guestNum),
('$guestData->firstName'),
('$guestData->mI'),
('$guestData->lastName'),
($guestData->creditCard),
($guestData->cellNumber),
($guestData->homeNumber),
($guestData->workNumber),
($guestData->numOfDays),
($guestData->charge),
($guestData->canceled),
($guestData->guestRoomNum),
('$roomData->roomType')" ) or die ($mysql->error);

Table Definition - Guest History

CREATE TABLE guestHistory (
reservationNum INT(6) NOT NULL,
guestNum INT(7) NOT NULL,
firstName VARCHAR(30) NOT NULL,
mI VARCHAR(1),
lastName VARCHAR(30) NOT NULL,
creditCard BIGINT(16),
cellNumber BIGINT(10),
homeNumber BIGINT(10),
workNumber BIGINT(10),
numOfDays SMALLINT(3),
charge FLOAT(6,2),
canceled BOOL,
guestRoomNum TINYINT NOT NULL,
roomType VARCHAR(10)
);


Table Definition - Room Data

CREATE TABLE roomData (
roomNum TINYINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
roomType VARCHAR(10),
roomRate FLOAT(6,2),
roomStatus VARCHAR(8)
);


Table Definition - Admission Data

CREATE TABLE admissionData (
reservationNum INT(6) NOT NULL,
guestNum INT(7) NOT NULL PRIMARY KEY AUTO_INCREMENT,
firstName VARCHAR(30) NOT NULL,
mI VARCHAR(1),
lastName VARCHAR(30) NOT NULL,
creditCard BIGINT(16),
cellNumber BIGINT(10),
homeNumber BIGINT(10),
workNumber BIGINT(10),
numOfDays SMALLINT(3),
charge FLOAT(6,2),
canceled BOOL NOT NULL,
guestRoomNum TINYINT NOT NULL )
);

[CFMaBiSmAd]

Form your query string in a variable so that you can echo it (your data - one of the numeric values - might have a comma in it that is treated as syntax that is part of the query) and are you sure that is the INSERT query that is producing the error?

[guelphdad]

small thing to note, change your FLOAT columns to DECIMAL. FLOAT is subject to rounding errors and shouldn't be used for the type of data you are storing in it.

[knight fyre]

Form your query string in a variable so that you can echo it (your data - one of the numeric values - might have a comma in it that is treated as syntax that is part of the query) and are you sure that is the INSERT query that is producing the error?

Yes, the insert query causes the problem.
I echoed out the contents and everything seems to be in order. Here's the data.

Data

Res Num: 651092
Guest Num: 3
First Name: Jay
Middle Name: D
Last Name: Joe
Credit Card: 1234123412341234
Cell Num: 8764384444
Home Num: 8764384444
Work Num: 8764384444
Num of Days: 12
Charge: 12
Chancelled: 0
Guest Rm Num: 3
Guest Rm Num: Smoking

[knight fyre]

Found the error. I only needed one set of brackets for all the VALUES not a bracket for each value.

The below code worked.

$mysql->query( "INSERT INTO guestHistory
VALUES
($guestData->reservationNum,
$guestData->guestNum,
'$guestData->firstName',
'$guestData->mI',
'$guestData->lastName',
$guestData->creditCard,
$guestData->cellNumber,
$guestData->homeNumber,
$guestData->workNumber,
$guestData->numOfDays,
$guestData->charge,
$guestData->canceled,
$guestData->guestRoomNum,
'$roomData->roomType')" ) or die ($mysql->error);

[CFMaBiSmAd]

Umm. Upon future review of what you posted, you have parenthesis () around each value. That makes each value an insert statement in a multi-insert query. There should only be one pair of parenthesis (), starting before the first value and ending after the last value. Forming the query in a string and actually echoing it wound have probably made it easy to see what was wrong with the query.