Default value problem

[newprimitive]

Hello,
I have problem with entering default values in MYSQL database. When i insert new record using phpmyadmin it works fine (empty fields get default value), but when i use the same query in my application (written in PHP), empty fields in html form don't get default value in MYSQL database. (I tried with NULL and NOT NULL attribute, but result is the same)

I know its beginners mistake but don't know how to solve this.

[smantscheff]

Show the output of your SHOW CREATE TABLE statement and the piece of code where you try to insert something.

[newprimitive]

I found where I made mistake, I sent empty variables to MYSQL, and although those variables were set to NULL, MYSQL treated them as ' '.

For example, user hasn't entered his email address in HTML form but in PHP function that variable was processed and inserted in database as empty string '' or 0 (for INT columns).

function insertUser($username, $email=null){
    $query="insert into user (username, email) values('$username', '$email');
    $result=mysql_query($query);
    so on..
}

call function:

$flag=insertUser($_POST['username'], $_POST['email']);

So, i have to check if there was some empty variable before calling function and exclude it, although this is a bit complicated because there should be many conditions then. Is there some solution better than this?

[smantscheff]

You can prepare your insert statement in PHP and then replace all empty strings '' by NULL:

$query="insert into user (username, email) values('$username', '$email')";
$query = str_replace( "''", "NULL", $query );

[newprimitive]

Actually, those variables already do have NULL value (defined in function definition: $email=null), just if I include them in mysql query, then in that field would not be default value, the field would stay blank.

[smantscheff]

You are mistaking the PHP function parameter for a MySQL input value.
If the PHP value for $email is null, than your query reads: insert into user (username, email) values('somename', ''); because PHP will replace the null value by an empty string.
If you change this query to insert into user (username, email) values('somename', NULL); the database field email will be set to NULL instead of ''.
That's what my str_replace(...) is for.

[newprimitive]

works!
thanks!

[ffghjk]

http://docs.oracle.com/cd/E17952_01/refman-5.1-en/comparison-operators.html

Click Here