title: passing NULL to mysql procedure
body: <p>I have the following mysql procedure:</p>
  
  <pre><code>CREATE PROCEDURE db.processEvent( IN eventId INT , IN creatorId INT , IN typeId INT , IN eventName VARCHAR( 60 ) , IN startDateTime DATETIME , IN endDateTime DATETIME , IN validStartTime TIME , IN validEndTime TIME )
  BEGIN
      INSERT INTO tbl_event
      VALUES
      (
          eventId , creatorId , typeId , eventName , startDateTime , endDateTime , validStartTime , validEndTime
      )
      ON DUPLICATE KEY UPDATE
          creator_user_id = creatorID ,
          event_type_id = typeId ,
          event_name = eventName ,
          start_date = startDateTime ,
          end_date = endDateTime ,
          valid_time_start = validStartTime ,
          valid_time_end = validEndTime
      ;
  END;
  </code></pre>
  
  <p>ON first run it does the correct thing and create a new record as I pass NULL into eventId. It also updates when I pass an id in for eventId (I am assuming it updates correctly at this stage).</p>
  
  <p>However... If I go to create a new record by pass NULL in to eventId the initial record is updated.</p>
  
  <p>I have tried adding:</p>
  
  <pre><code>    IF eventId != 0 OR eventId IS NOT NULL THEN
          set eID = eventId;
      END IF;
  </code></pre>
  
  <p>and using eID in the values list but to no avail.</p>
  
  <p>Any clues would be a great help.</p>
  
  <p>Thanks peeps.</p>
  
  <p>==========================</p>
  
  <p>Requested Info:</p>
  
  <p>table strcuture:</p>
  
  <pre><code>CREATE TABLE `tbl_event` (
  `event_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `creator_user_id` int(10) unsigned NOT NULL,
  `event_type_id` int(11) NOT NULL,
  `event_name` varchar(60) DEFAULT NULL,
  `start_date` datetime DEFAULT NULL,
  `end_date` datetime DEFAULT NULL,
  `valid_time_start` time DEFAULT NULL,
  `valid_time_end` time DEFAULT NULL,
  PRIMARY KEY (`event_id`,`creator_user_id`),
  UNIQUE KEY `event_id_UNIQUE` (`event_id`),
  UNIQUE KEY `creator_user_id_UNIQUE` (`creator_user_id`),
  KEY `fk_tbl_event_tbl_cm_profile1` (`creator_user_id`),
  KEY `fk_tbl_event_tbl_event_type1` (`event_type_id`),
  CONSTRAINT `fk_tbl_event_tbl_cm_profile1` FOREIGN KEY (`creator_user_id`) REFERENCES `tbl_cm_user_profile` (`cm_user_id`),
  CONSTRAINT `fk_tbl_event_tbl_event_type1` FOREIGN KEY (`event_type_id`) REFERENCES `tbl_event_type` (`event_type_id`)
  ) ENGINE=InnoDB
  </code></pre>
  
  <p>called via php:pdo like so:</p>
  
  <pre><code>$db     = \lib\DBObj::getInstance( DBUSE );
  $prEvnt = $db-&gt;prepare( 'CALL processEvent(:id , :creator , :type , :name , :startDate , :endDate , :startTime , :endTime );' );
  
  $prEvnt-&gt;bindValue( ':id' , $event_id , \PDO::PARAM_INT || \PDO::PARAM_NULL );
  $prEvnt-&gt;bindValue( ':creator' , $usr-&gt;getId() , \PDO::PARAM_INT );
  $prEvnt-&gt;bindValue( ':type' , $event_type , \PDO::PARAM_INT );
  $prEvnt-&gt;bindValue( ':name' , $event_name , \PDO::PARAM_INT );
  $prEvnt-&gt;bindValue( ':startDate' , $start_date , \PDO::PARAM_STR );
  $prEvnt-&gt;bindValue( ':endDate' , $end_date , \PDO::PARAM_STR );
  $prEvnt-&gt;bindValue( ':startTime' , $start_time , \PDO::PARAM_STR || \PDO::PARAM_NULL );
  $prEvnt-&gt;bindValue( ':endTime' , $end_time , \PDO::PARAM_STR || \PDO::PARAM_NULL );
  $prEvnt-&gt;execute();
  </code></pre>