Why is MySQL rejecting a null insert?

Databases
1

New coder here trying to figure out why I am getting an error. I have been coding on Could9.io for several months and had no issues with any of my mysql coding. I am now on Codeanywhere and am getting several errors. I’m guessing they are running different mysql versions, or have different settings enabled.

Here is my code for creating the table:

CREATE TABLE tablePages (page_id varchar (150) primary key, page_parent smallint, page_ordernumber smallint, page_sets_id smallint, page_menu_id smallint);

Here is the code I am trying to execute:

INSERT INTO tablePages (page_id, page_parent, page_ordernumber, page_sets_id, page_menu_id) VALUES ('main-menu', '0', '1', '', '1');

I then get this error message:
#1366 - Incorrect integer value: ‘’ for column ‘page_sets_id’ at row 1

I basically want a null value in the 3rd column, as I have PHP code that looks for it.

If I change from 2 single quotes ‘’ to null, then it works. On cloud 9, ‘’ did record as null.

Is there anyway to make it accept 2-single quotes?

This is sort of a big issue, as all of my code for inserting data into my tables, will insert ‘’ if the field is left blank. This is now going to cause issues for all of my tables.

Edit: I have been doing some more reading, and I noted I should post the server settings:

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Apparently STRICT_TRANS_TABLES is the issue. I don’t know how to disable this, or even if I can on Codeanywhere? Anyone have any ideas about this, or should I ask the support staff at Codeanywhere how to disable this?

2

I have been searching in Google for awhile, and there are 2 methods recommended.

  1. Access variables through PHPMyAdmin variables tab and change it that way. However I don’t see any variables tab, so I can’t do this.

  2. Another site indicates just to type this at the mysql promtp: $ mysql -u root -p -e “SET GLOBAL sql_mode = ‘NO_ENGINE_SUBSTITUTION’;”

Are there any concerns I should be aware of before doing #2?

3

If the column does not require a value, you could just leave it out of your INSERT query altogether, and it’ll either insert a null, or something else if you have a default value set up.

Obviously that would mean changing all your queries anyway, which I understand you are reluctant to do, but it does seem a bit strange from a readability point of view that you’re inserting a zero-length string into a numeric column.

But for the question that you asked, sorry, I can’t help on that. There’s a discussion on SO about it where a user posts it as their solution, but then edits their own post to reflect on the other “invalid” stuff that it allows.

4

not just “readability” – it offends common sense

@orclord1 i have very little sympathy for all the queries you would have to change now that you realize that a string (of zero or any length) is the wrong value to attempt to insert into an integer column

1 Like
5

I don’t normally respond when a correct response has already been provided, but this bears repeating. Just don’t do it!

6

Thank you everyone for helping me to learn the proper way to do this. Sadly, I am just following the way I was taught in some night classes I took at a university. I’ve come to realize that pretty much everything they taught was wrong. While I understand that they were just trying to teach us fundamentals to give us a baseline understanding of how everything works – most of it has been unusable.

If I am going to redo all my insert statements now, I want to make sure I am doing it correctly.

How would I “properly” construct an insert statement for variables that might contain no data?

For this statement:

$sql = "INSERT INTO tablePages (page_id, page_parent, page_ordernumber, page_sets_id, page_menu_id)
     VALUES (?,?,?,?,?)";
$stmt = $pdo->prepare($sql);
$stmt->execute([$mod_id, $mod_parent, $mod_orderNumber, $mod_setsId, $mod_menuId]);
$stmt = null;

I cannot figure out a way to write conditional inserts with PHP. I started the code for it, however not only would I have to have a conditional statement for each variable twice (once in the insert and once in the execute), but then I would also have to account for the conditional insertion of the commas between the values. Clearly this is not the way it is done.

This is the only way I can think of to do this, but it requires inserting NULL – however as suggested above, I would prefer not to insert blank variables, but I have no idea how to do it:

$sql = "INSERT INTO tablePages (page_id, page_parent, page_ordernumber, page_sets_id, page_menu_id)
     VALUES (?,?,?,?,?)";
$stmt = $pdo->prepare($sql);
if empty($mod_parent) {$mod_parent = null;}
if empty($mod_orderNumber) {$mod_orderNumber= null;}
if empty($mod_setsId) {$mod_setsId= null;}
if empty($mod_menuId) {$mod_menuId= null;}
$stmt->execute([$mod_id, $mod_parent, $mod_orderNumber, $mod_setsId, $mod_menuId]);
$stmt = null;

Please offer some suggestions/examples for this.

7 
$columns = [];
$params = [];
if (!empty($mod_parent)) { array_push($columns,'page_parent'); array_push($params,$mod_parent); }
//etc etc etc
$sql = "INSERT INTO tablePages (".implode(", ", $columns).") VALUES (".implode(",",array_fill(0,count($columns),"?")).");";
$stmt = $pdo->prepare($sql);
$stmt->execute($params);

You may want to add safety checks (make sure that there’s at least 1 column defined, for example). But that’s the theory.

1 Like
8

Firstly I would set the column’s default value to NULL.
In the INSERT query I would totally ignore that column, it gets no mention in the query, thus gets the default value of NULL.

Note that '' is not NULL, it is an empty string, that’s not the same thing as NULL.
An empty string is still a string, therfore not valid as an integer and not a NULL value either.

1 Like
10

You may be able to handle it in a neater fashion using named parameters instead of question-marks.

$vls = array();
if param is needed {
   $vls[":paramname"] = param-value
   $fieldlist .= ", fieldname " // checking commas
   }

$vallist = ""
foreach $vls as $ix => $contents {
   $vallist .= $ix // checking commas of course
   }
$query = "insert into table ( " .$fieldlist . ") values (" . $vallist . ")"
$db->prepare()
$db->execute($vls)

if you’ll forgive the mixture of PHP and pseudo-code. Because you build an array that contains parameter names and their values, you can use that to build the list of values as well. (By “checking commas” above I mean dealing with having an extra one at the start or finish. I’m not sure anything is bothered if your field list or value list has an extra comma on the end, if it is you’ll need to deal with it).

11

narrator: sorry, that’s not gonna fly

2 Likes
12

As r937 points out, having an extra comma at the end will trigger the engine to think there’s another column name coming, and trip it up when it finds a ) instead.

S’why I had to go A.O.E. to form arrays just to smash them together in post #7.

13

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.