Date range in WHERE clause

11 posts by 3 authors in: Forums > CMS Builder
Last Post: May 11   (RSS)

By terryally - May 9

Hi,

I need to display a list of names per year for a four-year period, starting at whatever the current year is. 

I tried the following but this returned every year in the database from 2004 to 2024.

	$thisyear = DATE('Y');
	$futuredate = DATE('Y') +4;

	list($storm_namesRecords, $storm_namesMetaData) = getRecords(array(
	'tableName'   => 'storm_names',
	'loadUploads' => true,
	'allowSearch' => false,
	'where' => 'storm_year >= "'.$date.'"' and 'storm_year <= "'.$futuredate.'"',
	'orderBy'	=>	'storm_year ASC, storm_name ASC',
	));

I then tried the following which returns 2020 - 2023. If I change the 'futuredate' to +5 years, it does the trick but I am wondering what is the correct syntax?

	$thisyear = DATE('Y');
	$futuredate = DATE('Y') +4;

	list($storm_namesRecords, $storm_namesMetaData) = getRecords(array(
	'tableName'   => 'storm_names',
	'loadUploads' => true,
	'allowSearch' => false,
	'where' => "storm_year BETWEEN '$thisyear' AND '$futuredate'",
	'orderBy'	=>	'storm_year ASC, storm_name ASC',
	));

Thanks

Terry

By gkornbluth - May 10 - edited: May 10

Hi Terry,

I know your code references a future date but I'm I assuming that you want to show the last 4 years of storms? So this year you want the names from 2017-2020

or am I not understanding?

Jerry Kornbluth

The first CMS Builder reference book is now available on-line!

Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

By terryally - May 10

Hi Jerry,

It is meant to show the current year +4 years i.e. 2020 - 2024.

What I do not understand is why this code does not produce that result.

The code below actually shows every record in the database up to 2023 and not 2020-2024.

	$thisyear = DATE('Y');
	$futuredate = DATE('Y') +4;

	list($storm_namesRecords, $storm_namesMetaData) = getRecords(array(
	'tableName'   => 'storm_names',
	'loadUploads' => true,
	'allowSearch' => false,
	'where' => 'storm_year >= "'.$thisyear.'"' and 'storm_year <= "'.$futuredate.'"',
	'orderBy'	=>	'storm_year ASC, storm_name ASC',
	));

By gkornbluth - May 10

I thought you mentioned that the 'BETWEEN' code worked

The first CMS Builder reference book is now available on-line!

Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

By gkornbluth - May 10

Hi Terry,

This seems to work in my mockup, but with $thisyear in the 'where' clause it only returns 2021-20204 records and with $lastyear it returns 2020-2024 records

$thisyear = DATE('Y');
$lastyear = DATE('Y') -1;
$futuredate = DATE('Y') +4;

'where' => "storm_year > $lastyear AND storm_year <= $futuredate",

Hope you get the same results.

Stay safe!

Jerry Kornbluth

The first CMS Builder reference book is now available on-line!

Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

By gkornbluth - May 10

Hopefully someone from IT can shed some light...

Jerry Kornbluth

The first CMS Builder reference book is now available on-line!

Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

By gregThomas - May 11

Hey Terry,

This turned out to be an issue that's quite difficult to spot at first. I tried separating the where statement then displaying it:

  $where = 'storm_year >= "'.$thisyear.'"' and 'storm_year <= "'.$futuredate.'"';
  showme($where);

The where statement returned is: 

storm_year >= "2020" 

but you'd expect it to be:

storm_year >= "2020" AND storm_year <= "2024"

this is because the where statement is actually broken up into two strings separated by a PHP and operator(https://www.php.net/manual/en/language.operators.logical.php) so only the first half of the string is used.

Here is some updated code with the issue resolved:

  $thisyear   = DATE('Y');
  $futuredate = DATE('Y') +4;

  list($storm_namesRecords, $storm_namesMetaData) = getRecords(array(
    'tableName'   => 'storm_names',
    'loadUploads' => true,
    'allowSearch' => false,
    'where'       => "`storm_year` * 1 >= $thisyear AND `storm_year` * 1 <= $futuredate",
    'orderBy'     => 'storm_year * 1 ASC, title ASC',
  ));

Adding *1 to the end of the year fields forces MySQL to treat them as integer fields instead of strings.

Cheers,

Greg Thomas

PHP Programmer - interactivetools.com

By terryally - May 11

Hi Greg,

Thanks for your expert eye ... and my bad :(

I am using a text field for the date rather than datetime. :( :(

I would never have spotted this in my lifetime.

Thank you very much. Much appreciated.

Regards

Terry

By gkornbluth - May 11

Thank you Greg,

Learning new things all the time...

Jerry Kornbluth

The first CMS Builder reference book is now available on-line!

Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php