Hi everyone, what I'm getting stuck on is when I initially create a row I'm not filling all the fields. Therefore some fields are 'NULL' to start with. Later on, I put some data in them but if I want to delete that data (i.e. fill with blank) the field is no longer NULL.
This means that my SELECT that lists everything where the cells contain NULL doesnt give the result I want any more! (WHERE variable IS NULL)
Is there a way to do something like: WHERE variable IS NULL OR ""
?
If anyone has any idea then thanks v much. I realise I could fill all fields with blank initially, but it makes my INSERT queries rather long.
SELECT where column could have 2 values.
Moderator: General Moderators
Re: SELECT where column could have 2 values.
Why don't you just go and try it?
Re: SELECT where column could have 2 values.
I can think of several solutions.
The first, and best, is not to let the fields be NULL in the first place. NULL has a very specific meaning. It means 'no value'. A string field that can be blank isn't the same as having no value. So in your database schema you could set all the columns to NOT NULL which would make them all empty strings by default rather than NULL. Problem solved.
The second option is to set the field to NULL when you delete the value rather than setting it to an empty string.
Lastly, if you don't want to change the db structure or the insert code, you could use what you suggested. "WHERE variable IS NULL OR ''" is nearly right... it needs to be "WHERE variable IS NULL OR variable = ''".
The first, and best, is not to let the fields be NULL in the first place. NULL has a very specific meaning. It means 'no value'. A string field that can be blank isn't the same as having no value. So in your database schema you could set all the columns to NOT NULL which would make them all empty strings by default rather than NULL. Problem solved.
The second option is to set the field to NULL when you delete the value rather than setting it to an empty string.
Lastly, if you don't want to change the db structure or the insert code, you could use what you suggested. "WHERE variable IS NULL OR ''" is nearly right... it needs to be "WHERE variable IS NULL OR variable = ''".