Update value NULL with php in mysql

Question

I have this query:

UPDATE `terms` 
   SET id = '15',
       taxonomy_id = '1',
       parent_id = NULL,
       level = 0,
       position = 1,
       active = '1',
       time_created = '2012-05-24 09:31:12',
       time_updated = '0000-00-00 00:00:00' 
 WHERE id = 15

And I want to set the parent_id to NULL with php, but how do I do this?
These are wrong:

$term->parent_id = 'NULL'; -> this is a string
$term->parent_id = NULL; -> this is empty
$term->parent_id = 0; -> this is an integer

What's in between the PHP code and the database? i.e. how are these values interpolated? — symcbean
– symcbean, Commented Jun 21, 2012 at 9:13
what wrong with your query? parent_id = NULL will update the field into null. — John Woo
– John Woo, Commented Jun 21, 2012 at 9:14
the query is correct, but I want to set the value with a variable, the code between is not that simple, it's build with classes — Ruben
– Ruben, Commented Jun 21, 2012 at 9:14
@Ruben: Raw PHP won't give you that level of abstraction you seem to be using. We need more details on what you actually use to actually help you. — Tadeck
– Tadeck, Commented Jun 21, 2012 at 9:18
ok, I think something goes wrong in my database class, I'll check that out first — Ruben
– Ruben, Commented Jun 21, 2012 at 9:25

Ruben · Accepted Answer · 2012-06-21 09:52:36Z

there was a problem in my database class, that's why my parent_id value was always empty
this is how I fixed it:

old code:

public function set($data)
    {
        $this->query .= ' SET ';

        if(is_object($data))
            $data = get_object_vars($data);

        if(is_array($data))
        {
            $l = count($data);
            $t = 1;
            foreach($data as $k => $v)
            {
                $this->query .= $k . ' = ' . ((is_string($v)) ? '\'' . $this->_escape($v) . '\'' : $this->_escape($v));
                if($t < $l)
                    $this->query .= ',';
                $t++;
            }   
        }
        else
        {
            $this->query .= $data;
        }


        return $this;
    }

new code:

public function set($data)
    {
        $this->query .= ' SET ';

        if(is_object($data))
            $data = get_object_vars($data);

        if(is_array($data))
        {
            $l = count($data);
            $t = 1;
            foreach($data as $k => $v)
            {
                $this->query .= $k . ' = ';
                if(is_string($v))
                {
                    $this->query .= '\'' . $this->_escape($v) . '\'';
                } elseif (is_null($v)) {
                    $this->query .= 'NULL';

                } else {
                    $this->query .= $this->_escape($v);
                }

                if($t < $l)
                    $this->query .= ',';

                $t++;
            }   
        }
        else
        {
            $this->query .= $data;
        }

        return $this;
    }

oxygen · Accepted Answer · 2012-06-21 13:47:25Z

1

Query should be as :

some query editors requires NULL as null

UPDATE `terms` SET 
    id = '15',
    taxonomy_id = '1',
    parent_id = null,
    level = 0,
    position = 1,
    active = '1',
    time_created = '2012-05-24 09:31:12',
    time_updated = '0000-00-00 00:00:00' 
WHERE 
    id = 15

edited Jun 21, 2012 at 13:47

oxygen

6,0898 gold badges39 silver badges71 bronze badges

answered Jun 21, 2012 at 9:14

Sudantha

16.3k45 gold badges110 silver badges162 bronze badges

5 Comments

John Woo Over a year ago

what's the difference between NULL and null?

Sudantha Over a year ago

@johntotetwoo it depends on his query editor MySQL Query Browser takes as simple null

Prathap Over a year ago

@Sudantha sql is case insensitive language. How do u say that query editors requires NULL as null?

Sudantha Over a year ago

@Prathap yes you are correct its not about SQL im talking about how query editor interprets that

Prathap Over a year ago

@Sudantha may I know some query editor that looks for case sensitive like null as NULL. It will be gud to know...

Collectives™ on Stack Overflow

Update value NULL with php in mysql

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related