This is one example where using prepared statements really saves you some trouble.
In MySQL, in order to insert a null value, you must specify it at INSERT time or leave the field out which requires additional branching:
INSERT INTO table2 (f1, f2)
VALUES ('String Value', NULL);
However, if you want to insert a value in that field, you must now branch your code to add the single quotes:
INSERT INTO table2 (f1, f2)
VALUES ('String Value', 'String Value');
Prepared statements automatically do that for you. They know the difference between string(0) "" and null and write your query appropriately:
$stmt = $mysqli->prepare("INSERT INTO table2 (f1, f2) VALUES (?, ?)");
$stmt->bind_param('ss', $field1, $field2);
$field1 = "String Value";
$field2 = null;
$stmt->execute();
It escapes your fields for you, makes sure that you don't forget to bind a parameter. There is no reason to stay with the mysql extension. Use mysqli and it's prepared statements instead. You'll save yourself a world of pain.
This is one example where using prepared statements really saves you some trouble.
In MySQL, in order to insert a null value, you must specify it at INSERT time or leave the field out which requires additional branching:
INSERT INTO table2 (f1, f2)
VALUES ('String Value', NULL);
However, if you want to insert a value in that field, you must now branch your code to add the single quotes:
INSERT INTO table2 (f1, f2)
VALUES ('String Value', 'String Value');
Prepared statements automatically do that for you. They know the difference between string(0) "" and null and write your query appropriately:
$stmt = $mysqli->prepare("INSERT INTO table2 (f1, f2) VALUES (?, ?)");
$stmt->bind_param('ss', $field1, $field2);
$field1 = "String Value";
$field2 = null;
$stmt->execute();
It escapes your fields for you, makes sure that you don't forget to bind a parameter. There is no reason to stay with the mysql extension. Use mysqli and it's prepared statements instead. You'll save yourself a world of pain.
For fields where NULL is acceptable, you could use var_export($var, true) to output the string, integer, or NULL literal. Note that you would not surround the output with quotes because they will be automatically added or omitted.
For example:
mysql_query("insert into table2 (f1, f2) values ('{$row['string_field']}', ".var_export($row['null_field'], true).")");
Videos
Don't put NULL inside quotes in your update statement. This should work:
UPDATE table SET field = NULL WHERE something = something
You're probably quoting 'NULL'. NULL is a reserved word in MySQL, and can be inserted/updated without quotes:
INSERT INTO user (name, something_optional) VALUES ("Joe", NULL);
UPDATE user SET something_optional = NULL;
I am trying to insert my form data into mysql table there are two required fields and others can be left empty but the problem is the empty fields are inserting blank in sql table instead of NULL value. How do i do this? Here is pastebin code link
If the column has the NOT NULL constraint then it won't be possible; but otherwise this is fine:
INSERT INTO MyTable(MyIntColumn) VALUES(NULL);
2 ways to do it
insert tbl (other, col1, intcol) values ('abc', 123, NULL)
or just omit it from the column list
insert tbl (other, col1) values ('abc', 123)