Null and Empty Strings

Anyone who has programmed for any length of time has encountered the concepts of null and empty strings. They are not the same, and confusing the two can cause some serious problems. This article deals with these concepts in the context of PHP and MySQL.

Before I actually start, here’s a little quiz. Is null equivalent to "" ? If you said yes, you definitely need to go through this article, and even if you know that "" is an empty string and not equivalent to NULL, you might want to run through the article.

Let’s start by talking about this concept in the context of PHP. The definition of null for PHP goes like this: "The special NULL value represents that a variable has no value. NULL is the only possible value of type NULL."

Note: The null type was introduced in PHP 4.

A variable is considered to be NULL if


  • it has been assigned the constant NULL.


  • it has not been set to any value yet.


  • it has been unset().


Furthermore, there is only one value of type NULL, and that is the case-insensitive keyword NULL.


<?php

$var = NULL;

?>


Possible cases where you may misunderstand this and run into problems are:


  1. $var is an empty string


$var = "" ;

$z = is_null($var) ;

return $z ; // returns false


OR


$var = ” ;

$z = is_null($var) ;

return $z ; // returns false



  1. $var is an empty array


$var = array() ;

$z = is_null($var) ;

return $z ; // returns false


The cases where null behaves as it is intended to are: 

$var = null ; // case is not a problem – NULL, null, Null ..are all same

$z = is_null($var) ;

return $z ; // returns true


// $var is undefined or unset or uninitialized

$z = is_null($var) ;

return $z ; // returns true


In most cases we are used to displaying the contents of $var in "if" style i.e.,


if ( $var ) { // returns true when var has value and $var is not equal to zero

echo $var ; // or do some processing

}


This way of coding has a problem: whenever $var has zero value, it will not return true. To escape this predicament, sometimes one uses is_null(), which again causes the problems I already listed above.

{mospagebreak title=Making Null Safe in PHP}

A safe way to to handle this problem would be to use a custom isNull function, like so:

function isNull($var) {

 

if(is_null($var)) {

return true ;

} else if((string)$var == "") { // $var == "" will return true if $var = 0 that’s why we need to cast into string

return true ;

} else if(count($var) < 1) { // return true even if its an empty array

return true ;

} else {

return false ;

}

 

}



Now the functional code becomes:


If ( isNull($var) ) { // returns true when var has value and $var is not equal to zero

echo $var ; // or do some processing

}


If your code is all object-oriented, you can put the function in one of the general classes as shown below:


Class GenClass

{


// class vars

….


function isNull()

{

…….

}



} // end of class


The functional code in this case becomes:


If ( $genClass->isNull($var) ) { // returns true when var has value and $var is not equal to zero

echo $var ; // or do some processing

}

{mospagebreak title=Null and MySQL}

Now let’s look at null in the context of MySQL. The principles are the same here; only the application differs.

Here, NULL means "a missing unknown value" and is treated somewhat differently from other values. To test for NULL, you cannot use the arithmetic comparison operators such as =, <, or <>. So, for handling null you are left with only is null and is not null operators.


mysql> select ” IS NULL, ” IS NOT NULL;

+————+—————-+

| ” IS NULL | ” IS NOT NULL |

+————+—————-+

| 0 | 1 |

+————+—————-+


So by definition NULL is just NULL , and anything which you might enter into the column is not NULL, including the empty string ( "").

I discuss the possible cases where you might run into problems next. We’ll start with an example table for the discussion. 

Table structure for example :


CREATE TABLE `example` (

`id` int(2) NOT NULL auto_increment,

`name` varchar(20) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


The way this code is written, it’s very possible to enter an empty string into the table rather than null. In the above example table, if you execute following query:


$query = "INSERT INTO `example` ( `id` , `name` )
VALUES (

”, ‘$var’) ";


If $name is empty, you have just entered an empty string into the table and not a null value. The updated table looks like this:


mysql> select * from example;

+—-+——+

| id | name |

+—-+——+

| 2 | |

+—-+——+

1 row in set (0.00 sec)


Again, taking the above table for our example, suppose you execute the following:

$query = "INSERT INTO `example`(`id`) values(”) " ;


Here what goes into the "name" column depends on the SQL mode in effect.


  • If a strict SQL mode is not enabled, MySQL sets the column to the implicit default value for the column data type.


  • If a strict mode is enabled, an error occurs for transactional tables and the statement is rolled back. For non-transactional tables, an error occurs, but if this happens for the second or subsequent row of a multiple-row statement, the preceding rows will have been inserted.


Implicit defaults for a non-strict SQL mode for MySQL datatypes are as follows:


  • For numeric types, the default is 0, with the exception that for integer or floating-point types declared with the AUTO_INCREMENT attribute, the default is the next value in the sequence.


  • For date and time types other than TIMESTAMP, the default is the appropriate "zero" value for the type. For the first TIMESTAMP column in a table, the default value is the current date and time.


  • For string types other than ENUM, the default value is the empty string. For ENUM, the default is the first enumeration value.


  • BLOB and TEXT columns cannot be assigned a default value.

Most probably your SQL mode will not be strict, as by default the strict mode is not on. So, in this case you again entered an empty string and not a null value!

The following are the updated values:


mysql> select * from example;

+—-+——+

| id | name |

+—-+——+

| 2 | |

| 3 | |

+—-+——+

2 rows in set (0.00 sec)


In both of the cases above we have entered empty strings, which may be confused with null. To make matters worse, we may actually enter null values in the column later.

{mospagebreak title=Handling Null in MySQL}

We will enter a null value to make things more clear. The query for that would be as follows:

// before inserting a value alter the column to accept null values

ALTER TABLE `example` CHANGE `name` `name` VARCHAR( 20 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL


$query = "INSERT INTO `example` (`id`, `name` ) values (”, null) " ;



mysql> select * from example;

+—-+——+

| id | name |

+—-+——+

| 2 | |

| 3 | |

| 4 | NULL |

+—-+——+

3 rows in set (0.00 sec)


Now, suppose, you want to select all of the tuples which have null values. The query for this would be as follows:


Select * from example where name is null ;


This will return id = 4


mysql> Select * from example where name is null ;

+—-+——+

| id | name |

+—-+——+

| 4 | NULL |

+—-+——+

1 row in set (0.03 sec)


Select * from example where name = ” ;


This will return id = 1,2


mysql> Select * from example where name = ” ;

+—-+——+

| id | name |

+—-+——+

| 2 | |

| 3 | |

+—-+——+

2 rows in set (0.00 sec)


In any practical case, you would want to get all three values because they are all empty. For this you will have to use the following query:


Select * from example where name is null OR name = ”;

mysql> Select * from example where name is null OR name = ”;

+—-+——+

| id | name |

+—-+——+

| 2 | |

| 3 | |

| 4 | NULL |

+—-+——+

3 rows in set (0.00 sec)


And this code becomes a safe way for SQL to handle results when you are not sure what you are handling, null or empty string.

Before I finish, there’s a "gotcha" I would like to mention. Let’s insert one more row with id as NULL:


mysql> INSERT INTO `example` (`id`, `name` ) values (”, ‘finalrow’);

Query OK, 1 row affected, 1 warning (0.00 sec)


mysql> select * from example where id IS NULL;

+—-+———-+

| id | name |

+—-+———-+

| 5 | finalrow |

+—-+———-+

1 row in set (0.00 sec)


Now, this looks weird but there’s an explanation for it. For the benefit of some ODBC applications (at least Delphi and Access), the following query can be used to find a newly inserted row:


SELECT * FROM tbl_name WHERE auto IS NULL;

–http://dev.mysql.com/doc/mysql/en/ODBC_and_last_insert_id.html


All further executions of the same statement provide the expected result:


mysql> select * from example where id IS NULL;

Empty set (0.00 sec)

I hope you now understand the difference between null and an empty string, and how to safely handle them in PHP and MySQL. Good luck!

[gp-comments width="770" linklove="off" ]

chat sex hikayeleri Ensest hikaye