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

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,

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