In this tutorial you can learn how to work with NULL in MySQL tables:
Insert, Update, and Select columns with NULL value.
Add column with NULL value
To insert into a MySQL table rows with columns having NULL, add the
NULL value without quotes.
Example:
$sql = "INSERT INTO `table_name`
(`column1`, `column2`, `column3`)
VALUES
('val1', NULL, 'val3')";
Or, if the NULL value is stored into a variable, add "NULL" as a string to that variable, then write the variable into the SQL query, without quotes.
$nul = 'NULL';
$sql = "INSERT INTO `table_name`
(`column1`, `column2`, `column3`)
VALUES
('val1', $nul, 'val3')";
- If you create the $nul value like this:
$nul = NULL; , the SQL query will contain nothing in the place where this variable is added.
Will result:
INSERT INTO `table_name` (`column1`, `column2`, `column3`) VALUES ('val1', , 'val3')
- If you want to insert the
string "NULL", add it within quotes:
$sql = "INSERT INTO `table_name`
(`column1`, `column2`, `column3`)
VALUES
('val1', 'NULL', 'val3')";
UPDATE with NULL value
The same works with UPDATE.
Example:
$sql = "UPDATE `table_name` SET `column1`='val1', `column2`=NULL, WHERE `column3`='val3'";
Or:
$nul = 'NULL';
$sql = "UPDATE `table_name` SET `column1`='val1', `column2`=$nul, WHERE `column3`='val3'";
SELECT fields with NULL value
To select rows in a MySQL table according to columns with NULL value, use
IS NULL.
Example:
$sql = "SELECT * FROM `table_name` WHERE `column` IS NULL";
If you want to Not return the rows with a specific NULL field, use
IS NOT NULL.
$sql = "SELECT * FROM `table_name` WHERE `column` IS NOT NULL";