删除数据库中表的记录后删除物理文件

what i want is to delete the image file after deleting the record of a table in database

我想要的是删除数据库中表的记录后删除图像文件

my database consisting 4 tables, linking with primary keys and foreign keys.

我的数据库包含4个表，连接主键和外键。

the 4 tables are : Brand, Product, Content, Promotion. All consisting column "brandID" and having a column of "file path"

这四个表是:品牌，产品，内容，促销。所有组成的列“brandID”和“文件路径”列

tables are altered to "ON DELETE CASCADE" so the records of others table will be deleted when a "brandID" is deleted.

表被更改为“ON DELETE CASCADE”，因此当“brandID”被删除时，其他表的记录将被删除。

** i save image file to server, file path to database. Again, what i want is to delete the physical file in server when i delete the file path(entry) of a table..

将图像文件保存到服务器，文件路径保存到数据库。同样，当我删除表的文件路径(条目)时，我想要删除服务器中的物理文件。

any advise is highly appreciated.

非常感谢您的建议。

im new to DB, below is the links i red.. i need more information..

我刚到数据库，下面是我的链接。我需要更多的信息。

thanks again

再次感谢

Can we delete the physical file from server when I delete the corresponding entry from database?

当我从数据库中删除相应的条目时，是否可以从服务器删除物理文件?

How to delete files when a record is deleted?

如何在删除记录时删除文件?

delete file using MySQL procedure

使用MySQL程序删除文件

2 个解决方案

#1

MySQL can't really perform such filesystem actions for you automatically*. Instead, your application code that issues the DELETE statement to the server should invoke a filesystem operation to remove the file.

MySQL不能自动为您执行这样的文件系统操作*。相反，向服务器发出DELETE语句的应用程序代码应该调用文件系统操作来删除文件。

Therefore you will need to:

因此，你需要:

Select the filepath from the database;

从数据库中选择文件路径;
Delete the records from the database (cascading foreign key constraints can ensure that all associated records are deleted as appropriate); and

从数据库中删除记录(级联外键约束可以确保根据需要删除所有相关记录);和
Delete the file from the filesystem. One presumes that your application is in PHP (since your question is so tagged), in which case you can call unlink() to delete the file.

从文件系统中删除文件。一个假设您的应用程序是在PHP中(因为您的问题被标记了)，在这种情况下，您可以调用unlink()来删除文件。

^{* This is not entirely correct, as one could use a UDF to delete the file from a trigger, but this will generally open a number of security vulnerabilities which are rarely worth the tradeoff.}

*这并不完全正确，因为可以使用UDF从触发器中删除文件，但这通常会打开一些安全漏洞，这些漏洞很少值得进行权衡。

#2

this is what i've done. If you notice, the deleting file and deleting database records are separated because the database tables are connecting by reference key, thus the deleting records part is quite easy by using "ON DELETE CASCADE" .

这就是我所做的。如果您注意到，删除文件和删除数据库记录是分开的，因为数据库表是通过引用键连接的，因此通过使用“ON DELETE CASCADE”，删除记录部分非常容易。

**the database was designed to only store the path of the uploaded file

**本数据库设计为只存储上传文件的路径

To delete the file, firstly, i create a query to obtain all the file location with a title "pictureLocation" , then unlink them 1 by 1 in the loop as shown as below

要删除文件，首先我创建一个查询，以获取标题为“pictureLocation”的所有文件位置，然后在循环中取消1×1的链接，如下所示

<?php

.....

.....

    //query and make file path  AS 'pictureLocation'


    if($table == "something_user"){
    $query= sprintf ("SELECT userID,userLogo AS 'pictureLocation' 
        FROM something_user
        WHERE $idTitle=$id
        UNION ALL
        SELECT userID,contentImage 
        FROM something_content
        WHERE $idTitle=$id  
        UNION ALL
        SELECT userID,promoImg 
        FROM something_promo
        WHERE $idTitle=$id 
        ORDER BY userID"  );

        }
        else if($table == "something_product"){
                $query= sprintf ("SELECT productID, contentImage AS 'pictureLocation'
                FROM something_content
                WHERE $idTitle=$id
                UNION ALL
                SELECT productID, promoImg
                FROM something_promo
                WHERE $idTitle=$id
                ORDER BY productID" );
                }
                else if ...


        else{
            //do nothing
        }


$result = mysql_query($query);      //execute the query , put into $result

$numrow = mysql_num_rows($result);  //for error checking
$removeCount = 0;

while ($row = mysql_fetch_array($result)) {    //fetch array to $row

$deletepath = "../".$row['pictureLocation'];        //adding  ../  to the path 
                                            //*pictureLocation storing the file path retrieved from query

    if(file_exists($deletepath)){
        echo "exist".$deletepath;

        if(unlink($deletepath)){
        $deletecount++;
        }
        else{ 
            die("unlink error");
            }

    }
    else{       //if file not exist
        echo $deletepath."not here";
        $deletecount++;
    }


}
//end of file deletion


    //if delete count equal to number of query, delete database entry
    if($removeCount == $numrow){

    $updateSQL = sprintf("DELETE FROM ".$_GET["table_name"]." WHERE $idTitle = %s",
                GetSQLValueString($id, "text"));        //defend against injection
    }

    mysql_query($updateSQL, $DBConnect) or die(mysql_error());
} 
else{
    $error = 1;
}
mysql_close();

any advises or improvements for the code is very appreciated. Thanks!

非常感谢您对代码的任何建议或改进。谢谢!

#1