CODEWITHSUNDEEP
phpmysql
Joey Azar
Joey Azar

Reputation: 152

php 7.2 is preventing insert record without adding all the table's fields

I upgraded my php version from 7.1 to 7.2.

In my phpmyadmin I have a table like this one :

CREATE TABLE `images` (
  `image_id` int(11) NOT NULL,
  `image_name` varchar(255) CHARACTER SET utf8 NOT NULL,
  `image_title` varchar(255) CHARACTER SET utf8 NOT NULL ,
  `image_alternative` varchar(255) CHARACTER SET utf8 NOT NULL ,
  `image_folder` varchar(255) CHARACTER SET utf8 NOT NULL ,
  `image_status` int(11) NOT NULL ,
  `date` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

image_id is the only required field. The rest, all optional and can be empty. When I'm trying to insert a record :

INSERT INTO `images`(`image_name`,`image_status`) VALUES ("new image",1);

I'm getting this error

 MySQL said: Documentation #1364 - Field 'image_title' doesn't have a default value

To solve this issue I had to add in the table a predefined value for all fields or in my query to mention all fields even if they are empty.

INSERT INTO `images`(`image_name`, `image_title`, `image_alternative`, `image_folder`, `image_status`) 
VALUES ("new image,"","","",0)

I understand that php 7.x is more secure from the old versions, and they are trying to make better with time. But how can I solve this issue without the need to change all my tables or my queries. Thank you

Upvotes: 0

Views: 1389

Answers (2)

Clomp
Clomp

Reputation: 3308

For future reference, here are some tips to remember:

  1. To make columns optional, simply replace the NOT keyword with the DEFAULT keyword. Remember: "Default Null" is optional. "Not Null" is required.
  2. The image_id column should be an AUTO_INCREMENT field. Adding it prevents allowing INSERT INTO ... SQL statements from failing, whenever the image_id column name & its value aren't explicitly inserted in a query. (Both of the INSERT INTO queries in the OP are missing those items.)
  3. The image_id column is also the PRIMARY KEY for this table. That should be added.

The table should look like this after those simple edits:

CREATE TABLE `images` (
`image_id` int(11) NOT NULL AUTO_INCREMENT,
`image_name` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`image_title` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`image_alternative` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`image_folder` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`image_status` int(11) DEFAULT NULL,
`date` datetime DEFAULT current_timestamp(),
 PRIMARY KEY (`image_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

Upvotes: 1

deceze
deceze

Reputation: 522145

The only possible explanation I can find for this would be connected to the removal of the sql.safe_mode ini setting:

sql.safe_mode boolean

If turned on, database connection functions that specify default values will use those values in place of any user-supplied arguments. For details on the default values, see the documentation for the relevant connection functions.

If that was on in your previous PHP version and somehow caused your chosen database function to set SQL strict mode to off, meaning your SQL session operated in non-strict mode, then that would explain the difference:

For data entry into a NOT NULL column that has no explicit DEFAULT clause, if an INSERT or REPLACE statement includes no value for the column, or an UPDATE statement sets the column to NULL, MySQL handles the column according to the SQL mode in effect at the time:

  • If strict SQL mode is enabled, an error occurs for transactional tables and the statement is rolled back. For nontransactional tables, an error occurs, but if this happens for the second or subsequent row of a multiple-row statement, the preceding rows will have been
    inserted.

  • If strict mode is not enabled, MySQL sets the column to the implicit default value for the column data type.

https://dev.mysql.com/doc/refman/8.0/en/data-type-defaults.html

Either way, you should explicitly choose which SQL mode you want to operate in and not leave it up to implicit defaults. And you should prefer to run in strict mode and either make your columns nullable, or provide explicit default values for them.

Upvotes: 0

Related Questions