MySQL empty string to NULL

Hello,

I would like to ask if there is any option in MySQL to auto-convert empty strings to NULL? The problem is that I use MySQL to store lots of integer values from HTML forms, but there is possibility to leave form empty which result in query looking like this:

$query = "UPDATE table SET number=''";

And with this query MySQL sets number value to '0' instead of NULL ;-(

I was thinking about some string converting with PHP to make query like this:

$query = "UPDATE table SET number=NULL;"

, but this seems to be very unconformtable, because I would have to parse each variable.

Thanks for any help
ElijenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

szewkamCommented:
can you change the structure of database? If yes change type of default value to NULL. Then when you insert empty value MySQL will insert NULL to cell :)
0
dportasCommented:
You can use the NULLIF function.

This may suggest a weakness in your underlying design however. If a value is really not required then why can't your data model accurately represent such cases without a dummy row and a null?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ElijenAuthor Commented:
Thanks for fast reply,

I know about default value, but MySQL always set value to 0 intstead of default one :-(

For example there is one value representing phone number (type: int, null: yes, default: NULL), when user skips this field and sends HITML form, MySQL set the phone number value to 0, which is not intended.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

dportasCommented:
Why would you use INT for a phone number? Why would you want a null phone number? Neither makes much sense to me.
0
szewkamCommented:
strange, i just check it om my localhost.
Value: `test` int(11) DEFAULT NULL
when I add a row without entering the value it has correctly filled with NULL not 0
0
szewkamCommented:
dportas - maybe he has table for users with phonenumber as value. And maybe some users won't want to gave their number. So you have to leave it blank.
0
dportasCommented:
Precisely and that would be an example of a poor design. I would not put a phone number in a user table because there is rarely (probably never) a one-to-one mapping between a user and a phone number.
0
szewkamCommented:
well, that is another thing ;). But there are cases where you can have one-to-one mapping for sure, and some probability of NULL value. I don't think is a very good choice to make another table just to prevent having some NULL values in one table...
0
ElijenAuthor Commented:
dportas> As szewkam said, users have possiblity to leave phone number blank. I use INT because I started with already done MySQL table, but I still can change it. Do you recommend changing it to VARCHAR?

szewkam> How does your UPDATE query look like? Btw I did not mention that when I INSERT new row, default NULL values are set fine.
0
dportasCommented:
The point is that such a "probability of a null" is created by the database designer, not by any "real" aspect of the business domain or the end user or anything external to the database. If a null is present then it exists because the database designer/programmer chose to put it in. Therefore the database designer ought to be able to explain why he put a null in that place rather than model the same information without nulls.

This is a question I always ask when I deal with any problem concerning nulls. If the designer can't justify it then probably the design was the wrong one to start with.
0
ElijenAuthor Commented:
dportas> I don't understand what is wrong about putting phone number in a table ;-)
I have there table called user_info which has about 50 columns (`phonenumber`, `city`, `areacode`, `ICQ`, `email`, ......). How should I store this info?
0
dportasCommented:
You don't yet have a user with more than one phone number or email address?

For personal info the Party Data Model is one common design pattern:
http://www.tdan.com/view-articles/5014/
0
szewkamCommented:
dportas - Having users with more than one e-mail address, or phone number is possible only when you allow them to insert more than one value ;). For most purposes one e-mail address, and one (max two) phone number is enough I think :)

And returning to original question, with updating query you have to you use function mentioned by dportas in first post, NULLIF, see at http://dev.mysql.com/doc/refman/5.1/en/control-flow-functions.html#function_nullif
0
ElijenAuthor Commented:
No, I don't. I realy don't need 1:N or even M:N connections for these basic info. I understand it could be more efective, but designing of a such DB would tak much much longer. And as I said I started with already (partialy) done DB which isn't designed this way.


0
ElijenAuthor Commented:
Oh, yes I see now ... NULLIF is exactly what I need ;-)
0
ElijenAuthor Commented:
Ok, I just tried updating row with phone=NULLIF('$phone', '') and it works fine. So I think solution was found :-)

Thank you very much for help.


0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.