1

In my SQL database there're many fields like this: Field Type:Text Null:Yes Default:NULL

My INSERT looks like this:

INSERT INTO tbl (col,col,col,...) VALUES ('val','val','val',...)

Now, those quotes in my INSERT statement's values are inserting '' (empty string) in to the database when what I really want is nothing (NULL).

So I tried

if (isset($_POST['title'])) {$newTitle = mysql_real_escape_string(trim($_POST['title']));} else {$newTitle = NULL;}

and that just inserts 'NULL' - the string containing the word NULL.

What can I do to be certain my NULL values are inserted properly?

Improve this question

4 Answers 4

Reset to default
2

What you have is fine, but you need to combine it with a prepared statement...

 // prepare the statement
    $stmt = $mysqli->prepare("INSERT INTO tbl (title, x,y,z) values (?,?,?,?)");

    $stmt->bind_param($newTitle, $x,$y,$z);

    $x = 'hello, world';
 // execute prepared statement
    $stmt->execute();

If x or newTitle are NULL, they will be NULL in the DB

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

1 Comment

Yes, Prepared statement seem to be the solution here. Guess I'll go learn about them. Thank you!
2

You can try by adding a NULL without the quotes example below:

INSERT INTO tbl (col,col,col,...) VALUES (NULL,'val','val',...)

Also make sure the column that you want to have a pure null must have the allowed NULL ticked.

Improve this answer

3 Comments

The column is set to accept NULL, so I'm sure the database is good. I don't want the value to always be NULL, only if nothing is entered, so hard coding NULL as the value is not an option for me. The heart of the matter is I'm trying to avoid empty strings in my database. When my INSERT values are wrapped in ' (as they should be per SQL syntax) it seems I'm left with either '' (empty string) or 'NULL' (string containing NULL).
With PHP, you have every opportunity to build the SQL string as you want. For each column, simply check the value and either put quotes around it, or use NULL instead.
I think my solution is in this though I have no familiarity with mysqli and prepared statements. Looks like I'm going to have to find a way to add the ' in VALUES ('val','val',...); dynamically.
0

Don't specify the field in INSERT INTO or provide a value. If you have 3 fields, f1 f2 f3 And you

INSERT INTO tbl (f1, f3) VALUES ('something', 'something')

Then f2 will not be inserted and default to null.

Improve this answer

1 Comment

But I do want the value inserted if something has been entered. Otherwise I want it set to NULL
-1

I use '0' instead of null. When you use if statements you can run queries like 

if($row['f2'] == 0){...

Rather than null :)

Improve this answer

7 Comments

This excludes the string '0' from being a valid value of the specific column...
What do you mean? It is a valid string for MySQL and PHP. I use 0 as a string all the time.
You proposed to not set empty columns to NULL, but to 0. Thus, all columns with the value 0 would be assumed to be empty. But if the user really specified the 0 as the value for that field, your code would think the column was empty.
True, I then depends on what the user is typing in. If in this case it is a title then the chances are the title isn't going to be called '0'. If the user does call the title '0' then a simple if statment can prevent it from being inserted to the database and return false.
But why should empty strings be converted to zeros? Empty strings in data bases are valid, correct and wiedely used. Using NULL instead is possible, they even may save a little space. But '0' is larger than '', so why to use it?
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.