Date range in WHERE clause

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

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',
	));

I thought you mentioned that the 'BETWEEN' code worked

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

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

Hopefully someone from IT can shed some light...

Jerry Kornbluth

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

Thank you Greg,

Learning new things all the time...

Jerry Kornbluth