MySQL Query with Idiorm and Date Issue

Question

I want to use a chart on some of my data and generate the values based on selected dates (with a date picker). I've stolen most of the stuff on the web and might have only a simple questions.

Here is the Idiorm query:

if (isset($_GET['start']) AND isset($_GET['end'])) {

$start = $_GET['start'];
$end = $_GET['end'];
$data = array();

// Select the results with Idiorm
$results = ORM::for_table('tbl_data')
        ->where_gte('date', $start)
        ->where_lte('date', $end)
        ->order_by_desc('date')
        ->find_array();


// Build a new array with the data
foreach ($results as $key => $value) {
    $data[$key]['label'] = $value['date'];
    $data[$key]['value'] = $value['rev'];
}

echo json_encode($data);
}

The $start and $end are from my datepicker and in yyyy-mm-dd format. The only thing I don't know how to do is how to change the ->where_gte statement. As you can see it's querying the db for the field date. In my db I have three fields, year, month and day.

Is there a way to combing the three fields year, month and day into one expression i.e. maybe ->where_gte('year'&'month'&'day', $start)???

I tried searching and searching but maybe have the wrong keywords or to less knowledge.

Thanks in advance for any help!

Answer 1

Since you have three fields in the DB, you need three where_gte clauses:

...
->where_gte('year', substr($start, 0, 4) // or most suitable date_format
->where_gte('month', substr($start, 5, 2) // or most suitable date_format
...

Hope it helps.