0

I have a record from a MySQL Database that needs to be validated to see if all the fields have a value or post some alert.

Is the best way to put all the field values into an array and then check the SUM where NOT NULL or Empty to see if it matches the qty of selected fields?

EXAMPLE: $result = | Value | NULL | NULL | Value | EMPTY |

In this example the Record has values from 5 Fields and 2 of those Fields are not Null or Empty

I need Validate to see when all 5 Fields have some value

Dharman
  • 30,962
  • 25
  • 85
  • 135
Burndog
  • 711
  • 2
  • 8
  • 21
  • Do you still want to retrieve such records from the database, or do you prefer the query to be modified so that such cases are never returned in the result set? – trincot Apr 23 '16 at 11:45
  • Since it is a validation in purpose, either will work. I'm trying to learn what options exist to better my understanding and skillset. If TotalRows==0 could be used if the recordset is empty (MySQL does the work polling for values in all selected fields) – Burndog Apr 23 '16 at 11:55
  • What data types do you columns have? – trincot Apr 23 '16 at 11:58
  • Some are Decimal (4,2) and others are Date. Leaning towards a reverse IN such as WHERE NOT NULL IN (column1, column2, column3) but not sure how to search for NOT NULL or EMPTY in such a query. – Burndog Apr 23 '16 at 12:05
  • SQL Fiddle that does not work by may be in the right direction: http://www.sqlfiddle.com/#!2/7f995/6 – Burndog Apr 23 '16 at 12:16

2 Answers2

1

You could use GREATEST (or LEAST):

SELECT * 
FROM   table1 
WHERE  GREATEST(col1, col2, col3, col4) IS NOT NULL

See SQL fiddle

The idea is that GREATEST needs to check all of the arguments, and because expressions with NULL will return NULL (see here for exceptions to that rule), GREATEST will return NULL if any of its arguments is NULL.

The above will filter the records that meat the condition, i.e. that do no have NULL values. With the same idea you could something that does not filter, but adds the information on whether it has a NULL or not:

SELECT *, 
       CASE WHEN GREATEST(col1, col2, col3, col4) IS NULL 
           THEN 0 
           ELSE 1
       END AS is_OK
FROM   table1

Now you will have an extra column in the result with either 0 or 1. It indicates whether your columns are all not NULL.

Community
  • 1
  • 1
trincot
  • 317,000
  • 35
  • 244
  • 286
  • The only problem is I already know the specific Record. So it is not the selection of the least or greatest Record but rather knowing if all the fields have values or not. – Burndog Apr 23 '16 at 20:51
  • But you can use this. You'd specify the *id* condition in the `WHERE` clause and then see if you get any result. If so, it means the record does not contain `NULL` values. If not, it means it does. See my added paragraph on how you could otherwise use `GREATEST`. – trincot Apr 23 '16 at 20:58
0

you can do it directly in the mysql query

WHERE COALESCE('some_field', '') = ''

this will return every record, where some_field is an empty string or null

more about COALESCE function

kejsu
  • 384
  • 2
  • 5