[RESOLVED] default value ''

[neljan]

Can someone please tell me how I set a db field so that nothing is entered if nothing is submitted? (I'm getting 0's entered instead of blank fields).

Thanks.

[neljan]

Is it because the field type is int?

If I set it to text, would I still be able do sums with numeric values in text fields?

[bradgrafelman]

First off, which DBMS are you using? I'm assuming MySQL for my reply...

neljan;10993248 wrote:

Can someone please tell me how I set a db field so that nothing is entered if nothing is submitted?

From the syntax given on the CREATE TABLE MySQL manual page:

[b][i]column_definition[/i]:[/b]
    [B][i]data_type[/i][/B] [NOT NULL | NULL] [DEFAULT [b][i]default_value[/i][/b]]
...

Note the 'NOT NULL' or 'NULL' specification before the DEFAULT clause; if you don't want to force a column to always have a value, simply define it as NULL rather than NOT NULL (which is the default, IIRC) and either omit the DEFAULT clause (NULL is assumed to be the default value for a NULLable column) or simply define the default value as being NULL.

neljan;10993248 wrote:

(I'm getting 0's entered instead of blank fields).

Well yes, that's because you told MySQL that the default value for the column should be a 0; a default value must be of the same type of the column, so MySQL tries to cast the empty string (which you defined as the default value) into a numeric quantity, which happens to become zero.

In other words, if you want the column to default to NULL rather than zero, then don't tell MySQL that the default value is zero. 😉

neljan;10993252 wrote:

Is it because the field type is int?

Yes and no - see explanation above.

neljan;10993252 wrote:

If I set it to text, would I still be able do sums with numeric values in text fields?

Think about that question: does it make sense that MySQL could perform mathematical operations on text? If you aren't sure, then what does "watermelon" plus "notebook" squared divided by "elbow" equal? 😉

[neljan]

bradgrafelman;10993255 wrote:

if you don't want to force a column to always have a value, simply define it as NULL

That's what I did:

http://i86.photobucket.com/albums/k98/neljanization/Forum/01Dec202212.gif

Even when I edit the table row within phpMyAdmin and delete all the zero's, they re-appear:

http://i86.photobucket.com/albums/k98/neljanization/Forum/02Dec202213.gif

[bradgrafelman]

Show us the query you're using to INSERT and/or UPDATE the rows.

[neljan]

ah that was the problem. I created the columns within myPhpAdmin and although I set them as NULL, a 0 default was added.

Re-created them via sql query and can now see NULL as values, thanks.

Brad wrote:

If you aren't sure, then what does "watermelon" plus "notebook" squared divided by "elbow" equal?

And that's easy, it's "spoon".

[neljan]

sigh all empty submissions are being added to the db as a zero with each new record. So if I submit "23" for $POST['banana'] and leave $POST['apples'] empty, "23" and "0" are entered in the db instead of "23" and nothing (NULL).

The sql I used to create the columns was:

ALTER TABLE table_name ADD column_name INT(3) NULL

(for some reason I couldn't have default ='' (I presume because it's INT, sound right?) regardless, the db structure page within phpMyAdmin indicates that the default value is NULL).

So assuming it's a coding issue, I've tried...

$apples = $_POST['apples'];
	if($_POST['apples'] == '') {$apples = '';}
	$bananas = $_POST['bananas'];
	if($_POST['bananas'] == '') {$bananas = '';}
	$grapes = $_POST['grapes'];
	if($_POST['grapes'] == '') {$grapes = '';}

$apples = $_POST['apples'];
	$bananas = $_POST['bananas'];
	$grapes = $_POST['grapes'];

$apples = $_POST['apples'];
	if($_POST['apples'] == '') {$apples = NULL;}
	$bananas = $_POST['bananas'];
	if($_POST['bananas'] == '') {$bananas = NULL;}
	$grapes = $_POST['grapes'];
	if($_POST['grapes'] == '') {$grapes = NULL;}

$apples = $_POST['apples'];
	if(empty($_POST['apples'])) {$apples = '';}
	$bananas = $_POST['bananas'];
	if(empty($_POST['bananas'])) {$bananas = '';}
	$grapes = $_POST['grapes'];
	if(empty($_POST['grapes'])) {$grapes = '';}

$apples = $_POST['apples'];
	if(empty($_POST['apples'])) {$apples = NULL;}
	$bananas = $_POST['bananas'];
	if(empty($_POST['bananas'])) {$bananas = NULL;}
	$grapes = $_POST['grapes'];
	if(empty($_POST['grapes'])) {$grapes = NULL;}

[bradgrafelman]

The problem is this: if you don't enter any text into a form field, it will be submitted as the empty string (which we've already agreed is not the same as NULL). Since you want this response to be replaced with a NULL value in the database, you have to assign the value to be NULL inside the SQL query, rather than a quoted string value.

In other words, it's the difference between executing this:

UPDATE tbl SET foo = '';

versus this:

UPDATE tbl SET foo = NULL;

The key is replacing the empty string '' in the SQL query with the special keyword NULL.

[neljan]

The key is replacing the empty string '' in the SQL query with the special keyword NULL.

Well even I know that this would work:

safe_query("UPDATE fruit SET number_of_apples = NULL ");

But obviously if a value is submitted, I would like that value to be updated instead (in the traditional manner 🙂 )

$apples = $_POST['apples']; 
if($_POST['apples'] == '') {$apples = NULL;}

safe_query("UPDATE fruit SET number_of_apples = '".$apples."' ");

That's not expected to work then?

[neljan]

(sorry for double post)

I opted to use 2 seperate sql queries, the 2nd being:

if($_POST['apples'] == '') {
// sql update blah blah = NULL
} 

Thanks Brad <3

[bradgrafelman]

neljan;10993335 wrote:

Well even I know that this would work:

safe_query("UPDATE fruit SET number_of_apples = NULL ");

Yes, that's the desired end result, but that isn't what you're telling PHP to do.

neljan;10993335 wrote:

$apples = $_POST['apples']; 
if($_POST['apples'] == '') {$apples = NULL;}

safe_query("UPDATE fruit SET number_of_apples = '".$apples."' ");

That's not expected to work then?

Of course it's not, because you're going to end up with queries that look like:

UPDATE fruit SET number_of_apples = ''

which we've already agreed doesn't match the desired:

UPDATE fruit SET number_of_apples = NULL

Nowhere in your code do you use logic to determine if the single quotes should be added or not (since you don't know whether you need to insert a string value or the NULL vlaue), nor do you ever insert the keyword NULL into the query (that's the four letter word/string N-U-L-L, not to be confused with a NULL value in PHP).

neljan;10993336 wrote:

I opted to use 2 seperate sql queries

Sounds highly inefficient and unnecessary... and more like a sloppy workaround rather than simply fixing the error in your coding. 😉