如何使用MYSQL在结果中将0值设为NULL

Question

I have a query that selects a list of customers, and how many times we have performed a service, which I can reset whenever I want. When we have not been there, the value shows up as zero, when I would rather it just not show up at all. How can I do this?

here is the code:

$query = "  SELECT * 
            FROM customers b
            JOIN customerRoutes a
            ON a.customerPK=b.customerPK
            WHERE a.inches <= $inches
            AND a.routesPK = $route_name
            AND $day
            AND b.active=1
            ;";

$result = mysqli_query($con,$query);
    echo "<table border='1'><tr><th>Customers</th><th>Times Visited</th>
                                </tr>";

while($row    = mysqli_fetch_assoc($result))
  {
  echo "<tr>";
  echo "<td> <a href='snow.php?technician=".$technician."&customer=".$row['customerName']."&route=".$row['routesPK']."&service=".$newService."&customerPK=".$row['customerPK']."' target='iframe_a' >".$row['customerName']."</a></td>";
  echo "<td>".$row['occurences']."</td>";
  echo "</tr>";
  }

The trouble is near the bottom, its "OCCURENCES". I've tried using NULLIF but I'm not sure where it should go properly. Any help is appreciated.

Answer 1

You can either do it using PHP, e.g.

echo "<td>" . ($row['occurences'] > 0 ? $row['occurences'] : "") . "</td>";

or you can use SQL as @spencer7593 suggested

SELECT b.customerName, b.customerPK, NULLIF(b.occurrences,0) AS occurrences, ...

As an alternative, you can try the standard case statement

select b.customerName, b.customerPK,
       case b.occurrences when 0 then null else b.occurrences end
from customers b

---

Unrelated, but you should not use select *, rather be explicit and name each column, e.g. select customerName, occurences, ....