Query Help

[Anon]

What is wrong with this code? the query execute just fine, but it does not return the correct results. What I need to do is count how many of total events, total killed, total affected happened in each year and print it out, then i also have a query which says "just droughts in that year, how many, killed, affected" those queries work just fine, but the main one doesn't. Any ideas?

for ($year=2001; $year > 1974; $year--)
{

$queryMAIN = "SELECT count(Type) AS counted , sum(Killed) AS summedKilled, sum(Affected) AS summedAffected FROM emdat WHERE Type = 'Drought' OR Type = 'Earthquake' OR Type = 'Epidemic' OR Type = 'Flood' AND country = '$country' AND year = '$newyear'";

$resultMAIN = mysql_query($queryMAIN, $linkID);

?>
<tr>
<td class="year" >&nbsp;<? echo $year; ?></td>
<td class="totalevents" >&nbsp;<? echo $rowMAIN[0]; ?></td>
<td class="totalkilled" >&nbsp;<p align="right"><? echo $rowMAIN[1]; ?></td>
<td class="totalaffected" >&nbsp;<p align="right"><? echo $rowMAIN[2]; ?></td>
<?
}

[webber]

You're using $newyear in your query, while your loop var is $year... could this be the problem?

[Anon]

actually that should be just $year ($newyear = $year anyway)

[Anon]

queryMAIN = "SELECT count(Type) AS counted , sum(Killed) AS summedKilled, sum(Affected) AS summedAffected FROM emdat WHERE (Type = 'Drought' OR Type = 'Earthquake' OR Type = 'Epidemic' OR Type = 'Flood') AND country = '$country' AND year = '$newyear'";

You can get all data with one query if you use a group by clause

queryMAIN = "SELECT year, count(Type) AS counted , sum(Killed) AS summedKilled, sum(Affected) AS summedAffected FROM emdat WHERE (Type = 'Drought' OR Type = 'Earthquake' OR Type = 'Epidemic' OR Type = 'Flood') AND country = '$country' group by year order by year desc";