Question: Do the number of columns in a mysqli db table, excluding an auto-increment column, have to match exactly the number of values in the INSERT query for a prepared statement?
I have got a mysql table with three columns.
CREATE TABLE IF NOT EXISTS `secure_user_data` (
`user_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`first_name` varchar(32) NOT NULL,
`last_name` varchar(32) NOT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
An INSERT query using a prepared statement with the two values, first_name and last_name, works well.
/* This works fine with the above table. */
if ($stmt = $mysqli->prepare("INSERT INTO secure_user_data (first_name, last_name) VALUES (?,?)")) {
$stmt->bind_param('ss', $first_name, $last_name);
}
$stmt->execute();
However, if I create a new column, user_name, in the table the above INSERT query won't insert a record.
CREATE TABLE IF NOT EXISTS `secure_user_data` (
`user_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`first_name` varchar(32) NOT NULL,
`last_name` varchar(32) NOT NULL,
`user_name` varchar(32) NOT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
If I add the 3rd value, user_name, to the INSERT query it will insert a record into the table with the immediately above.
/* This works with auto-increment column plus the three more columns. */
if ($stmt = $mysqli->prepare("INSERT INTO secure_user_data (first_name, last_name, user_name) VALUES (?,?,?)")) {
$stmt->bind_param('sss', $first_name, $last_name $user_name);
}
$stmt->execute();
My table might eventually end up with twenty or thirty columns. I am trying to figure out how I can start with the minimal number of INSERT query values and then let the user fill in the rest as he can find the time.
Thank you in advance.
