How to create a SQL Query with Between

mmarroquin

Hi Allan, Thank for all your help.

I'm working with a Conditional query, and in my staff.php have this:

Editor::inst( $db, 'mknDetalleAseguradoras' )
    ->fields(
        Field::inst('FechaFactura'),
        Field::inst('Vendedor'),
        Field::inst('FolioFiscal'),
        Field::inst('CantidadMaterial')->validator( 'Validate::numeric' ),
        Field::inst('DenomMaterial'),
        Field::inst('ImportePiezaNeto')->validator( 'Validate::numeric' ),
        Field::inst('ReporteQualitas'),
        Field::inst('Interlocutor')->validator( 'Validate::numeric' ),
        Field::inst('Taller'),  
        Field::inst('Poblacion'),
        )
        ->where('FechaFactura', '2014/07/10')
    ->process( $_POST )
    ->json();

But I need get a date value from $_POST for example:

$fecha = $value_get_by_post

$fecha_inicio = strtotime('-7day', strtotime($fecha));
$fecha_inicio = date('Y-m-d', $fecha_inicio);

And make this query:

->where('FechaFactura', '"BETWEEN ".$fecha_inicio." AND ".$fecha')

My question is: It's Possible, because I try the query but nothing happens.
I can define a normal SQL Query in the staff.php:

SELECT * FROM mknDetalleAseguradoras WHERE FechaFacura LIKE VALUE AND VALUE

Thanks in Advance Allan :D

allan

What you can do is use the following:

    ->where( function ( $q ) use ( $fecha_inicio, $fecha ) {
        $q->where( 'start_date', "BETWEEN ('{$fecha_inicio}') AND ('{$fecha_inicio}')", "", false );
    } )

There is a similar question which I answered in some detail here.

The one difference here is that we need to use parenthesis for the dates to "trick" the Editor library into thinking it is a function so it doesn't try to protect the column identifiers. That is something I will fix in the next release.

Regards,
Allan

mmarroquin

You're awesome Allan Thanks :D