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