Displaying and selecting blank fields or columns

Beginners - Learning PHP
#1

Hi Guys
I have been experimenting with CRUD scripts using MySqli and PHP 7 (actually I am using Maria) but I don’t think that is relevant to this question)

I am confused by default field types and the Null checkbox using PhpMyAdmin, and selecting / updating.

In my naive mind (I was brought up programming in BASIC on a 32K Commordore PET and saving my files to a cassette tape !), null empty, isset(), ‘’, ’ ', 0, 0.00, spaces are all confusing me and conflicting.

What I want seems simple to me I want to detect ‘blank’ fields in a mysql database. To clarify this I am interested in fields that look blank or = to zero numerically.

I do everything I can to ‘sanitize’ entries using trim().

I want to be able to say basically in PHP and MySQL SQL if something == ‘blank’, or WHERE something == ‘blank’, something being a $var or a column in a table. I am happy to distinguish between numeric and text fields and use different tests for each.

But simplistically I want if something == ‘blank’ to be true if the contents are nothing, an empty string, a string of spaces, any quantity of 0’s with or without decimal point or not there at all. I have tried PHP(empty) and isset() but MySQL sql does not seem to have an equivalent and = ‘’ fails if it is ’ ’ (a space)

What would be my simplest solution in defining field types, defaults etc in PhpMyAdmin and selecting ‘blank’ both by PHP and SQL. I simply want to be able to find / identify / test any fields / values that would look to a human to be blank or zero and for sort purposes be at the top of the list alphabetically / numerically. I want, in my words, fields that contain nothing, but in programming nowadays, nothing has a whole new meaning.

Thanks guys
Kerry

#2

You really should be doing that in the query itself, not in code.

#3

Hi thanks, but the problem is there are 2 elements here:-
1 - select rows using a mysqli query, this gives you your base set
2 - examine each row for further processing using PHP

I have examined further and decided on the following approach, I will include some explanation as it may help others.

First the actual table / field structure - I decided against NULL because of the complications it can cause by adding another potential value (or non - value!) So I unticked the tickbox for relevant fields in PhpMyAdmin so they are NOT NULL (Null is not allowed) so now I can forget about NULL as it cannot exist. Next, again using PhpMyAdmin, I set text fields as DEFAULT = AS DEFINED: and left the defined value field blank. I set integer and DEC,2 fields as DEFALT = 0. By doing this, I avoid NULL, I do not have to worry about SQL being set to STRICT MODE or not, whereas if I used DEFAULT = NONE then SQL MODE would decide what goes in there. So now my SQL is simply WHERE field = ‘’ for text and WHERE field = 0 for numeric.

Second for checking and futher operations with PHP I can use if(field=='';) for text and if field=0;) for numeric.

I can avoid any errors with unpredictable values, NULLs, spaces etc I dont have to use iisset () or is_null() or emtpty() (which sees 0.00 as not empty !) and makes all my selections / checks and the results reliable. I don’t have to worry about the differences between isset() vs empty() vs is_null()

I do not say this as a recommendation, simply my thoughts, decisions and reasons as it may help others confused with all the possible defaults, possible values and ways of checking.

This gives me a very simple similar syntax for SQL and PHP, I am confident on the default values of my fields and with simple sanitization like TRIM() I am confident of what values the fields may contain. It makes things much simpler for me.

#4

Try this, tell us what the REAL problem is that you are trying to solve rather than telling us about your approach to solving the real problem. This is clearly what is known as an XY Problem.

I want to detect ‘blank’ fields in a mysql database. To clarify this I am interested in fields that look blank or = to zero numerically.

Why? What is all the concern with blank and empty fields?

Post an SQL dump of your DB and give us the high level overview of what you have going on. We dont care HOW you are try to do something. We need to know WHAT you are doing.

#5

Hi
The problem with blank and empty fields is MySQL does not seem to have an empty() function and neither have a blank() function. I was using the term ‘blank’ as an example. The further problem with PHP empty() is it does not treat 0.00 as empty nor a string of spaces.

I thought I did explain the REAL problem as it appears to me when I said:-

What I want seems simple to me I want to detect ‘blank’ fields in a mysql database. To clarify this I am interested in fields that look blank or = to zero numerically.

If I knew enough about different field types and PHP / SQL to identify the problems in those terms I would not have to ask the question. Further, I thought at least posting the solution I found would help clarify what I was asking since often by providing one solution to achieve a result helps clarify.

I don’t know what an XY problem is.

I am looking for the best and hopefully simplest way to identify fields that APPEAR to contain nothing to a non-programmer. In other words numerically zero or textually no visible characters. This will necessarily involve the source (the MySQL field parameters) and the examination (a combination of PHP and SQL.

I can give a database dump if you want but it is really meaningless since part of the problem is how to structure the database.

In any case - I posted my solution since it works and wanted to help others. I was not expecting a solution to my solution but would welcome any advice or improvements.

Thanks for your time.

#6

I get what you are trying to do. What I want to know is WHY? What is the OVERALL purpose of this application?

http://xyproblem.info/

Again, WHY? My experience says your problem, whatever it is, lies elsewhere.

#7

OK benanamen, let me try further, but as I say I think I have sorted it now.

When a user is presented with a form that reads, displays and updates a row, they do not always see the actual contents or value of the columns. A field that looks empty could be null, empty, a zero length string or a string of any number or spaces. Also a user would generally take 0, 0.0 and 0.00 as the same value but certain php funtions do not. As we know, SQL and PHP will ‘view’ these differently from the user and sometimes differently from each other. My goal or desire is to design the database and interrogate, update, process and display it in a way that reproduces the ‘human’ interpretation. So all fields that appear to visually have a value of zero or appear ‘blank’ to a user are actually treated in the way they would expect. And I want to accomplish this as simply and logically as possible. The solution I found seems to accomplish this by setting certain parameters in the database, sanitizing input and allowing a simple test of ‘=0’ for numeric or =’’ for text but I am open to advice. The short answer to your question ‘Why?’, would, without any intention of offending, simply be - because that’s what my user wants.
Thanks for you interest.
Kerry

#8

For some reason you keep refusing to tell me what this application is. Without knowing what this is about you are making it near impossible to give you expert advice. I am pretty sure the purpose of this app is not to detect zeros, nulls and blank spaces. You just keep telling me about your attempted solution.

Then tell me what this application is so I can understand the context of zeros, blanks and nulls.

#9

see this: https://www.w3schools.com/sql/func_mysql_trim.asp
And this: https://www.w3schools.com/sql/func_mysql_char_length.asp
And this: https://stackoverflow.com/questions/17832906/how-to-check-if-field-is-null-or-empty-mysql

But: you should trim fields before inserting them into the database so that your data in your database is always clean!

If you check the NULL checkbox in phpMyAdmin then this column may be empty (NULL) if you insert a new record. if unchecked an error will be thrown that the column must have a value and the record will then not be inserted.

#10

Thanks for all your help guys, like I say - it is sorted now. Yeah I’m trimming fields before saving and It works best for me to uncheck null and set a default. Thanks for the links frankbeen

Sponsor our Newsletter | Privacy Policy | Terms of Service