我试图以更好的方式解决的问题是删除带有标签的图像文件夹。所以对于每张图片我都需要删除
-图像本身
-来自三个数据库(img_offer, img_member, img_horses)的图像标签
目前,我得到要删除的文件夹的所有图像id,然后用四种不同的查询遍历这四次,这似乎相当低效。
主要问题是,据我所知,你不能同时打开多个prepare语句,并且在每次迭代中创建新的语句似乎也违反直觉。
我认为最好的方法是像多个查询准备语句这样的东西,但我找不到类似的东西,所以也许这里有人有一个想法,如何以更干净的方式解决这个问题
我的想法是这样的
$multiplePreparedStatement= "DELETE this FROM that WHERE id=?;
DELETE this2 FROM that2 WHERE id2=?;
DELETE this3 FROM that3 WHERE id3=?;";
$preparedStmt = $conn->prepare($multiplePreparedStatement);
foreach($imgArray as $imgId){
$preparedStmt->bind_param("iii", $imgId, $imgId, $imgId);
$preparedStmt->execute();
}
$preparedStmt->close();
但我不认为这将工作作为多个SQL查询不支持在准备好的语句或他们?
下面是我当前的代码:
$id=$_GET['deleteAlbum'];
$getImages = "SELECT image_id AS id
FROM Images
WHERE folder_id = ?";
$deleteImage="DELETE FROM Images
WHERE image_id=?";
$deleteOffer = "DELETE FROM Images_Offers
WHERE image_id=?";
$deleteHorse = "DELETE FROM Images_Horses
WHERE image_id=?";
$deleteTeam = "DELETE FROM Images_Team
WHERE image_id=?";
//get all image ids
$ImgStmt=$conn->prepare($getImages);
$ImgStmt->bind_param("i", $id);
$ImgStmt->execute();
$ImgStmt->bind_result($id);
$imgToDelete = array();
while($ImgStmt->fetch()){
array_push($imgToDelete, $id);
}
$ImgStmt->close();
$stmt=$conn->prepare($deleteOffer);
foreach ($imgToDelete as $imgId){
$stmt->bind_param("i",$imgId);
$stmt->execute();
}
$stmt->close();
$stmt=$conn->prepare($deleteHorse);
foreach ($imgToDelete as $imgId){
$stmt->bind_param("i",$imgId);
$stmt->execute();
}
$stmt->close();
$stmt=$conn->prepare($deleteTeam);
foreach ($imgToDelete as $imgId){
$stmt->bind_param("i",$imgId);
$stmt->execute();
}
$stmt->close();
$stmt=$conn->prepare($deleteImage);
foreach($imgToDelete as $imgId){
unlink("../assets/img/images/img".$imgId.".jpg");
$stmt->bind_param("i",$imgId);
$stmt->execute();
}
$stmt->close();
我也有创建多个连接的想法,但我认为这可能会出现问题,如果例如删除图像,而我仍然有一个查询迭代图像
根本不需要遍历image_id
(至少不需要遍历SQL数据)。您可以一次从数据库中删除与特定folder_id
相关的所有内容:
DELETE Images, Images_Offers, Images_Horses, Images_Team
FROM Images
LEFT JOIN Images_Offers ON Images_Offers.image_id = Images.image_id
LEFT JOIN Images_Horses ON Images_Horses.image_id = Images.image_id
LEFT JOIN Images_Team ON Images_Team.image_id = Images.image_id
WHERE folder_id = ?;
当然,在此之前你应该unlink
实际的文件