If $row result is '0' then swap for 'null' ?

[wright67uk]

I'm not really too sure which forum to post in, so forgive me if I should of asked this in the sql forum.

 

The below code outputs...

 

4 iron

38.0000,0.0000,72.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,

 

whereas i would now like to change either the PHP or SQL, so that each 0.0000 reads 'null'

 

How can i use the IFNULL keyword if the returned data type is different to the original data type?

(if i can't then what are my alternatives?)

 

How can I achieve the same result using php?

I'm guessing I would need some kind of foreach $row as $avscore statement aswell as an if statement, however im' not certain how to piece everything together.

 

 

echo "4 iron" . "<br/>" ;
$sql= "SELECT month_table.month , IFNULL(AVG( yard ), 0) AS avscore FROM month_table LEFT JOIN `snag` ON month_table.month =
 MONTH(date)AND club = '4 iron' AND user_id = $user_id AND YEAR = $year GROUP BY `month`";
$result=mysql_query($sql);
while($row = mysql_fetch_array($result) )
{$avscore = $row['avscore']; echo $avscore.',';}

[Barand]

The one you want is NULLIF(field,0) in your query

[wright67uk]

I've tried several combinations similar to the command below. basically Im trying to incorporate the NULLIF whilst still keeping 'AVG yard' to display the non-null average values;

 

SELECT month_table.month , AVG(yard), NULLIF(yard,0) AS avscore FROM month_table LEFT JOIN `snag` ON month_table.month =
MONTH(date)AND club = '3 iron' AND user_id = $user_id AND YEAR = $year GROUP BY `month` 

 

This returns ,,,,,,,,,0;

[Barand]

I know from past experience of your posts that this doesn't come easily, but why don't you take a deep breath then tell us what you are trying to do?

[wright67uk]

lol,

 

I have a table (month_table) with a single column (month) listing each month of the year.

I have a table (snag) with columns (yard, club, user_id, date, year...

 

I'm trying to;

 

1) display the average yard for each month of the year

2) display null where any average yard equals zero

[Barand]

Do you want to display the word "null" or just show a blank instead of zero?

[wright67uk]

I want to display the word null

[Jessica]

if($avscore){ 
echo $avscore;
}else{
echo 'NULL';
}

or 

echo (($avscore) ? $avscore : 'NULL');

[wright67uk]

Thankyou. So if I went down the php route, I would end up with somthing like..

 

<?php
$sql= "SELECT month_table.month , AVG( yard ) AS avscore FROM month_table LEFT JOIN `snag` ON month_table.month =
 MONTH(date)AND club = '3 iron' AND user_id = $user_id AND YEAR = $year GROUP BY `month` ";
$result=mysql_query($sql);
while($row = mysql_fetch_array($result) ){$avscore = $row['avscore']. ',' ;
echo(($avscore) ? $avscore : 'NULL');
}
?>

 

NB: removing the ifnull from avg?

[Barand]

If it's the world "null" then that has nothing to do with SQL NULLs, so IFNULL() or NULLIF() do not apply.

 

You could try something like the query below or do it in PHP as sweetheart Jessica suggested.

 

SELECT month_table.month , IF(AVG(yard), AVG(yard), 'NULL') AS avscore
FROM month_table
LEFT JOIN `snag` ON month_table.month = MONTH(date)
   AND club = '3 iron'
   AND user_id = $user_id
   AND YEAR = $year
GROUP BY `month`

 

edit: IMHO "NULL" is not particularly user-friendly, better would be "NONE", "NOT AVAILABLE" or "NO VALUE" or similar

Edited February 2, 2013 by Barand

[wright67uk]

Thank you for this Barand and Jessica! I'm using null as this is a requirement of the highcharts plugin I am using, but that has been noted for future use.

 

In your opinion, what is the best way to learn SQL.... eg. Book vs trial and error vs w3school tutorials etc. ?

[Barand]

My introduction was a FoxPro manual. I remember there was a schema diagram showing the relationships between the various tables (area, salesman, customer, orders, invoice, invoiceItem, products) and a series of questions such as finding those salesmen who had sold product 1 but not product 2 etc. Each question had an example SQL solution.

 

After that it's been trial and error. If a query performs slowly then experiment to improve it. Also reading the manuals and getting to know the internal functions available. I've also learned from the forum here over the years, both from the replies of experienced members and by tackling new problems raised by others.

 

That was the programming side; I was formally trained in the analysis of data requirements, data normalization, modelling data relationships and database design.