There was a question about updating data in two tables, here is the function

public function save($task_id) { if ($this->id) { $sql = 'update phones set phone=\''.$this->phone.'\', data=\'\', '. 'reply=\''.$this->reply.'\', duration=\''.$this->duration.'\', status=\''.$this->status.'\' '. 'where id=\''.$this->id.'\''; $result = DB::query(Database::UPDATE, $sql)->execute(); if ($result) { $result = $this->id; } else { $result = false; } } else { $sql = 'insert into phones(task_id, date, phone, data, reply, duration, status) values('. '\''.$task_id.'\', '. '\''.$this->date.'\', '. '\''.$this->phone.'\', '. '\'\', '. '\''.$this->reply.'\', '. '\''.$this->duration.'\', '. '\''.$this->status.'\' '. ')'; $result = DB::query(Database::INSERT, $sql)->execute(); if ($result) { list($id, $rows) = $result; $this->id = (int)$id; $result = $this->id; } else { $result = false; } } return ($result); }

It is necessary that the task_id, data, reply, status fields are also updated in the billing table. Please help me fix the function

  • task_id, data, reply, status fields, sorry) - Andrey Balashov
  • MySQL has an INSERT ... ON DUPLICATE KEY UPDATE . And PostgreSQL has a similar construction. So do not make this prygotnu with pulling unnecessary data from the server and back. And for insertion into the second table of the link to the first one based on known data, use INSERT ... SELECT . - Akina

1 answer 1

Wrote function

public function save ($ task_id) {

  if ($this->id) { $sql = 'update phones set phone=\''.$this->phone.'\', data=\'\', '. 'reply=\''.$this->reply.'\', duration=\''.$this->duration.'\', status=\''.$this->status.'\' '. 'where id=\''.$this->id.'\''; $sqlbill = 'update billing set data=\'\', '. 'reply=\''.$this->reply.'\', status=\''.$this->status.'\' '.'where dst=\''.$phone'\''; $result = DB::query(Database::UPDATE, $sql, $sqlbill)->execute(); if ($result) { $result = $this->id; } else { $result = false; } } else { $sql = 'insert into phones(task_id, date, phone, data, reply, duration, status) values('. '\''.$task_id.'\', '. '\''.$this->date.'\', '. '\''.$this->phone.'\', '. '\'\', '. '\''.$this->reply.'\', '. '\''.$this->duration.'\', '. '\''.$this->status.'\' '. ')'; $sqlbill = 'insert into billing(task_id, data, reply, status) values('. '\''.$task_id.'\', '. '\'\', '. '\''.$this->reply.'\', '. '\''.$this->status.'\' '. ')'; $result = DB::query(Database::INSERT, $sql, $sqlbill)->execute(); if ($result) { list($id, $rows) = $result; $this->id = (int)$id; $result = $this->id; } else { $result = false; } } return ($result); }
  • Read about START TRANSACTION, COMMIT, ROLLBACK and about the construction of TRY CATCH - Ordman