mysql 统计非空字段数,计数非空字段mysql

I want to count how many fields of a specific fieldset are empty in mysql, I've found some examples but they all go through the whole table.

Basically I have 8 fields,

listing_photo_1 to listing_photo_8, I want to get the count of how many of them are filled.

I tried:

$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'];

Which results in: 1

To clarify the result I am expecting:

listing_photo_1: filled

listing_photo_2: filled

listing_photo_3: filled

listing_photo_4: empty

listing_photo_5: empty

listing_photo_6: empty

listing_photo_7: empty

listing_photo_8: empty`

The result should be 3

解决方案

You code attempts to count the number of rows where all the fields a not null. You should be using is not null rather than just not null.

To count the number of fields, use this:

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';

To count the number of rows:

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'";

EDIT:

If they are blank, use logic like this:

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';