Insert NULL value into database field with char(2) as type

Question

How can I insert a NULL value into a database field (MySQL) with char(2)?

Before I used mysql_query and I got in the database the following value: NU

Instead of this value there should be NULL.

After reading this question I switched to prepared statements. I still have NU in my database.

Now to my code. I'm reading out line for line of a CSV. Then I make

    if(empty($data[$c])) {
        $data[$c] = NULL;
    }

If I echo $data[1] I get NULL. Then I make my prepared query:

$stmt = $connection->prepare("INSERT INTO table (first, second, ..) VALUES (?, ?, ...)");
$stmt->bind_param("ss...", $data[0], $data[1], ...);
$res=$stmt->execute();

What is wrong?

Edit:

According to Shiplu I did the following:

Do it without giving the last params which causes NU:

$stmt = $connection->prepare("INSERT INTO employee (first, second, ...) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
$stmt->bind_param("sssssssssssssii", $data[0], $data[1], ..., $last);

Using default:

$stmt = $connection->prepare("INSERT INTO employee (first, second, ..., last) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, default(last))");
$stmt->bind_param("sssssssssssssii", $data[0], $data[1], ...);

But still the same result. The char(2) field has default = NULL. How can that be? It seems that the problem is not with the query. I think the easiest solution would be an update query ...

Create table:

CREATE TABLE IF NOT EXISTS `mytable` (
  `id` int(11) NOT NULL auto_increment,
  `text` varchar(128) default NULL,
  `no` int(11) default NULL,
  `problemfield` char(2) default NULL,
  PRIMARY KEY  (`id`),
  FULLTEXT KEY `text` (`text`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=832 ;

`NULL` should't be enclosed in quotes or it is considered a string — Marco, Jan 17 '12 at 15:33
"If I echo $data[1] I get NULL" If you really set it to `NULL`, you shouldn't be getting anything echoed. If it's returning NULL then you actually set it to the string `'NULL'`. What do you get from `var_dump($data[1])`? — Andrew, Jan 17 '12 at 16:52
Both in PHP and SQL, `NULL` is a special value that is not the same as the `'NULL'` string. Otherwise, how could you distinguish a real null? — Álvaro González, Jan 17 '12 at 16:56
@ÁlvaroG.Vicario: I know, but I'm not using it as a string. — testing, Jan 17 '12 at 17:35
@testing: the [sample code](http://sscce.org/) is incomplete without the necessary SQL statements (here, the schema as `CREATE TABLE` statements). — outis, Jan 17 '12 at 20:42

Shiplu Mokaddim · Answer 1 · 2012-01-17T16:04:02.077

If the default value for subject row is not specified or null you can just omit the name from column list in insert statement.

mysql> create table t(
    -> n varchar(20),
    -> a char(2),
    -> primary key(`n`)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> insert into t (n) values ('myname');
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> select * from t;
+--------+------+
| n      | a    |
+--------+------+
| myname | NULL |
+--------+------+
1 row in set (0.00 sec)

Another technique is to use DEFAULT function. But its for MySQL only. I dont know if it works of other RDBMS engine.

mysql> insert into t (a, n) values (default(a), 'myname1');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+---------+------+
| n       | a    |
+---------+------+
| myname  | NULL |
| myname1 | NULL |
+---------+------+

testing · Accepted Answer · 2012-01-18T11:00:23.480

0

I found the problem. I was reading out a csv file. This file was exported with phpmyadmin and it contained NULL strings. My check

if(empty($data[$c])) {
    $data[$c] = NULL;
}

wasn't enough, because it never checked for the string NULL. So the solution is:

if(empty($data[$c]) || $data[$c]=='NULL') {
    $data[$c] = NULL;
}

So Andrew was right.

edited Jan 18 '12 at 11:00

answered Jan 17 '12 at 16:51

testing

19,681
50
236
417

Insert NULL value into database field with char(2) as type

2 Answers2

Linked