Insert NULL to mySQL-DB instead of 0000-00-00 using PHP Class/function

I have a little problem with my PHP-class/function which adds data to my mySQL DB. There is a form in html/php which calls a function on submit to insert data into the DB. Inside the form there is a date-field which is optional to fill out.

When I now pass the form data to my function to insert to the database and the user entered no date, mySQL always inserts “0000-00-00” to the date-field (pt_duedate) instead of NULL. But in case user does not provide a duedate, I want mysql to insert NULL. If user provides a date, I want mysql to insert that date.

My current workaround is to fire a second query to set pt_duedate=NULL where pt_duedate=’0000-00-00′ but I don’t think thats a good way to do this.

I read several topics here and also on Google but could’t solve my problem.

Hope you guys have any ideas/hints for me.

    public function addDiary($pid, $vals = array() )
    {
        $query = $this->pdo->prepare("INSERT INTO tbl_project_diary SET
                                        pt_date = :date,
                                        pt_category = :category,
                                        pt_type = :type,
                                        pt_responsible = :responsible,
                                        pt_duedate = :duedate,
                                        pt_text = :text,
                                        pid = :pid,
                                        pt_created_by = :created,
                                        pt_updated_by = :updated");
        
        $x=1;
        foreach($vals as $val){
            $val = esc($val);
            $query->bindValue($x, $val);
            $x++;
        }
        $query->bindParam("pid", $pid, PDO::PARAM_INT);
        $query->bindParam("created", $_SESSION['usr_id'], PDO::PARAM_INT);
        $query->bindParam("updated", $_SESSION['usr_id'], PDO::PARAM_INT);
        $query->execute();
    }

**Database table tbl_project_diary**
pt_date DATE
pt_category VARCHAR
pt_type = VARCHAR
pt_responsible = INTEGER
pt_duedate = DATE
pt_text = TEXT
pid = INTEGER
pt_created_by = INTEGER
pt_updated_by = INTEGER

Thanks a lot in advance! 🙂

Answer

Check if the field is empty and bind to null instead.

        foreach($vals as $val){
            $query->bindValue($x, $val == '' ? null : $val);
            $x++;
        }

Advertisement

Answer