我有一个查询,该查询从数据库中打印记录,但我不了解如何忽略某些字段包含零的记录。可能包含零的字段是townhallsorg_pressconfrep_doorsvol_doorscontactsphonescoffeehoursnewsarticlesmediahits。如果我上面提到的每个字段都等于零,我该如何编写查询以忽略那些结果?

// 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/

10-14 13:36
查看更多