0

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.

3
Contributors
7
Replies
8
Views
6 Years
Discussion Span
Last Post by ffghjk
0

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

0

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?

Edited by newprimitive: n/a

0

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 );
0

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.

Edited by newprimitive: n/a

1

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.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.