PDO Use value null when insert instead of empty

Question

How I can tell PDO to insert NULL instead of an empty value ?

Example :

        $SQL = 'INSERT INTO `Calendars_Events` (`CalendarID`, `AccID`, `DateFrom`, `DateTo`, `Location`, `Title`, `Description`)
                VALUES (:CalendarID, :AccID, :From, :To, :Location, :Title, :Description)';

        $Query = $this->DB->Link->prepare($SQL);
        $Query->bindParam(':CalendarID',        $CalendarID,        PDO::PARAM_INT);
        $Query->bindParam(':AccID',             $this->Account->ID, PDO::PARAM_INT);
        $Query->bindParam(':From',              $From,              PDO::PARAM_INT);
        $Query->bindParam(':To',                $To,                PDO::PARAM_INT);
        $Query->bindParam(':Location',          $Location,          PDO::PARAM_STR);
        $Query->bindParam(':Title',             $Title,             PDO::PARAM_STR);
        $Query->bindParam(':Description',       $Description,       PDO::PARAM_STR);
        $Query->execute();

If Location is empty, it will insert nothing (empty, so '') instead of NULL.

Thanks

rid · Accepted Answer · 2012-04-20 18:55:44Z

8

Pass in null instead of the empty $Location string, in case the string is empty:

empty($Location) ? null : $Location;

answered Apr 20, 2012 at 18:55

rid

64k31 gold badges159 silver badges201 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

David Bélanger Over a year ago

Ok, hmm so I have to tell php first that the value is null instead of empty ? I tought MySQL / PDO was doing that on INSERT

rid Over a year ago

@DavidBélanger, well, if you pass it an empty string, it has no way of knowing that you actually want a NULL instead of an empty string... An empty string is a perfectly reasonable value, so it doesn't replace that with NULL on its own.

David Bélanger Over a year ago

That is true... it is perfectly legit ! Thanks a lot, of corse, it is working !

Collectives™ on Stack Overflow

PDO Use value null when insert instead of empty

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related