MySQL int column allows null but enters null as zero

Question

I've looked around and have noticed a few people have had the same problem but their oversight doesn't seem to apply here.

I have a PHP function to add an array of values into a table. It first checks whether the values are empty and if so replaces them with NULL, in order to place a null in the table field. Each fields that I'm placing null into allows for null yet places a 0 there instead.

Here's some code:

public static function AddGame($array, $tId)
{
    global $db; // Bring DB into scope
    // Get IDs from particular names
    $locId = $array['fLocation'];
    // Ensure empty values are SQL null
    $hTeamId = "'{$array['fHomeTeam']}'";
    $vTeamId = "'{$array['fVisitTeam']}'";
    $hScore = "'{$array['fHomeScore']}'";
    $vScore = "'{$array['fVisitScore']}'";
    $hHoldFor = "'{$array['fHomeHoldFor']}'";
    $vHoldFor = "'{$array['fVisitHoldFor']}'";
    // Prepare row for insertion
    $row = "'','$tId','$locId',$hTeamId,$vTeamId,'{$array['fDate']}','{$array['fTime']}',$hScore,$vScore,'{$array['fGameType']}',$hHoldFor,$vHoldFor";
    $stmt = $db->prepare("INSERT INTO `game` VALUES($row)");
    if($stmt->execute()) return true;
    else return false;
}

I've debugged this function at various lines and have dumped the $row string and it shows this, which is expected:

'','1','1','21','21','10/10/12','10:30AM','NULL','NULL','pool','NULL','NULL'

Yet when I check the table text type fields literally have the value NULL which is not what I want and also int fields show as 0. If I leave the values blank or as PHP's null then text fields show as empty (or properly null as I'd like) yet the ints still show as 0.

I expect this is only caused due to the way I insert the values indirectly.

Here is the SHOW CREATE TABLE game

CREATE TABLE `game` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tId` int(11) NOT NULL,
  `Lid` int(11) NOT NULL,
  `hTeamId` int(11) DEFAULT NULL,
  `vTeamId` int(11) DEFAULT NULL,
  `date` text NOT NULL,
  `time` text NOT NULL,
  `hScore` int(11) DEFAULT NULL,
  `vScore` int(11) DEFAULT NULL,
  `type` text NOT NULL,
  `hHoldFor` text,
  `vHoldFor` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1

UPDATE:

INSERT INTO `game` VALUES('','1','1','','','10/09/12','9:30AM','','','pool','winner of pool A','winner of pool B')

Mike Mackintosh · Accepted Answer · 2012-08-22 15:53:10Z

8

You are physically setting the value of the wanted NULL columns to a string of 'NULL'

NULL != 'NULL'

If you set your table structure for those columns to NULL, you can omit them from your query and they will automatically be NULL on insert.

Do this instead:

if(!empty($hHoldFor)) $hHoldFor = $array['fHomeHoldFor'];

Update

I was looking further into your example and there is a second point of failure.

$row = ... ",'$hHoldFor','$vHoldFor'";

The above line, if you set $hHoldFor = NULL, will insert quotes around NULL, turning it back into a string of NULL.

Try this:

if(!empty($hHoldFor)) $hHoldFor = "'{$array['fHomeHoldFor']}'";
...  
$row = ... ",$hHoldFor,$vHoldFor";

This removes the single quotes around this value in the QUERY string and adds them to the variable itself.

Update 2

Here is an SQLFiddle using your schema. It returns NULL for NULL columns. Can you do an echo "INSERT INTOgameVALUES($row)"; and post the output? The issue is PHP is converting the NULL to 'NULL' still somewhere. This will help us get to the bottom of it.

Update 3

The issue is just as thought. Your PHP is inserting a blank string, '', into your database, which is not a NULL value. NULL is of a type, '' is a string with no length.

INSERT INTO `game` VALUES('','1','1','','','10/09/12','9:30AM','','','pool','winner of pool A','winner of pool B')

Try this:

public static function AddGame($array, $tId)
{
    global $db; // Bring DB into scope
    // Get IDs from particular names
    $locId = $array['fLocation'];
    // Ensure empty values are SQL null
    $hTeamId = (strlen($array['fHomeTeam']) != 0 ? "'{$array['fHomeTeam']}'" : 'NULL');
    $vTeamId = (strlen($array['fVisitTeam']) != 0 ? "'{$array['fVisitTeam']}'" : 'NULL');
    $hScore = (strlen($array['fHomeScore']) != 0 ? "'{$array['fHomeScore']}'" : 'NULL');
    $vScore = (strlen($array['fVisitScore']) != 0 ? "'{$array['fVisitScore']}'" : 'NULL');
    $hHoldFor = (strlen($array['fHomeHoldFor']) != 0 ? "'{$array['fHomeHoldFor']}'" : 'NULL');
    $vHoldFor = (strlen($array['fVisitHoldFor']) != 0 ? "'{$array['fVisitHoldFor']}'" : 'NULL');
    // Prepare row for insertion
    $row = "'','$tId','$locId',$hTeamId,$vTeamId,{$array['fDate']}','{$array['fTime']}',$hScore,$vScore,'{$array['fGameType']}',$hHoldFor,$vHoldFor";
    $stmt = $db->prepare("INSERT INTO `game` VALUES($row)");
    if($stmt->execute()) return true;
    else return false;
}

edited Aug 22, 2012 at 15:53

answered Aug 22, 2012 at 14:31

Mike Mackintosh

14.3k7 gold badges64 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

14 Comments

Lee Over a year ago

I expected this was the case so I tried it without the quotes too, so = null. Which would be PHP's representation of null. This didn't work either. I'm trying your answer now though, thanks.

Lee Over a year ago

Ok so I defined all the variables as null first to avoid the undefined variables errors. The int fields still show as zero.

Mike Mackintosh Over a year ago

There is a second point of failure which i pointed out in update. Take a look.

Lee Over a year ago

That's a great point thanks. If this works, I need not the conditional for checking empty right? As regardless it'll put the value or the null in?

Lee Over a year ago

ok I did as you said but omitted the empty check is it doesn't seem necessary. I revised the code in the original post but those bloody ints still show as zero.

|

Kermit · Accepted Answer · 2012-08-22 16:19:06Z

5

You can't insert 'NULL'. Remove the single quotes around NULL.

Your string

'','1','1','21','21','10/10/12','10:30AM','NULL','NULL','pool','NULL','NULL'

Should look like

'','1','1','21','21','10/10/12','10:30AM',NULL,NULL,'pool',NULL,NULL

You should also define a column list whenever making an INSERT (ie. INSERT INTO table (col1, col2) VALUES ...)

Edit 1

I would recommend looking through your SHOW CREATE TABLE tbl_name

Edit 2

After testing this, I would still say the problem is with how you're inserting the data.

(18,1,1,21,21,'10/10/12','10:30AM',NULL,NULL,'pool',NULL,NULL)

Works.

('18','1','1','21','21','10/10/12','10:30AM','NULL','NULL','pool','NULL','NULL')

Does not work: Incorrect integer value: 'NULL' for column 'hScore' at row 1:

Edit 3

Here is an improved version of your class:

public static function AddGame($array, $tId)
{
    global $db; // Bring DB into scope

    // Get IDs from particular names
    $locId = $array['fLocation'];

    // Ensure empty values are SQL null
    $hTeamId = empty($array['fHomeTeam']) ? 'NULL' : "'" . $array['fHomeTeam'] . "'";
    $vTeamId = empty($array['fVisitTeam']) ? 'NULL' : "'" . $array['fVisitTeam'] . "'";
    $hScore = empty($array['fHomeScore']) ? 'NULL' : "'" . $array['fHomeScore'] . "'";
    $vScore = empty($array['fVisitScore']) ? 'NULL' : "'" . $array['fVisitScore'] . "'";
    $hHoldFor = empty($array['fHomeHoldFor']) ? 'NULL' : "'" . $array['fHomeHoldFor'] . "'";
    $vHoldFor = empty($array['fVisitHoldFor']) ? 'NULL'  : "'" . $array['fVisitHoldFor'] . "'";

    // Prepare row for insertion
    $row = "$tId,$locId,$hTeamId,$vTeamId,'{$array['fDate']}','{$array['fTime']}',$hScore,$vScore,'{$array['fGameType']}',$hHoldFor,$vHoldFor";
    $stmt = $db->prepare("INSERT INTO game (tId, Lid, hTeamId, vTeamId, date, time, hScore, vScore, type, hHoldFor, vHoldFor) VALUES($row)");
    if($stmt->execute()) return true;
    else return false;
}

Non-NULL values will be encased in quotes, otherwise they are assigned NULL. I've also defined the column list for INSERT and excluded id, as it's an AUTO_INCREMENT column.

edited Aug 22, 2012 at 16:19

answered Aug 22, 2012 at 14:35

Kermit

34.1k13 gold badges89 silver badges121 bronze badges

10 Comments

Lee Over a year ago

That still doesn't work. I mentioned I tried this at the bottom of the post.

Kermit Over a year ago

Can you try the edited version of your string? If this doesn't work, can you reply back with what error you are receiving?

Lee Over a year ago

There's no error, everything inserts which is why I never bothered with defining the column names first as it just adds unneccessary complexity. The problem is the int fields showing zero instead of blank, or (null).

Kermit Over a year ago

@Lee can you post what your SHOW CREATE TABLE tbl_name looks like?

Lee Over a year ago

I posted it. The navicat design view has been lying to me? :S

|

John Woo · Accepted Answer · 2012-08-22 14:30:28Z

1

Does you column allows NULL values? Check the DDL again maybe you have set the DEFAULT VALUE to zero.

When you try to insert null value in a column, don not wrap it with single quote. Example

INSERT INTO tableName (colName, ColNameB) VALUES (1, NULL)

answered Aug 22, 2012 at 14:30

John Woo

265k70 gold badges509 silver badges500 bronze badges

2 Comments

Lee Over a year ago

Yes the check boxes are ticked for allow null and they are also set to default null.

John Woo Over a year ago

when you insert the value, do not wrap it with single quote.

Alex brsp · Accepted Answer · 2016-10-18 18:23:10Z

0

In my case i had to display three situations: Case-empty, Case-yes and Case-no. I planned to use null, one and zero. But the null was being saved always as 0. The column i was working accepted null entries but all my nulls was being saved as zeros. My solution, was considering Case-empty as zero, Case-yes as the number one and case-no as the number two. It is a workaround but solved the problem.

answered Oct 18, 2016 at 18:23

Alex brsp

211 bronze badge

Collectives™ on Stack Overflow

MySQL int column allows null but enters null as zero

4 Answers 4

14 Comments

10 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

14 Comments

10 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related