mysql 不适用于 IS NOT NULL [英] mysql not working for IS NOT NULL

问题描述

所以我有以下查询

   query = "SELECT date_format(startPeriod,'%a, %M, %d, %Y') as startDate,
             date_format(startTime,'%I:%i%p') as startTime,
             date_format(endTime,'%I:%i%p') as endTime,
             jobLocation,jobId,
              hoursWorked
       FROM users,paycheck
       WHERE users.userId = '" . $_SESSION['userId'] .
       "' AND userId = empId
       AND startPeriod IS NOT NULL
       ORDER BY paycheck.startPeriod DESC";

问题是 startPeriod( 这是一个日期) 的一些值是空的，所以当我运行我的查询时，我不想显示那些空的日期，但不幸的是空的日期显示在我的表上.我该如何解决这个问题?

The problem is that some of the values of startPeriod( which is a date) are null so when i run my query I dont want to show those dates which are null, but unfortunately dates that are null show up on my table . How can I fix this?

这是我的源文件中的输出文件

this is my output file on my source

  <tr><td></td><td>12:00AM</td><td>12:00AM</td><td>none</td><td>none</td><td>0.00</td></tr>

在第一个 tds 之间你可以看到它是空的

In between the first tds you can see its empty

推荐答案

您确定插入的数据实际上是 NULL，而不仅仅是空字符串?

Are you sure that the data inserted is actually NULL, and not just an empty string?

之后将 startPeriod IS NOT NULL 替换为 TRIM(startPeriod) <>""

或

query = "SELECT date_format(startPeriod,'%a, %M, %d, %Y') as startDate,
             date_format(startTime,'%I:%i%p') as startTime,
             date_format(endTime,'%I:%i%p') as endTime,
             jobLocation,jobId,
              hoursWorked
       FROM users,paycheck
       WHERE users.userId = '" . $_SESSION['userId'] .
       "' AND userId = empId
       AND (startPeriod != '0000-00-00' OR startPeriod IS NOT NULL)
       ORDER BY paycheck.startPeriod DESC";

希望对你有帮助.

这篇关于mysql 不适用于 IS NOT NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！