Date range in WHERE clause

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

By terryally - May 9, 2020

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 terryally - May 10, 2020

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, 2020

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, 2020

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 terryally - May 10, 2020

Hi Jerry,

That is strange, is it not?

$thisyear should start from 2020 because the operators being used are "greater than or equal to" therefore if 2020 is in the database, it should start at 2020 but if it is not, then it should start at 2021.

When I write it long-hand (as per below) it works as intended. That is the reason that I posted this query because it's not working in CMSB.

	    <?php include getcwd()."/includes/mysql_cyclones_connect.php";
			$date = DATE('Y');
			$futuredate = DATE('Y') +4;
			$query = "SELECT * FROM names WHERE year >= $date AND year <= $futuredate";
			$result = $con->query($query);
			$row = $result->fetch_assoc();

			$CycloneList = array(); 

			foreach ($result as $row) { 
				$Year = $row['year'];
					if (!array_key_exists($Year, $CycloneList)) { 
						$CycloneList[$Year] = array(); 
					}
				$CycloneList[$Year][] = $row; 
			}
		?>

Regards

Terry

By gkornbluth - May 10, 2020

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, 2020

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, 2020

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, 2020

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