Delete comma separated records

Question

I am uploading several images and storing them in the bank, so far it is perfect. The records are recorded, separated by comma in the field materialDocumento on the table tb_material. The problem is that I am not able to delete a specific image that is selected when necessary. Could you help me?

The files are recorded in the database as follows:

498818_03c4414ce0324292a51e63009f513a82_mv2.jpg, 64223640_2226772990741441_1192728863528976384_o.jpg, 64254162_1912126265586260_4480819099357675520_o.jpg, 25044125149117.jpg

php.

<a href="material-edita.php?edita_material_ID=<?php echo $_GET['edita_material_ID']; ?>&imagem_material=<?php echo $img; ?>"><button type="submit" class="btn btn-large btn-danger" name="btn-deletar-imagem"><i class="fa fa-trash"></i> &nbsp; Excluir Imagem Material</button></a>

<?php
  if (isset($_GET['edita_material_ID'])) {
      $materialID = $_GET['edita_material_ID'];
      extract($classeMaterial->getID($materialID));

      $mDocumento = $_GET['imagem_material'];

  if ($classeMaterial->deletarArqImg($materialID, $mDocumento)) {
    var_dump($mDocumento);
  }
}
?>

class.classe_material.php

<?php
    public function deletarArqImg($materialID, $mDocumento) 
    {
        try {
            $stmt = $this->db->prepare("DELETE FROM tbl_material WHERE materialID = :materialID AND materialDocumento = :mDocumento");
            $stmt->bindparam(":mDocumento", $mDocumento);
            $stmt->bindparam(":materialID", $materialID);
            $stmt->execute();
            return true;

            } catch (PDOException $e) {
            echo $e->getMessage();
            return false;
        }
    }
?>

Answer 1

Since you were storing a "collection" of string values you need to understand what you are doing.

All your documents related to a particular material are saved as a single value (string). So your database does not understand that there are several related documents because this structure was not carried out. But as you know that there are several relationships the ideal is to follow the suggestion of @Andersoncarloswoss and carry out a update in string.

---

First, you need to get the record to be updated (If you don’t have a method for this):

public function find($id)
{
    $stmt = $this->db->prepare("SELECT * FROM tbl_materia WHERE materialID = ?");
    $stmt->execute(array($id));
    $material = $stmt->fetchAll();
    return $material[0];
}

---

After obtaining the registration by id, it is necessary to check whether the document to be deleted exists to then carry out the update in place of delete.

public function deletarArqImg($materialID, $mDocumento) 
{
    $material = $this->find($materialID);

    //Utilizar o array_map para aplicar a função trim (remover os espaços vazios do lado direito e esquerdo) em cada valor do array
    $documentos = array_map('trim', explode(',', $material['materialDocumento']));

    $key = array_search($mDocumento, $documentos); //Verificar se existe o documento a ser "excluído"
    if ($key !== false) {
        unset($documentos[$key]); //Removendo o documento do array de documentos
        $newDocumentos = implode(',', $documentos);

        try {
            $update = $this->db->prepare("UPDATE tbl_material SET materialDocumento = :newDocumentos WHERE materialID = :materialID");
            $update->bindParam(":newDocumentos", $newDocumentos);
            $update->bindParam(":materialID", $materialID);    
            return $update->execute(); //Retorna TRUE para sucesso ou FALSE para falha
        } catch (PDOException $e) {
            echo $e->getMessage();
            return false;
        }
    }
}