我有一个查询,该查询从数据库中打印记录,但我不了解如何忽略某些字段包含零的记录。可能包含零的字段是townhalls
,org_pressconf
,rep_doors
,vol_doors
,contacts
,phones
,coffeehours
,newsarticles
和mediahits
。如果我上面提到的每个字段都等于零,我该如何编写查询以忽略那些结果?
// START Organizer Report
// sending query
$result = mysql_query("SELECT
Concat(last_name, ' , ', first_name) as Representative,
sum(townhalls) as Townhalls,
sum(org_pressconf) as PressConference,
sum(rep_doors) as RepDoors,
sum(vol_doors) as VolunteerDoors,
sum(contacts) as Contacts,
sum(phones) as Phones,
sum(coffeehours) as CoffeeHours,
sum(newsarticles) as NewsArticles,
sum(mediahits) as MediaHits
FROM reports
join representatives on reports.rep = representatives.id
join users on reports.username = users.username
WHERE date BETWEEN '$from' AND '$to' AND role = 'organizer'
Group By representative Order By representative;");
if (!$result) {
die("Query to show fields from table failed");
}
$fields_num = mysql_num_fields($result);
echo "<h2>Organizer Report <small>($from to $to)</small></h2>";
echo "<table border='1' cellspacing='0' cellpadding='3' cellspacing='0' cellpadding='3'>
<tr>";
// printing table headers
for($i=0; $i<$fields_num; $i++)
{
$field = mysql_fetch_field($result);
echo "<td>{$field->name}</td>";
}
echo "</tr>\n";
// printing table rows
while($row = mysql_fetch_row($result))
{
echo "<tr>";
// $row is array... foreach( .. ) puts every element
// of $row to $cell variable
foreach($row as $cell)
echo "<td>$cell</td>";
echo "</tr>\n";
}
mysql_free_result($result);
echo "</table>\n";
// END Organizer Report
最佳答案
如果要忽略所有这些字段均等于零的行,请添加以下HAVING子句。
HAVING (townhalls + org_pressconf + rep_doors + vol_doors + contacts + phones + coffeehours + newsarticles + mediahits) > 0
关于php - 如何省略某些字段中包含零的记录?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/18275765/