MySQL: NULL in field edit

[LotsOfZeros]

Just wondering about this:

Also, I've noticed despite this setup, the field is sometimes empty anyway which throws my queries off when searching for NULL values here.

 

[chatmasta]

Checking the "Null" box means that the value can be null, and it does not require a default value. Further, if you specify the default value as "NULL", that will BE the default value. If that is the case, it should not be inserting empty values. I have noticed that there seems to be a difference in the way various PHP engines (mysql_, mysqli_, PDO, etc.) handle null values. Sometimes it just returns empty, sometimes it returns the PHP "null" equivalent. As a tip, using PDO (Why you Should be using PHP’s PDO for Database Access | Nettuts+) makes all this much more standardized and simple. As another tip, your screenshot is just phpMyAdmin and I would not trust it as reliable insight into the MySQL engine.

 

[LotsOfZeros]

Checking the "Null" box means that the value can be null, and it does not require a default value. Further, if you specify the default value as "NULL", that will BE the default value. If that is the case, it should not be inserting empty values. I have noticed that there seems to be a difference in the way various PHP engines (mysql_, mysqli_, PDO, etc.) handle null values. Sometimes it just returns empty, sometimes it returns the PHP "null" equivalent. As a tip, using PDO (Why you Should be using PHP’s PDO for Database Access | Nettuts+) makes all this much more standardized and simple. As another tip, your screenshot is just phpMyAdmin and I would not trust it as reliable insight into the MySQL engine.

Thanks for the detail. I believe it to be true since I get undesirable results when running WHERE clause searching for NULL. What would you suggest as an alternative to phpMyAdmin? I have used HeidiSQL and like it a lot as well.

 

[chatmasta]

phpMyAdmin is great don't get me wrong. In fact I think it's the best out there. But I'm just saying be aware it's only a tool. The correct query would be

SELECT ... FROM table WHERE col IS NULL

- NOT - :

SELECT ... FROM table WHERE col = NULL
or
SELECT ... FROM table WHERE col = 'NULL'

http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html

 

[SeriousDevver]

And if something isn't null -

SELECT ... FROM table WHERE col IS NOT NULL

That whole empty value issue is one annoyance you'll have to deal with in every programming language. All of them handle it differently, even in PHP you have empty(),which doesn't always return what you expect, and sometimes you need to actually check the length other ways like with (count(my_array) == 0) and the like.

The one that drives me completely insane is Javascript - sometimes you have to use weird functions like isNaN() to check if something isn't a number to figure out if it's empty, because by the time it gets there it's probably gone through 30 goddamn type conversions.

 

[wiley]

phpMyAdmin is great don't get me wrong. In fact I think it's the best out there.

You might wanna give heidisql a try. It's a windows based front-end for MySQL/MariaDB but works fine in Wine on Linux.

 

[Flash4Ever]

What would you suggest as an alternative to phpMyAdmin? I have used HeidiSQL and like it a lot as well.

I have tested HeidiSQL but use and prefer Database Browser.
Try it:
Database Browser - Overview | Database Browser

 

[ionisis]

Also, ANYTHING that is evaluated AGAINST null BECOMES null. So, for example:

set @s = concat(nullfield,' with some other text');
select @s; // returns null

if (@null_variable = 3) // this whole expression becomes null, and cannot ever return "true"

Also, i'm a HUGE MySQL Query Browser advocate (now MySQL Workbench, bleh).