Delete image id for deleted image

Question

I deleted some images from my ftp folder but in my SQL database the products are still linking to those images so in my store they turn in blank images. I would like to know it there is a way for my database to check if the files exist and if not clear the field on the image table.

Hope I could explain myself a little bit.

Edit: The questioner, who is using phpmyadmin, advises (see comments) that 'It's a database from a website. I use plesk so I think it's MySQL.'

Answer 1

You can do that in OpenCart framework, like this:

Open this file:

admin\controller\common\header.php

Find:

$data['logged'] = true;

Add after:

// Select all products with image
$query = $this->db->query("SELECT product_id, image FROM " . DB_PREFIX . "product WHERE image <> ''");
if ($query->num_rows) {
    foreach ($query->rows as $product) {
        // If product image does not exists, update product and set image to ''
        if (!is_file(DIR_IMAGE . $product['image'])) {
            $this->db->query("UPDATE " . DB_PREFIX . "product SET image = '' WHERE product_id = '" . (int)$product['product_id'] . "'" );
        }
    }
}

Now visit a page in your admin panel.

Note: you may need to clear your modifications cache. go to extensions / modifications / click on the refresh button.

After you did it, you don't need above code, remove it from your header.php