[SOLVED] why is field in mysql blank?

[dsaba]

I am entering the values of variables into a mysql database

I know that some values of the variables are GOING to be blank

 

so to prep my mysql database tried 2 different things:

1. i set those fieldnames to "NULL" in mysql with phpmyadmin, and also put a default value

2. i set those fieldnames in mysql to "NOT NULL" and still put a default value

 

 

well when the value of the variable i'm entering into a fieldname is in fact NULL

 

it shows up blank in my database ALL the time, with either of the two methods i tried

the default values i set in phpmyadmin DOES NOT SHOW UP for either method

 

 

so my question is how do I make this work, and why is this happening?

-thank u

 

and if you're wondering if my variables are not being type correctly in the first place

THEY ARE FINE

because when the value of the variable is NOT NULL, the mysql fieldnames of those entered values show up fine

 

 

i'm doing all this on INSERT also if thats any help

[benjaminbeazy]

if you want a default value, it must be NOT NULL

blog and text cant have defaults

 

what version of mysql is it, and what's your insert code look like?

[btherl]

Are you aware that there is a difference between an empty string and null?

 

Edit: To insert null into a database, you must write the word "null", or leave the column unspecified altogether.  If you insert a blank string value into the database, then you will get a blank string, not null.

[dsaba]

my mysql version is 4.1.18

 

here is the part where I assign a value to the variable:

$svideoifwalla = $_POST['svideoifwalla'];

 

the post value can either be NULL or empy string (as I dont know the difference)

or it can be "Yes"

 

here is the part where I insert it into my db:

    $query1 = "INSERT INTO `mainfilelist` ( `fileifwalla` )

VALUES ( '$svideoifwalla' )";

 

$runquery1 = mysql_query( $query1 );

 

 

[dsaba]

ok so what I am doing then is inserting a BLANK STRING

 

what I want is my fieldname to have a default value in the case that it is a blank string

 

do I set the fieldname to NULL and put a default?

 

OR do i set the fieldname to NOT NULL and put a default?

[benjaminbeazy]

NOT NULL, with default

[dsaba]

that DOES NOT WORK

 

thats what i first described in "method 2"

 

doing that still inserts a blank value and ignores the default into my db

 

I don't understand because it used to work before........

[Archadian]

just check to see if its blank before it goes into the DB if it is then insert whatever you set it to insert and if its not blank then use a variable to insert whatever it is in the DB...

[benjaminbeazy]

is it empty, or is it a white space?

[btherl]

Default values are only used when you leave the field unspecified.  That is, if you don't mention it at all in your insert statement.

 

For your case, it may be better to set the default value in php like this:

 

if (empty($svideoifwalla)) {
  $query1 = "INSERT INTO `mainfilelist` ( `fileifwalla` ) values ( 'your_default_value' )";
} else {
  $query1 = "INSERT INTO `mainfilelist` ( `fileifwalla` ) values ( $svideoifwalla )";
}

 

empty() will tell you if nothing was entered into the form field.  An empty form field will always have a blank string value, rather than null.

 

If you do it this way, it doesn't matter if your field is null or not null, and the default value (in sql) is not used.

[dsaba]

ok so you're saying that if you are entering a value with a blank or null string in your INSERT statement

THEN there is not way you can make mysql automatically set a default value for that field

 

 

and therefore:

the ONLY WAY you can make mysql set default values is when you DO NOT MENTION THEM at all in your insert statements

 

 

is that correct?

[Archadian]

yes

 

 

if (empty($svideoifwalla)) { // if this variable is empty then in the INSERT DO NOT MENTION IT

 

$query1 = "INSERT INTO `mainfilelist` ( `fileifwalla` ) values ( 'your_default_value' )";

 

} else { // else if it is NOT empty then mention it here and do what btherl has below

 

  $query1 = "INSERT INTO `mainfilelist` ( `fileifwalla` ) values ( $svideoifwalla )";

 

}

 

 

 

then you can set a default for that particular column in your table

[dsaba]

ok thanks for all the help everyone I will implement new strategies

 

this clears up a lot of confusion on blank strings, null strings, and how mysql communicates with them when inserting default values