Query in editor

antoniocib

Good morning,

I have a problem with these two Where in practice I set the two dates to view the data that must be mandatory and I would like to put the driver's entry as optional.
I just don't understand how to set up the query can you help me?

->where( function ($q) {
    if (isset($_POST['startDate'] )) {

    $q->where('data_bxd', $_POST['startDate'], '>=');
    $q->where('data_bxd', $_POST['endDate'], '<=');
//  $q->where('nome_aut', $_POST['autista'],'=');

}
else{
    $q->where('','0000-00-00');

}
    }
        )
        ->where( function ($q) {
            if (isset($_POST['autista'])) {

            $q->where('nome_aut', $_POST['autista'],'=');

        }
        else if(is_null($_POST)){
            
        }
            }
                )

allan

Reformatted:

->where( function ($q) {
    if (isset($_POST['startDate'] )) {
        $q->where('data_bxd', $_POST['startDate'], '>=');
        $q->where('data_bxd', $_POST['endDate'], '<=');
        //  $q->where('nome_aut', $_POST['autista'],'=');
    }
    else{
        $q->where('','0000-00-00');
    }
})
->where( function ($q) {
    if (isset($_POST['autista'])) {
        $q->where('nome_aut', $_POST['autista'],'=');
    }
    else if(is_null($_POST)){
            
    }
})

That looks perfectly reasonable to me. Are you getting an error or something else? I'm not clear on what the issue is.

Allan

antoniocib

@allan

It is not that I get some error it does not generate any json and as if the query did not work.

the operation I want to have is:

Mandatory date
"Autista" if the select has value, search for "autista" + date, instead if "autista" has not been validated, search only for date.

allan

Add ->debug(true) just before the ->process() call - that will result in the SQL being executed being returned to the client-side. What is the JSON returned please?

Allan

antoniocib

@allan this is:

{"data":[],"options":[],"files":[],"debug":
[{"query":"SELECT `Buono`.`id` as 'Buono.id', `buono`.`id_spedizione` as 'buono.id_spedizione',
 `buono`.`data_spedizione` as 'buono.data_spedizione', 
`buono`.`mittente` as 'buono.mittente',
 `buono`.`prov_mittente` as 'buono.prov_mittente',
 `buono`.`destinatario` as 'buono.destinatario',
 `buono`.`loc_destinatario` as 'buono.loc_destinatario',
 `buono`.`prov_destinatario` as 'buono.prov_destinatario', 
`buono`.`n_bancali` as 'buono.n_bancali',
 `buono`.`n_bxd` as 'buono.n_bxd', 
`buono`.`data_bxd` as 'buono.data_bxd',
 `buono`.`autista` as 'buono.autista',
 `buono`.`prezzo` as 'buono.prezzo',
`buono`.`nolo` as 'buono.nolo',
 `cond`.`zona` as 'cond.zona',
 `cond`.`prov` as 'cond.prov',
 `aut`.`codice_aut` as 'aut.codice_aut',
 `aut`.`nome_aut` as 'aut.nome_aut',
 `buono`.`targa` as 'buono.targa'
 FROM `Buono` 
LEFT JOIN `cond` ON `cond`.`prov` = `buono`.`prov_destinatario` 
LEFT JOIN `aut` ON `aut`.`codice_aut` = `buono`.`autista` 
WHERE `data_bxd` >= :where_0 AND `data_bxd` <= :where_1 AND `nome_aut` = :where_2 ",
"bindings":[{"name":":where_0","value":"2022-05-16","type":null},
{"name":":where_1","value":"2022-05-16","type":null},
{"name":":where_2","value":"","type":null}]}]}

allan

Many thanks - so that is showing:

WHERE
    `data_bxd` >= :where_0 AND
    `data_bxd` <= :where_1 AND
    `nome_aut` = :where_2

And where_2 is being bound as an empty string. So I'd say:

 if (isset($_POST['autista']) && $_POST['autista']) {

is the condition you want (i.e. autista is set, but is an empty string at the moment - you need to take that into account).

Allan

antoniocib

->where( function ($q) {
            if (isset($_POST['autista']) && $_POST['autista']) {

            $q->where('nome_aut', $_POST['autista'],'=');
        }
        else if(is_null($_POST)){
        }
            }
                ) 

@allan works in this way thanks man!