set sql-field with date-format

set sql-field with date-format

MadMax76MadMax76 Posts: 91Questions: 16Answers: 0

when clicking a checkbox I want one field to be set to '1', that works perfect. Additionally I need to keep track of the time when this happened. I have this code for this:

        $('#liste_freigaben').on('change', 'input.editor-active', function () {
            editor
                    .edit($(this).closest('tr'), false)
                    .set({
                        'V_Rechnungern_mit_freig.freig_erledigt': $(this).prop('checked') ? 1 : 0,
                        'V_Rechnungern_mit_freig.freig_angefordert_wann': function () { return new Date(); }
                            })
                    .submit();
        });

As mentioned the first one works, the second doesnt. Here the json-response:

        "query": "UPDATE  [freigabe] SET  [freig_erledigt] = :freig_erledigt, [freig_angefordert_wann] = :freig_angefordert_wann WHERE [ID_freigabe] = :where_0 ",
        "bindings": [
            {
                "name": ":freig_erledigt",
                "value": 1,
                "type": null
            },
            {
                "name": ":freig_angefordert_wann",
                "value": null,
                "type": null
            },
            {
                "name": ":where_0",
                "value": "463",
                "type": null
            }
        ]
    }
}

If I put "2021/03/03" instead of "function () { return new Date(); ", this does get put into the correct "value"-field above, but formatted as text, therefore the sql-server ignores this.

In the server-side I have set this:

        Field::inst( 'V_Rechnungern_mit_freig.freig_angefordert_wann' )
            ->getFormatter( Format::dateSqlToFormat( 'Y-m-d' ))
            ->setFormatter( Format::dateFormatToSql('Y-m-d' )),

What could cause the problem?

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 53,629Questions: 1Answers: 8,318 Site admin
    edited February 22

    set() doesn’t accept (and evaluate) functions as the value. You don’t need a function here anyway - try:

    $('#liste_freigaben').on('change', 'input.editor-active', function () {
        let now = new Date();
    
        editor
                .edit($(this).closest('tr'), false)
                .set({
                    'V_Rechnungern_mit_freig.freig_erledigt': $(this).prop('checked') ? 1 : 0,
                    'V_Rechnungern_mit_freig.freig_angefordert_wann': now.toISOString().split('T')[0] }
                        })
                .submit();
    });
    

    Regards,
    Allan

    edited Remove smart quotes

  • MadMax76MadMax76 Posts: 91Questions: 16Answers: 0
    edited February 21

    Hi Allan,

    the ‘T’ is marked as error in php-storm, if I change it to 'T' then a red mark comes up behind [0] }

    If I just leave it as you suggest, the editor doesnt fire at all for the checkbox.

    Thanks
    Max

  • allanallan Posts: 53,629Questions: 1Answers: 8,318 Site admin

    Oo yes - I had smart quotes in there from somewhere - sorry about that! It should indeed just be 'T'.

    I guess PHPStorm doesn't like the [0] because we should check for the length of the array (although this is one of the cases were we can say for sure that it will have two components, since that is how toISOString works. Let's try:

    $('#liste_freigaben').on('change', 'input.editor-active', function () {
        let now = new Date().toISOString().split('T');
        
        editor
                .edit($(this).closest('tr'), false)
                .set({
                    'V_Rechnungern_mit_freig.freig_erledigt': $(this).prop('checked') ? 1 : 0,
                    'V_Rechnungern_mit_freig.freig_angefordert_wann': now.length ? now[0] : '' }
                        })
                .submit();
    });
    

    Allan

  • MadMax76MadMax76 Posts: 91Questions: 16Answers: 0

    Hi Allan,

    thanks once more - now you have a } to much :-)

    But also after removing that the data send is

    {
        "1": {
            "name": ":freig_angefordert_wann",
            "value": "2021-02-23",
            "type": null
        }
    }
    

    this is interpreted by sql as text, and therefore ignored.

    I have tried this with and without

                ->getFormatter( Format::dateSqlToFormat( 'Y-m-d' ))
                ->setFormatter( Format::dateFormatToSql('Y-m-d' )),
    

    makes no difference.

    completely other points:
    - how can I start a thread as "Priority"?
    - - I did never get emails about updates to threads, although I should according to my settings
    - - I cant comment the one thread I have in priority. For other people I would like to update the solution there.

    Max

  • allanallan Posts: 53,629Questions: 1Answers: 8,318 Site admin
    edited February 23 Answer ✓

    this is interpreted by sql as text, and therefore ignored.

    What database server are you using? SQL Server, MySQL, Postgres? I'm going to guess MySQL based on the note about the value being ignored if it is in error! However, we use MySQL extensively with Editor for this sort of thing and it will correctly see that ISO8601 string as a date. In fact, all of the database servers we support will do.

    Are there any error messages at all from the database server? Can you show me the full query that is being executed?

    Meta stuff

    how can I start a thread as "Priority"?

    When you click the "Ask a question" button you should get a page like this:

    Click the "Category" dropdown and select the category you want. As I say though, it doesn't make much difference.

    I did never get emails about updates to threads, although I should according to my settings

    Have you checked your spam folder?

    I cant comment the one thread I have in priority. For other people I would like to update the solution there.

    I've moved it to "General" - could you try it now? Something odd is going on with the forum permissions!

    Allan

  • MadMax76MadMax76 Posts: 91Questions: 16Answers: 0

    ad threath - works now, my error -THANKS!

    now I see the field for category, I must have been blind up to now.

    And now I also get mails!

    Max

Sign In or Register to comment.