PHP/MySQL Insert null values

by

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.

More Related Contents:

  1. Using PHP read word in textarea line by line and insert to mySQL
  2. How to insert multiple rows from array using CodeIgniter framework?
  3. PHP mysql insert date format
  4. Why I am getting Cannot pass parameter 2 by reference error when I am using bindParam with a constant value?
  5. Incorrect Integer (2147483647) is inserted into MySQL?
  6. Escaping single quote in PHP when inserting into MySQL [duplicate]
  7. MySQL – ignore insert error: duplicate entry
  8. Object of class DateTime could not be converted to string
  9. How to store NULL values in datetime fields in MySQL?
  10. using nulls in a mysqli prepared statement
  11. PHP Check for NULL
  12. php run once and insert twice in mysql database
  13. MySQL and PHP – insert NULL rather than empty string
  14. How to select rows where column value IS NOT NULL using CodeIgniter’s ActiveRecord?
  15. PHP mySQL – Insert new record into table with auto-increment on primary key
  16. Replacing \r\n with PHP
  17. MySQL, how to insert null dates
  18. how to write php code for the key press enter button when fill the form?
  19. Create hyperlink from html table row to open record on a new page
  20. mysql_fetch_array(): supplied argument is not a valid MySQL result resource [duplicate]
  21. SELECT COUNT(*) AS count – How to use this count
  22. How to upload images into MySQL database using PHP code
  23. selecting unique values from a column
  24. How do I set the selected item in a drop down box
  25. Fatal error: Call to undefined function mysqli_result()
  26. How can I employ “if exists” for creating or dropping an index in MySQL?
  27. PHP MySQL pagination with random ordering
  28. Using utf8mb4 with php and mysql
  29. how do I get month from date in mysql
  30. How to write a stored procedure in phpMyAdmin?

Leave a Comment