mysql / delete on server question

razzera · 06-19-2011, 06:28 AM

Hello

I have a question that i hope someone can help me with. Im running a debian server with Apache2 and Mysql.

I wonder if it is possible to delete stuff of a folder in my webserver if it is not in a certain collumn in a table in my mysql database?

for example, let's say that in /home/user/public_html/images/ i have an image called 4520As.jpg, is it possible to check this against a collumn called "name" in the table "images" in my mysql database and if the name of the image is not in that collumn of the database it will get deleted ?

Snark1994 · 06-19-2011, 06:47 AM

Code:

$database = "db";
$user = "usr";
$host = "host";
$password="passwd";
$query=" SELECT name FROM images";

$keep_images = array();
$image_list = array();

$link = @mysql_connect($host, $user, $password, true);
$result=mysql_query($query);
$number=mysql_numrows($result);
$i=0;
echo "<pre>";
while ($i < $number) {
    $keep_images[] = mysql_result($result,$i,"name");
    $i++;
}
mysql_close();

$myDirectory = opendir("/home/user/public_html/images/");
while($entryName = readdir($myDirectory)) {
    $image_list[] = $entryName;
}
closedir($myDirectory);

foreach ($image_list as $image){
    if(!in_array($image,$keep_images)){
        echo "Would delete /home/user/public_html/images/" . $image . "<br/>";
        //unlink("/home/user/public_html/images/" . $image);
    }
}
echo "</pre>";

Something similar to this should work. It's completely untested, hacked together from a couple of PHP scripts I had written for my work's website. I've set it up so it doesn't delete anything, only tells you which ones it would try to delete (for obvious reasons); if you want it to do actual deleting then uncomment the 'unlink()' command

Hope this helps,

razzera · 06-19-2011, 07:28 AM

Quote:

Originally Posted by Snark1994

Code:

$database = "db";
$user = "usr";
$host = "host";
$password="passwd";
$query=" SELECT name FROM images";

$keep_images = array();
$image_list = array();

$link = @mysql_connect($host, $user, $password, true);
$result=mysql_query($query);
$number=mysql_numrows($result);
$i=0;
echo "<pre>";
while ($i < $number) {
    $keep_images[] = mysql_result($result,$i,"name");
    $i++;
}
mysql_close();

$myDirectory = opendir("/home/user/public_html/images/");
while($entryName = readdir($myDirectory)) {
    $image_list[] = $entryName;
}
closedir($myDirectory);

foreach ($image_list as $image){
    if(!in_array($image,$keep_images)){
        echo "Would delete /home/user/public_html/images/" . $image . "<br/>";
        //unlink("/home/user/public_html/images/" . $image);
    }
}
echo "</pre>";

Something similar to this should work. It's completely untested, hacked together from a couple of PHP scripts I had written for my work's website. I've set it up so it doesn't delete anything, only tells you which ones it would try to delete (for obvious reasons); if you want it to do actual deleting then uncomment the 'unlink()' command

Hope this helps,

Worked perfectly, thanks you very very much!

is it possible to reverse his or add a line so that if there is a database entry for an image that do not exist the entry is removed from the database ?

Snark1994 · 06-20-2011, 09:50 AM

Code:

$database = "db";
$user = "usr";
$host = "host";
$password="passwd";
$query=" SELECT name FROM images";

$keep_images = array();
$image_list = array();

$link = @mysql_connect($host, $user, $password, true);
$result=mysql_query($query);
$number=mysql_numrows($result);
$i=0;
echo "<pre>";
while ($i < $number) {
    $image_list[] = mysql_result($result,$i,"name");
    $i++;
}

$myDirectory = opendir("/home/user/public_html/images/");
while($entryName = readdir($myDirectory)) {
    $keep_images[] = $entryName;
}
closedir($myDirectory);

foreach ($image_list as $image){
    if(!in_array($image,$keep_images)){
        $query="DELETE FROM images WHERE name='" . $image . "15'";
        echo "Would run:    " . $query . "<br/>";
        //mysql_query($query);
    }
}
mysql_close();
echo "</pre>";

It's pretty much the same as before - I've just swapped around the two arrays for keeping and listing images, and the code to delete items (so it deletes the database entry, not the file)