Prepare statement and placeholder value is no set... null or blank field?

I'm using a prepared statement to insert some values into my table. Before doing so, i'm validating the fields. Some fields are empty and therefore need to remain NULL.

For some reason, on my local development environment, when the field is not initialize, the field in the database remains null... but, on my live development, when a field is not initialize, the field in the database changes to a blank when it's a varchar and 0 when it's a int.

Why is that? I want to remain NULL if no values has been entered in the form!

this is what mysql looks like:

CREATE TABLE album (
   album_id smallint unsigned not null auto_increment,
   title varchar(75) not null,
   subtitle varchar(75),
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

my php script to validate the data:

// validation
$warning = array();

// validate title (required)
$title = trim($_POST['title']);

if ($title == '') {
   $warning['title'] = 'The title is a required field';
}

// validate subtitle if exists (optional)
if (trim($_POST['subtitle']) != '') {
   $subtitle = trim($_POST['subtitle']);
}

my php script if validation passed!

if (!warning) {
   $sql = 'INSERT INTO album (title, subtitle, created) VALUES(?, ?, NOW())';
   $stmt->prepare($sql);
   $stmt->bind_param('ss', $title, $subtitle);
   $stmt->execute();
   $stmt->free_result();

   if ($stmt->affected_rows > 0)
      echo 'Success!';
   else
      echo 'Failed!';
}

If the subtitle has no value (not even set!), shouldn't the field remain NULL (as defined in the database) instead of a blank field?

That's what i thought when designing the model in my local env. But, once on my live site, the reaction is completely the opposite?

Help! How do i make it so it rema开发者_高级运维ins NULL and not a blank field...

Thanks in advance

What's your database framework? both systems with same version?

Seems that you PDO bind_params don't work fine with PDO::bindValue with PDO::PARAM_NULL data_type in one of your systems.

http://php.net/manual/en/pdostatement.bindvalue.php

Edit - 1 --

Ops, and your SQL sentence seems wrong:

 INSERT INTO album (title, subtitle, created) VALUES(?, ?, ?, NOW())';

four values for three fields.

Edit - 2 --

I supose that you do:

 $stmt = $mysqliConn->prepare($sql);
 $stmt->bind_param('ss', $value1, $value2);

and previously

 $value2 = null;

If it is null. In your code you don't assign any value, try to force to assign null.

i'll guess i'll have to explicitly declare $subtitle NULL in the validation code for this to work... weird!

if (trim($_POST['subtitle']) == '') {
  $subtitle = NULL;

} else {
  $subtitle = trim($_POST['subtitle']);
}

The lesson here: never let the system decide what the value of a variable should be... oh well!