MySQL inserting NULL values.

borconi

I have the following MySQL table:

CREATE TABLE `eventorganizer` (
  `id` smallint(5) UNSIGNED NOT NULL,
  `name` varchar(128) NOT NULL,
  `defaultheader` mediumint(8) UNSIGNED DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The following PHP Library for handling data:

->fields(
        Field::inst( 'name' ) ->validator( function ( $val, $data, $field, $host ) {
                        $pos = strpos($val, " ");
                        return ($pos !== false) ?
                                'Name cannot contain spaces.' :
                                true;}
                                ),
        Field::inst( 'defaultheader' )
                ->upload( Upload::inst( __DIR__ .'/advert_images/__ID__.__EXTN__' )
                ->db( 'advert_files', 'id', array(
                    'filename'    => Upload::DB_FILE_NAME,
                    'filesize'    => Upload::DB_FILE_SIZE,
                    'web_path'    => Upload::DB_WEB_PATH,
                    'system_path' => Upload::DB_SYSTEM_PATH
                ))
                ->validator( function ( $file ) {
                    return$file['size'] >= 150000 ?
                        "Files must be smaller than 150K" :
                        null;
                } )
                ->allowedExtensions( array( 'png', 'jpg', 'gif' ), "Please upload an image" ))
        );

And the following JavaScript on the front-end:

   editor = new $.fn.dataTable.Editor( {
       ajax: "eventorganizer_data.php",
        table: "#example",
        fields: [  {
                label: "Organiser",
                name: "name"
                    },
                    {
                label: "Default header:",
                name: "defaultheader",
        type: "upload",
                display: function ( file_id ) {
                    return '<img src="../'+editor.file( 'advert_files', file_id ).web_path+'"/>';
                },
                    default:null,
                clearText: "Clear",
                noImageText: 'No image'
                }
        ]
    });
   

Now if I try to create a new entry without an image the editor will post the following values:

action:create
data[0][name]:EXAMPLE
data[0][defaultheader]:

Which then leads to the following SQL Error: General error: 1366 Incorrect integer value: '' for column 'defaultheader' at row 1

How can I insert null values, or do I need to manually check if the defaultheader in the data array is an empty string and if so replace it with null?

Thank you.

tangerine

Try the ifEmpty formatter.
https://editor.datatables.net/manual/php/formatters#Empty-values

borconi

Thanks @tangerine