如果图像不在服务器上，则从数据库中删除行 [英] Remove row from database if image not on server

问题描述

我有一个带有以下格式表的 mysql 数据库:

I have a mysql database with a table in the following format:

ID:1
日期:2010-12-19
图片:5d61240f-7aca-d34b-19-12-10-15-36.jpg
图片说明:圣诞快乐

ID: 1
Date: 2010-12-19
Image: 5d61240f-7aca-d34b-19-12-10-15-36.jpg
Caption: Merry Xmas

我想创建一个 php 脚本，它检查此表中的每一行，并检查图像是否存在于我服务器上的图库文件夹中.如果图像不在画廊文件夹中，那么我想从我的数据库中删除这一行.非常感谢有关如何执行此操作的一些指示.

I want to create a php script which checks through each row in this table and checks that the image is present in a gallery folder on my server. If the image is not in the gallery folder then I want to delete this row from my database. Some pointers on how to go about doing this would be very much appreciated.

谢谢！

推荐答案

try

<?php

    define ("GALLERY_ROOT", "/path/to/gallery/" );

    $mysqli = new MySQLi ($host, $username, $password, $db);

    $result = $mysqli -> query ("
        SELECT
            id,
            image
        FROM
            table
    ");

    if ( $result ){
        while ($row = $result->fetch_assoc()) {
            if ( !is_file (GALLERY_ROOT . $row['image'] ) ){
                $mysqli -> query ("
                    DELETE FROM
                        table
                    WHERE
                        id = '" . $row['id'] . "'
                    LIMIT 1
                ");

                print "Deleted " . $row['id'] . "<br />";
            }
        }

        $result -> free();
    }

    $mysqli -> close();

    print "Congratz!! All invalid rows has been deleted!";
?>

那很快，我实际上并没有尝试运行它.

That was a quick one, i didn't try running it actually.

此外，如果您有很多行，那么您可能需要重新考虑一次选择所有行.但请再次告诉我它是如何工作的

Also if you have a lot of rows, then you might want to rethink about selecting all the rows at once. But again get back to me on how it works

这篇关于如果图像不在服务器上，则从数据库中删除行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！