startDate and endDate from 2 Dates

antoniocib

Hi everyone, I am facing a problem that I cannot find any solution, the situation is the following, I have two in my table I have two dates and with the input date I have to search for data in the table and then fill in the datatable , I tried to use this Where clause but it doesn't work, can you help me?

->where( function ($q) {
    if (isset($_POST['startDate'])) {
        $q
        ->where('scarico_bo', $_POST['startDate'], '>=');
        ->or_where('delivery', $_POST['startDate'], '>=');
        $q
        ->where('scarico_bo', $_POST['endDate'], '<=');
        ->or_where('delivery', $_POST['endDate'], '<=');

    }
    else {
        // Dates not submitted - use a condition that can never be true
        $q
        ->where('scarico_bo', '0000-00-00');
        ->or_where('delivery', '0000-00-00');

}})

This way the table kicks out all the data from the database, so I think the Where clause doesn't work.

I use this method of 2 dates with only one input date and it works, so like this:

->where( function ($q) {

        if (isset($_POST['startDate'])) {
            $q
                ->where('scarico_bo', $_POST['startDate'], '=')
                            ->or_where('delivery', $_POST['startDate'], '=');
                            }
                            $q
                            ->where('scarico_bo',null,'!=');
})

I hope I was clear, and I thank everyone who will help me

allan

Hi,

Could you add ->debug(true) just before the ->process(...) call please? Then show me the JSON that the server returns when the table is first loaded. That will contain the SQL so we can see what is going on.

I suspect you'll need to use closures:

    if (isset($_POST['startDate'])) {
        $q
            ->where(function ($r) {
                $r->where('scarico_bo', $_POST['startDate'], '>=');
                $r->or_where('delivery', $_POST['startDate'], '>=');
            })
            ->where(function ($r) {
                $r->where('scarico_bo', $_POST['endDate'], '<=');
                $r->or_where('delivery', $_POST['endDate'], '<=');
            });
    }

The closure function will put parenthesis around the conditions inside it.

Regards,
Allan

antoniocib

Yes @allan works with your way, thanks for availability!

antoniocib

@allan i find another problem when i set the data ex: 15/01/2021 same in the input date the sql kicks out every data when is incluse 15/01/2021

scarico_bo = 14/01/2021 and delivery = 16/01/2021

allan

I think I'll need the debug trace that I mentioned above to be able to understand what is going wrong there. Could you upload the trace and send it over to me please?

Allan

antoniocib

@allan Can you make me an example how i do?

antoniocib

If you for debug you mean this:

{"data":[{"DT_RowId":"row_12174","start":"HUB","cliente":"","pr":"","scarico":"","scarico_m":"","scarico_bo":"11\/01\/2021","scarico_na":null,"epal":"0","perd":"0","ind":"0","dus":"0","mittente":"","dlinea":"","hub":"0","hub_na":"0","dlinea2":"","drit_bo":"","note":"","note_bo":"","delivery":"17\/01\/2021","prezzo":"0","targabo":"","rimorchiobo":"","giro":"0","active_bo":"0"}]

This is the search for 15/01/2021.

allan

Thanks for that - that does help, but it doesn't show what I was looking for unfortunately.

Could you add ->debug(true) just before the ->process(...) call please? Then use the debugger to give me a trace please - click the Upload button and then let me know what the debug code is.

Allan