insert emply value into smallint column

[DataSpy]

I'm trying to insert a empty value into a smallint column but when it inserts it puts a 0 in instead.  I echo back the data before it inserts and it's empty, is this possible?

 

Any help greatly appreciated, thanks in advance!

[Mchl]

Is the column set to enable empty values (i.e. it is not defined as NOT NULL)?

If yes, then entering NULL value (not the string 'NULL') should work.

If not, then what do you expect. You can't have empty values if you defined a column like this.

[DataSpy]

I have NULL set to Yes and it still doesn't work

[Mchl]

Show how are you trying to insert it.

[DataSpy]

this finds out if the post is empty or not

if it's not empty it submits the notes to the notes table and assigns it the notes id

if it is empty it just assigns a variable the empty post

$drama_notes = mysql_real_escape_string($_POST['drama_notes']);

if (!empty($_POST['drama_notes']))
   {
   $insert_notes = "INSERT INTO notes (
notes_notes
)
VALUES(
'$drama_notes')";

$result_notes = mysql_query($insert_notes) or die(mysql_error());

$drama_notes_id = mysql_insert_id();
   }
else
   {
   $drama_notes_id = mysql_real_escape_string($_POST['drama_notes']);
   }

 

this just inserts the rest of the data into the db

$insert_drama = "INSERT INTO drama (
drama_main_title,
drama_romanization,
drama_foreign_title,
drama_type,
drama_discs,
drama_episodes,
drama_complete,
drama_type2,
drama_format,
drama_extention,
drama_subtitles,
drama_fansub_group_id,
drama_link,
drama_notes_id
)

VALUES(
'$drama_main_title',
'$drama_romanization',
'$drama_foreign_title',
'$drama_type',
'$drama_disc_num ' '$drama_disc_type',
'$drama_episodes',
'$drama_complete',
'$drama_type2',
'$drama_format',
'$drama_extention',
'$drama_subtitles',
'$drama_fansub_group_id',
'$drama_link',
'$drama_notes_id')";

$result_drama = mysql_query($insert_drama) or die(mysql_error());
  
echo "1 record added<br>click <a href=add_drama1.php>here</a> to add another drama!<br>";

 

here's the mysql table in question

CREATE TABLE IF NOT EXISTS `drama` (
  `drama_id` smallint(5) NOT NULL AUTO_INCREMENT,
  `drama_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `drama_main_title` varchar(75) NOT NULL,
  `drama_romanization` varchar(100) NOT NULL,
  `drama_foreign_title` varchar(255) NOT NULL,
  `drama_type` varchar( NOT NULL,
  `drama_discs` varchar(9) NOT NULL,
  `drama_episodes` varchar(5) NOT NULL,
  `drama_complete` varchar(3) NOT NULL,
  `drama_type2` varchar(11) NOT NULL,
  `drama_format` varchar(4) NOT NULL,
  `drama_extention` varchar(3) NOT NULL,
  `drama_subtitles` varchar(4) NOT NULL,
  `drama_fansub_group_id` smallint(3) NOT NULL,
  `drama_link` varchar(175) NOT NULL,
  `drama_notes_id` smallint(5) DEFAULT NULL,
  PRIMARY KEY (`drama_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

 

it's actually odd, I can insert null values into almost everything that says NOT NULL (I have a form that has some things preselected with drop down boxes).  I think I'm having a problem because it's an int column.

 

Thanks for the help!!!!

[Mchl]

Empty string ('') will be cast to 0. You need to explicitly insert NULL.

 

else
   {
   $drama_notes_id = 'null';
   }

 

And remove quotes around '$drama_notes_id' in second insert query.

[DataSpy]

Thank you!!!