Wednesday, December 10, 2014

MYSQL: merge update and insert using replace




MySQL REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

So REPLACE know when to insert a row or update a row
EXAMPLE: id is primary key. semester. stakeholder_id and courses_id are composite key.
         $c = new Connection();
  $sql = "REPLACE  contract_si_ta_percourse
                  SET semester = {$this->sanitizeInput($arr['term'])},
                               ContactHour = {$this->sanitizeInput($arr['contacthour'])},
                                       courses_id = {$this->sanitizeInput($arr['course_id'])},
                       stakeholder_id  = {$this->sanitizeInput($arr['stakeholder_id'])}
                           ";  
       $result = $c->query($sql);


If we do not use replace, and use insert and update, the code will become very complicate.

          $c = new Connection();
  $sql = "SELECT id FROM  contract_si_ta_percourse
                  WHERE semester = {$this->sanitizeInput($arr['term'])} AND
                                       courses_id = {$this->sanitizeInput($arr['course_id'])} AND
                       stakeholder_id  = {$this->sanitizeInput($arr['stakeholder_id'])}
                           ";  
       $result = $c->query($sql);

if($c->getNumRows($result) <= 0){
         $sql = "INSERT  contract_si_ta_percourse
                  SET semester = {$this->sanitizeInput($arr['term'])},
                               ContactHour = {$this->sanitizeInput($arr['contacthour'])},
                                       courses_id = {$this->sanitizeInput($arr['course_id'])},
                       stakeholder_id  = {$this->sanitizeInput($arr['stakeholder_id'])}
                   ";  

}else {
               $sql = "UPDATE  contract_si_ta_percourse
                              SET ContactHour = {$this->sanitizeInput($arr['contacthour'])}

                           WHERE
                          semester = {$this->sanitizeInput($arr['term'])} AND
                               courses_id = {$this->sanitizeInput($arr['course_id'])} AND
                                       stakeholder_id  = {$this->sanitizeInput($arr['stakeholder_id'])};

}
    $result = $c->query($sql);

No comments:

Post a Comment