Zend: How to insert NULL values into MySQL

Question

I am using Zend Framework with MySQL,Apache and Ubuntu 9.04.

I am trying to insert NULL values into database like this:

$personObj->setPersonId( '1' );
$personObj->setPersonEmail('NULL');
$personObj->save();

But 'NULL' is stored in database as string and not NULL.

When I use this:

$personObj->setPersonId( '1' );
$personObj->setPersonEmail(NULL);
$personObj->save();

But nothing happens and previous entry is unchanged.

What should I do to insert NULL values into MySQL?

When I use $personObj->setPersonEmail('NULL'); It inserts NULL as string. When I use $personObj->setPersonEmail(NULL); previous entry is unchanged and nothing happens. — Naveed, Nov 13 '09 at 06:34
What type of object is `$personObj`? As others already said, without knowing the implementation of `setPersonEmail()` you cannot determine what has to be passed in. — Stefan Gehrig, Nov 13 '09 at 07:59
$personObj is person's record object. setPersonEmail() is a mothod to set the value for personEmail entry in database. save() is a method to store all set values into database.It is a common practice in zend framework. As I said that it is working for String values perfectly but problem is NULL entry. — Naveed, Nov 13 '09 at 08:06
That does not help... It's crucial to know the implementation `setPersonEmail()` and `save()`; especially important is how the data is written to the database. A lot of things can happen in between calling `save()` and the time when the data is written to the database. — Stefan Gehrig, Nov 13 '09 at 10:16

Joseph Montanez · Accepted Answer · 2010-06-01T17:55:36.103

17

If you are not modifying any of the values after they are assigned then

new $personObj->setPersonEmail(new Zend_Db_Expr('NULL'));

edited Jun 01 '10 at 17:55

answered Nov 13 '09 at 06:49

Joseph Montanez

3

This worked for me too, but I have to do `new Zend_Db_Expr('NULL')` – Jake N Jun 01 '10 at 15:12

score 1 · Answer 2 · answered Nov 13 '09 at 06:23

1

First thought would be straight passing in the null keyword, without quotes around it. As pavium said, the quotes around it turn it into a string.

answered Nov 13 '09 at 06:23

Tarka

I have tried this $personObj->setPersonEmail(null); But no luck. Actually I am updating this entry. An email is already exists in database and I want to remove this and insert NULL. – Naveed Nov 13 '09 at 06:28
Could try empty quotes, though that would likely just make it an empty string. Have you tried accessing the column more directly, rather than through the set function? ie: $personObj->personEmail = null; – Tarka Nov 13 '09 at 06:48

score 0 · Answer 3 · answered Nov 13 '09 at 06:20

0

I think putting NULL in quotes is what makes it look like a string.

I don't know about your method, but for a direct insert though a mysql INSERT command, the single quotes around a NULL are incorrect.

answered Nov 13 '09 at 06:20

pavium

I have also tried this $personObj->setPersonEmail(NULL); But nothing happened. Previous entry is unchanged. – Naveed Nov 13 '09 at 06:30
1

It depends **entirely** on how the `setPersonEmail()` method works, then, and whether it is valid for *updating* an entry. – pavium Nov 13 '09 at 06:45
Yes it updates. When I use this $personObj->setPersonEmail('test@test.com'); It replace previous database entry with test@test.com. But my problem is to remove previous entry and insert NULL if an user remove his/her email address. – Naveed Nov 13 '09 at 07:14

3 Answers3