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
Gordon Linoff :
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.\n\nTo count the number of fields, use this:\n\nSELECT sum((listing_photo_1 IS NOT NULL) +\n (listing_photo_2 IS NOT NULL) +\n (listing_photo_3 IS NOT NULL) +\n (listing_photo_4 IS NOT NULL) +\n (listing_photo_5 IS NOT NULL) +\n (listing_photo_6 IS NOT NULL) +\n (listing_photo_7 IS NOT NULL) +\n (listing_photo_8 IS NOT NULL)\n ) as total\nfrom listings\nWHERE pmpid = '$pmpid';\n\n\nTo count the number of rows:\n\nSELECT count(*) as total\nfrom listings\nWHERE listing_photo_1 IS NOT NULL AND\n listing_photo_2 IS NOT NULL AND \n listing_photo_3 IS NOT NULL AND \n listing_photo_4 IS NOT NULL AND \n listing_photo_5 IS NOT NULL AND \n listing_photo_6 IS NOT NULL AND \n listing_photo_7 IS NOT NULL AND \n listing_photo_8 IS NOT NULL AND \n pmpid = '$pmpid'\";\n\n\nEDIT:\n\nIf they are blank, use logic like this:\n\nSELECT sum((listing_photo_1 IS NOT NULL and listing_photo_1 <> '') +\n (listing_photo_2 IS NOT NULL and listing_photo_2 <> '') +\n (listing_photo_3 IS NOT NULL and listing_photo_3 <> '') +\n (listing_photo_4 IS NOT NULL and listing_photo_4 <> '') +\n (listing_photo_5 IS NOT NULL and listing_photo_5 <> '') +\n (listing_photo_6 IS NOT NULL and listing_photo_6 <> '') +\n (listing_photo_7 IS NOT NULL and listing_photo_7 <> '') +\n (listing_photo_8 IS NOT NULL and listing_photo_8 <> '')\n ) as total\nfrom listings\nWHERE pmpid = '$pmpid';\n",
2013-09-07T15:53:54