A MySQL/PHP question

[bigmac]

I have been driving myself mad trying to work this out....
I have a web page that compares monthly weather stats for several years. A visitor to the page can select the month they want to view. See here http://www.menstonweather.co.uk/monthcompare.php
For most values I am returning, I have worked out the correct MySQL queries and PHP code to display "N/A" if the month hasn't happened yet, i.e. in the current year.
But one particular stat I am trying to return counts the number of days where the minimum temp dropped below zero. Now obviously for some months this will be zero, e.g. July (in the UK), but also, if the month hasn't yet happened (for the current year) the value will also be zero.
This is the query I am using:

Code: Select all

$resulttysmbz=mysql_query("SELECT COUNT(MinTemp) as belowzero FROM Dayfile WHERE YEAR(LogDate)=YEAR(NOW()) AND MONTH(LogDate)=$mo AND MinTemp<0") or die('Error: '.mysql_error());

$mo is a numerical value representing the user selected month from the drop down box.

And this is the code that I test the returned value with, to output the result:

Code: Select all

<?php if (mysql_num_rows($resulttysmbz) === false) {?>
					<td><?php echo "N/A"?></td>
					<?php } else {?>
					<?php while($row=mysql_fetch_object($resulttysmbz)){?>
					<td><?php echo $row->belowzero;?></td>
					<?php
					}
					?>
					<?php
					}
					?>

I thought that if the month for the current year hasn't happened yet, there would be no results returned and therefore the "false" condition would result, but this doesn't seem to work, it simply displays "0".

You will see that the same problem occurs for the "No. of days without rain" variable as well.

What am I doing wrong?

[tobyspond]

Hi,

Your query actually returns a value (0), so the false statement does not happen.

You could try using a NULLIF statement with the count, that will return NULL in your mysql query, then you could set your php statement to if belowzero is NULL then "N/A" I have not tested this so not sure if it will work.

Kerry

[mcrossley]

I can't see a simple one query solution, I expect Kerry can work it out though
I'd do a range check first..

SELECT 1 FROM Dayfile WHERE YEAR(LogDate)=YEAR(NOW()) AND MONTH(LogDate)=$mo LIMIT 1

if that returns a row, then fetch the count..

SELECT COUNT(1) as belowzero FROM Dayfile WHERE YEAR(LogDate)=YEAR(NOW()) AND MONTH(LogDate)=$mo AND MinTemp<0

[bigmac]

Hmmm, thanks Kerry. I'm not sure I am understanding it correctly.

Using this in my SELECT statement:

Code: Select all

<?php $resulttysmbz=mysql_query("SELECT COUNT(NULLIF(MinTemp,0)) as belowzero FROM Dayfile.......

still returns a value of 0 when I run it directly against my database in phpMyAdmin, rather than "NULL".

Have I misunderstood it's usage? My understanding is that if the COUNT of temperatures less than 0 is zero, that is compared against the 0 in the NULLIF part, and should then return a NULL.

[tobyspond]

Try this

SELECT NULLIF(COUNT( case when MinTemp<0 then 1 else null end),0) as belowzero...

Kerry

[bigmac]

Thanks Kerry, that works in terms of a NULL being returned if the month hasn't happened, but I guess I can't use mysql_num_rows to display "N/A" in this instance.

I've looked at various ways of checking to see if a variable is null, and if(is_null($var)) seems to crop up, but this doesn't work for me. The "is_null" doesn't seem to be recognised as a correct php function.

I'll keep playing and see if I can get the right combo.

Thanks again.

[tobyspond]

try adding this to the end of your original query after the where clause: having count(mintemp)<0

by adding the having clause the query returns an empty set

if false does not work then instead of false use empty

Update: this may do it for you

$resulttysmbz=SELECT year(logdate) as year, NULLIF(COUNT( case when MinTemp<0 then 1 else null end),0) as belowzero from Dayfile WHERE YEAR(LogDate)=YEAR(NOW()) AND MONTH(LogDate)=$mo ";

$result= mysql_query($resulttysmbz)or die('Error: ' . mysql_error());
while ($qValues=mysql_fetch_assoc($result))
if (is_null($qValues["belowzero"]))
echo "N/A";
else
echo $qValues["belowzero"];

good luck,

Kerry

[bigmac]

Thanks again Kerry. Had already tried a similar thing to what you had suggested, still no luck.
In the end I have gone with Mark's suggestion and done a double query, i.e. check if the month exists and if so then do the check on the number of days.
This is the code that seems to work ok:

Code: Select all

<?php $existcheck=mysql_query("SELECT 1 FROM Dayfile WHERE YEAR(LogDate)=YEAR(NOW()) AND MONTH(LogDate)=$mo LIMIT 1") or die('Error: '.mysql_error());?>
					<?php if (mysql_num_rows($existcheck) != 0) {?>
					<?php $resulttysmbz=mysql_query("SELECT COUNT(MinTemp) as belowzero from Dayfile WHERE YEAR(LogDate)=YEAR(NOW()) AND MONTH(LogDate)=$mo AND MinTemp<0") or die('Error: ' . mysql_error());?>
					<?php while($row=mysql_fetch_object($resulttysmbz)){?>
					<td><?php echo $row->belowzero;?></td>
					<?php
					}
					?>
					<?php } else { ?>
					<td><?php echo "N/A"?></td>
					<?php
					}
					?>

Thanks again for your help.

[tobyspond]

Happy to hear that you found a method that worked.

Enjoy your weekend,

Kerry