Member Avatar for 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.

  • 3 Contributors
  • 7 Replies
  • 240 Views
  • 1 Year Discussion Span
  • Latest Post Latest Post by ffghjk
Member Avatar for smantscheff

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

Member Avatar for 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?

Edited by newprimitive because: n/a
Member Avatar for 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 );
Member Avatar for 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.

Edited by newprimitive because: n/a
Member Avatar for 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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.