php - mysql非空字段计数

Question

我想计算mysql中特定字段集有多少字段为空，我找到了一些示例，但它们都遍历了整个表。

基本上我有8个字段，

listing_photo_1到listing_photo_8，我想知道其中有多少张已被填充。

我尝试过:

$result=mysql_query("SELECT count(*) as total from listings 
                       WHERE listing_photo_1 IS NOT NULL AND 
                             listing_photo_2 IS NOT NULL AND 
                             listing_photo_3 IS NOT NULL AND 
                             listing_photo_4 IS NOT NULL AND 
                             listing_photo_5 IS NOT NULL AND 
                             listing_photo_6 IS NOT NULL AND 
                             listing_photo_7 IS NOT NULL AND 
                             listing_photo_8 IS NOT NULL AND 
                             pmpid = '$pmpid'");

$data=mysql_fetch_assoc($result);
echo $data['total'];

结果为:1

澄清我期望的结果:

listing_photo_1:已填充

listing_photo_2:已填充

listing_photo_3:已填充

listing_photo_4:空

listing_photo_5:空

listing_photo_6:空

listing_photo_7:空

listing_photo_8:空`

结果应该是3

Answer 1

您的代码尝试计算所有字段不为空的行数。您应该使用 is not null 而不仅仅是 not null。

要计算字段数量，请使用以下命令:

SELECT sum((listing_photo_1 IS NOT NULL) +
           (listing_photo_2 IS NOT NULL) +
           (listing_photo_3 IS NOT NULL) +
           (listing_photo_4 IS NOT NULL) +
           (listing_photo_5 IS NOT NULL) +
           (listing_photo_6 IS NOT NULL) +
           (listing_photo_7 IS NOT NULL) +
           (listing_photo_8 IS NOT NULL)
          ) as total
from listings
WHERE pmpid = '$pmpid';

计算行数:

SELECT count(*) as total
from listings
WHERE listing_photo_1 IS NOT NULL AND
      listing_photo_2 IS NOT NULL AND 
      listing_photo_3 IS NOT NULL AND 
      listing_photo_4 IS NOT NULL AND 
      listing_photo_5 IS NOT NULL AND 
      listing_photo_6 IS NOT NULL AND 
      listing_photo_7 IS NOT NULL AND 
      listing_photo_8 IS NOT NULL AND 
      pmpid = '$pmpid'";

编辑:

如果它们为空，请使用如下逻辑:

SELECT sum((listing_photo_1 IS NOT NULL and listing_photo_1 <> '') +
           (listing_photo_2 IS NOT NULL and listing_photo_2 <> '') +
           (listing_photo_3 IS NOT NULL and listing_photo_3 <> '') +
           (listing_photo_4 IS NOT NULL and listing_photo_4 <> '') +
           (listing_photo_5 IS NOT NULL and listing_photo_5 <> '') +
           (listing_photo_6 IS NOT NULL and listing_photo_6 <> '') +
           (listing_photo_7 IS NOT NULL and listing_photo_7 <> '') +
           (listing_photo_8 IS NOT NULL and listing_photo_8 <> '')
          ) as total
from listings
WHERE pmpid = '$pmpid';