Execute SQL query

wasimwani

I want to retrieve only records belonging to current month from my table using sql query like

SELECT * from JKBINSR where MONTH(doi) = MONTH(CURRENT_DATE)

Just for experimental purposes i had made an attempt like this but it doesnt seem to work

    ->where( $key = 'MONTH(doi)', $value = 'MONTH(CURRENT_DATE)', $op = '='  )

How can one execute such query?

allan

As I mentioned in the PM conversation you can use the sql() method if you are using the PHP libraries to execute custom SQL: docs.

Complex queries such as that can't be used with Editor though, since it doesn't "understand" SQL functions.

Allan

wasimwani

@allan any code snippet or example to use sql() method

allan

From the documentation I linked to:

$result = $db->sql( 'SELECT * FROM myTable;' );

Expanded using fetchAll():

$result = $db->sql( 'SELECT * FROM myTable;' );
$rows = $result->fetchAll();

Allan

wasimwani

here is what i have tried so far but without success (What actually i was trying to do is to to retrieve only those records whose doi column value is equal to current month e.g only those records should be displayed whose doi value is Feb)

 $result = $db->sql( 'SELECT * from JKBINSR where MONTH(doi) =    MONTH(CURRENT_DATE);' );
$rows = $result->fetchAll();
// Build our Editor instance and process the data coming from _POST

Editor::inst( $db, 'JKBINSR', 'id' )
->fields(
Field::inst( 'accntno' )
->validator( 'Validate::minMaxLen', array( 'empty'=>false, 'min'=>16, 'max'=>16 ) ),
Field::inst( 'accnttitle' )
->validator( 'Validate::notEmpty' ),
Field::inst( 'alias' )
->validator( 'Validate::notEmpty' ),
Field::inst( 'doi' )
->validator( 'Validate::dateFormat', array( 'empty'=>false, 'format'=>'m-d-  y',"message" => "Please enter a date in the format yyyy-mm-dd" ) )
->getFormatter( 'Format::date_sql_to_format', 'm-d-y' )
->setFormatter( 'Format::date_format_to_sql', 'm-d-y' ),
Field::inst( 'doe' )
->validator( 'Validate::dateFormat', array( 'empty'=>false, 'format'=>'m-d-y' ) )
->getFormatter( 'Format::date_sql_to_format', 'm-d-y' )
->setFormatter( 'Format::date_format_to_sql', 'm-d-y' ),
Field::inst( 'amount' )
->validator( 'Validate::notEmpty' ),
Field::inst( 'POLNO' ),
Field::inst( 'remark' )
)
//->where( $key = 'accntno', $value = '590020100000042', $op = '='  )
//->where( $key = $MONTH(doi), $value = $MONTH(CURRENT_DATE), $op = '='  )
->process( $rows )
->json();

wasimwani

@allan Am i wrong here

->process( $rows )

allan

Hi,

I've just been looking into this. The way to restrict the results to just records which match the current month is to use:

    ->where( 'MONTH(doi)', date('m') )

That uses PHP's date() of course. If you want to use CURRENT_DATE it is slightly more complicated as you need to use a closure to access the Query class own where() method (rather than Editor->where()). The reason for this is that it provides the option to not bind the value (which would effectively escape it):

    ->where( function ( $q ) {
        $q->where( 'MONTH(doi)', 'MONTH(CURRENT_DATE)', '=', false );
    } )

Regards,
Allan

wasimwani

@allan : Thanks a lot allan ....that really does my job. Just one little question. What instead of current date we need set of records between range of dates. e.g from 1-feb-2015 till 28-feb-2015. what would the above query be like? I had tried like this

->where( function ( $q ) {
$q->where( 'SUM(amount)', '2015-02-02', '<>', false );
$q->where( 'SUM(amount)', '2015-04-05', '<>', false );
} )

and like this

->where( function ( $q ) {
$q->where( 'SUM(amount)', '["2015-02-02","2015-04-05"]', '<>', false );
} )

ignignokt

I'm not sure how it handles multiple where statements, but I'm pretty sure you don't want to use that operator for a range of dates. <> is the same as !=, so you'd literally need to put in every date that exists in your database minus the ones you want in your results for that to work.

What @allan showed you will give you the same results as a date range for an entire month, but I think a date range would look something like this:

->where( function ( $q ) {
    $q->where( 'doi', '2015-02-01', '>=', false )
    ->and_where( 'doi', '2015-02-28', '<=', false );
} )

Also based on allan's example, it looks like the first argument is the column, not the select. So you would want the SUM(amount) in your select.

wasimwani

@ignignokt The above query gives following error:

DataTables warning: table id=reminder - SQLSTATE[42S22]: Column not found: 1054 Unknown column '2015-02-01' in 'where clause'

wasimwani

@ignignokt The above query gives following error:

DataTables warning: table id=reminder - SQLSTATE[42S22]: Column not found: 1054 Unknown column '2015-02-01' in 'where clause'

allan

Correct, because you are passing in the forth parameter as false which tells it not to bind the value, but rather to execute as SQL. Remove the , false and @ignignokt's suggestion should work nicely.

Allan

wasimwani

@allan That indeed did work :) You again saved my day.

wasimwani

.