Problem with deleting items from pivot table

I am building project for learning purposes in oop php where I create properties/ads, and I have three tables, properties, photos and property_photo. My goal is when I click on delete button to delete properties at the same time to remove those photos that are connected to properties through pivot table, but when I try that I only delete properties from properties table and photos and ids in pivot table remain in database. I’m having difficulties writing sql query in my model. Any help is appreciated. Here is my code:

AdModel.php

public function deleteProperty($id)
{
    $this->db->query('DELETE FROM properties WHERE id=:id');
    $this->db->bind(':id', $id);
    if ($this->db->execute()) {
      return true;
    }
    else {
      return false;
    }
}

public function deletePropertyPhoto($id)
{
  $this->db->query('DELETE FROM photos WHERE id=:id;
  DELETE FROM property_photo WHERE photo_id=:photo_id AND property_id=:property_id');
  $this->db->bind(':id', $id);
  $this->db->bind(':photo_id', $id);
  $this->db->bind(':property_id', $id);
  if ($this->db->execute()) {
    return true;
  } else {
      return false;
    }
}

AdsController.php

public function addeleteAction()
 { 
    $this->Auth->isLoggedin();
    $this->Auth->isAdmin($_SESSION['user_id']);
    if ($_SERVER['REQUEST_METHOD'] == 'POST') {
        $_POST = filter_input_array(INPUT_POST, FILTER_SANITIZE_STRING);
        $_GET = filter_input_array(INPUT_GET, FILTER_SANITIZE_STRING);
        $this->Auth->isSet($_GET['id'], "ads/index");

          if ($this->AdModel->deleteProperty($_GET['id'])) {
            $photo = $this->AdModel->deletePropertyPhoto($_GET['id']);
            if ($photo != false) {
              if (file_exists('public/photos/' . $photo->photo)) {
                unlink('public/photos/' . $photo->photo);
              }
            }
            redirect('ads/index');
          }
          echo "User is not found!!!";
     } 
 }

Answer

First delete from photos:

DELETE FROM photos 
where photos.id in (select photos.id FROM photos join property_photo   
where property_photo.property_id = :property_id);

After that:

DELETE FROM property_photo WHERE property_id=:property_id

Advertisement

Answer