Help with strange sql/datatables issue

Help with strange sql/datatables issue

panzrampanzram Posts: 27Questions: 10Answers: 0

Hi,

A while back my database got updated and I've had many issues ever since. Most I've been able to solve, but one issue still eludes me. Whenever I try to add a new user I get an error message:

SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect integer value: '' for column 'mydb'.'users'.'id' at row 1

I've googled this and, as per the error message, I'm probably sending a blank value for the id field, for example:

INSERT INTO 'users' ('id', 'name') VALUES ('', 'dummy');

The database update forced me to either set every field to nullable or required. I've tried to set a "if blank string then null" statement, and a few other naive attempts without success. My client-side editor looks like this:

    editor = new $.fn.dataTable.Editor( {
        ajax: 'users/users',
        table: "#example",
        fields: [ 
             {
                label: "id",
                name: "id",
                type: "hidden"
            },

My server-side editor looks like this:

Editor::inst( $db, 'users', 'id')
    ->fields(
        Field::inst( 'users.id as id' ),
        Field::inst( 'profiles.user_id as user_id' )->validator( 'Validate::notEmpty' ),

I have a feeling that the solution is rather obvious to a datatables expert. Any advice is greatly appreciated.

Answers

  • tangerinetangerine Posts: 3,057Questions: 24Answers: 357

    I use a Formatter rather than a Validator:

                Field::inst('table.field')->setFormatter( Format::ifEmpty(null) ),
    

    explicitly supplying a null value.

  • panzrampanzram Posts: 27Questions: 10Answers: 0

    Thank you.

    I've tried the setFormatter and ifEmpty before, but I get an error saying that ifEmpty takes 3 parameters and I only supply 1. I guess it has to do with me being on an older version.

    I've been fiddling around with the client-side and the preSubmit event:

    editor.on( 'preSubmit', function ( e, data, action ) {
            if(!isNaN(data.data[0]['id'])) data.data[0]['id']=null;
            console.log(data);
        } );
    

    The data is now null, but I still get the error...still lost.

  • allanallan Posts: 54,186Questions: 1Answers: 8,456 Site admin

    What version are you using? You could try:

    Field::inst('table.field')->setFormatter( 'Format::ifEmpty', null ),
    

    which is the old way of doing it. Make sure you remove the preSubmit on the client-side as well.

    Allan

  • tangerinetangerine Posts: 3,057Questions: 24Answers: 357

    Hi Allan.
    My way is the "old" way? What's the new way then?

  • panzrampanzram Posts: 27Questions: 10Answers: 0

    Hi Alan,

    It looks like i'm on 1.5.4 for the editor...

    Your legacy call suggesion doesn't throw an error, but the original issue is still there.

    SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect integer value: '' for column 'mydb'.'users'.'id' at row 1

    I did remove the clientside, preSubmit call.

  • panzrampanzram Posts: 27Questions: 10Answers: 0

    tangerine, I think he meant that your way is the new way:

    ->setFormatter( Format::ifEmpty(null) ),

    and that this is the old way:

    ->setFormatter( 'Format::ifEmpty', null ),

  • allanallan Posts: 54,186Questions: 1Answers: 8,456 Site admin

    My way is the "old" way? What's the new way then?
    tangerine, I think he meant that your way is the new way:

    Yup. Calling the function is the new way (it returns a function). The old way was passing a string with the function name in it.

    It looks like i'm on 1.5.4 for the editor...

    It'd be worth updating :). We can debug from there if still present.

    Allan

  • tangerinetangerine Posts: 3,057Questions: 24Answers: 357

    Ah, right. Thanks, both.

Sign In or Register to comment.