Hi All,

I have a bunch of 'missing images' those classic boxes with the red 'x' in them on my website. I am trying to figure out how to make some php code to delete those products out of my shopping cart database.

Note: I don't care if the thumbnail image, full-size image or both are missing. I want to delete those products from the database using some php code. The script should JUST DELETE THE PRODUCTS WITH THE MISSING IMAGES.

Also, I USED TO HAVE A Shopping Cart Called Sunshop 3.2, and they provided me with this code to work in there cart, only now I have a different cart called Pinnacle cart, which in this case should be different...But here's the code they gave me that worked for Sunshop 3.2.

<?

include "global.php";

$result = $DB_site->query("SELECT itemid, imagename, thumb FROM ".$dbprefix."items");

while($row = $DB_site->fetch_array($result)){
if(!file_exists("images/products/".$row[imagename]) || !file_exists("images/products/".$row[thumb])){
echo "Deleting item #".$row[itemid]."<br>";
$DB_site->query("DELETE FROM ".$dbprefix."items WHERE itemid ='".$row[itemid]."'");
}
}

?>

If someone can assist on this please do, I could really use some working code here.

Thank you.
-Brian

In order to do this you need to look at the database structure for pinnacle cart and how it stores the paths to the images. You can take a similar approach to the script above and loop through the db, checking if the image files exist.

I would recommend that you back up your database before doing this in case it doesn't work first time and deletes things it shouldn't. Or you can write the script so it prints the ones it intends to delete and then change it to actually delete them once it is right.

Hi Oberon,

Thank you for your reply. My images are stored remotely in my database. Another words, the images are not on my server. If you need more info regarding my db, here's some info that might be useful to tackle this script.

products (the table in my db which stores all the info on the products)

product_id (row in products table which is to give each product it's own id #)

image_url (row in the products table where the image urls are stored)

Thank you,
And I hope this helps.
-Brian

Maybe it can help you, but i recomend to backup your database first.

Delete the empty image_url rows :

$q =  "DELETE FROM products WHERE image_url=''";
mysql_query($q);

Or you can try this :

$q =  "DELETE FROM products WHERE image_url='images/products/'";
mysql_query($q);

Thank you laurente,

Your suggestion did work, however that works when you have missing urls in the 'image_url' row in the database. How about if there ARE image urls in the row, but they are 'web' urls, (remotely hosted urls) and those urls point to another server in which those images are broken? Would there be a way to remove those?

Thanks again laurente!
-Brian

This might help:
http://www.devscripts.net/browse/53.php

How to check if a remote file exists.

Hi Oberon,

That sounds like a good script, only one thing, (and not trying to be 'smart' here) but how could that script be useful if I have thousands of files to 'compare' meaning I need to find out if thousands of gif/jpgs exist, and if they don't (the script) should delete them from my db. I really don't have that kind of time to check one file at a time, and to do that each time I have to import new products...That would be the 'End Times' for me...

-Brian

Hmm, I looked at that script and couldn't get it to work anyway. I dug around for another one and this one seems to do he job. You don't have to do each on individually - you can wrap the script up in a loop to do all the work for you.

Here's some example code - you will need to replace the foreach loop with a database query to get a list of image urls and use a while loop to check them. If the function returns 0 or 2 you can delete that entry since the file does not exist.

PHP Code:

<?php 
function remote_file_exists ($url) 
{ 
/*
    Return error codes:
    1 = Invalid URL host
    2 = Unable to connect to remote host
*/    
    $head = ""; 
    $url_p = parse_url ($url); 
    
    if (isset ($url_p["host"])) 
    { $host = $url_p["host"]; } 
    else 
    { return 1; } 
    
    if (isset ($url_p["path"])) 
    { $path = $url_p["path"]; } 
    else 
    { $path = ""; } 
    
    $fp = fsockopen ($host, 80, $errno, $errstr, 20); 
    if (!$fp) 
    { return 2; } 
    else 
    { 
        $parse = parse_url($url); 
        $host = $parse['host']; 
        
        fputs($fp, "HEAD ".$url." HTTP/1.1\r\n"); 
        fputs($fp, "HOST: ".$host."\r\n"); 
        fputs($fp, "Connection: close\r\n\r\n"); 
        $headers = ""; 
        while (!feof ($fp)) 
        { $headers .= fgets ($fp, 128); } 
    } 
    fclose ($fp); 
    $arr_headers = explode("\n", $headers); 
    $return = false; 
    if (isset ($arr_headers[0])) 
    { $return = strpos ($arr_headers[0], "404") === false; } 
    return $return; 
} 

// sample code 
$url = "http://www.google.it/intl/it_it/images/logo.gif"; 
if (remote_file_exists ($url)) 
{ print ($url . " file exists!"); } 
else 
{ print ($url . " file doesn't exist!"); } 
?>

That gave me the result :

http://www.webmaster-talk.com/wtstyle/images/header_bottom_logo.png exists
http://www.google.co.uk/intl/en_uk/images/logo.gif exists
http://www.google.com/bananas.jpg does not exist
http://www.nowhere.com/turtle.png bad server name

as you would expect.

Hi Oberon,

That's some terrific news!

you will need to replace the foreach loop with a database query to get a list of image urls and use a while loop to check them.

Since I am not the expert in SQL yet, could I ask you to do that loop for me? I don't want to screw up your code.

Tia,
-Brian

Hi Oberon,

Also, could you make it so that if it finds an image that is 'broken' or doesn't exist, have the script delete the row row out of my db, instead of just telling me that it doesn't exist?

Tia,
-Brian

I'm busy with work and toher things today but I'll see if I can hack something up later on.

Hi Oberon,

Take your time, when you get to it, great! I appreciate your programming time. ;-)

Thanks again, bud!
-Brian

Okay here is some code - some important things:
1 - I don't know the structure of your database in the new shopping cart, so I've made up some obvious sounding names for things - you will need to change these to suit your database.
2 - I've left it in a state where it should print the list of images to delete so you can test it out. I've put the SQL statement to actually do the deletion in a comment line underneath. When ready and you are sure it is not going to delete anything it shouldn't, you can uncomment this line and do the deletions.
3 - Back up your database before you do anything!!

PHP Code:

<?php

function remote_file_exists ($url) 
{ 
/*
    Return error codes:
    1 = Invalid URL host
    2 = Unable to connect to remote host
*/    
    $head = ""; 
    $url_p = parse_url ($url); 
    
    if (isset ($url_p["host"])) 
    { $host = $url_p["host"]; } 
    else 
    { return 1; } 
    
    if (isset ($url_p["path"])) 
    { $path = $url_p["path"]; } 
    else 
    { $path = ""; } 
    
    $fp = @fsockopen ($host, 80, $errno, $errstr, 20); 
    if (!$fp) 
    { return 2; } 
    else 
    { 
        $parse = parse_url($url); 
        $host = $parse['host']; 
        
        fputs($fp, "HEAD ".$url." HTTP/1.1\r\n"); 
        fputs($fp, "HOST: ".$host."\r\n"); 
        fputs($fp, "Connection: close\r\n\r\n"); 
        $headers = ""; 
        while (!feof ($fp)) 
        { $headers .= fgets ($fp, 128); } 
    } 
    fclose ($fp); 
    $arr_headers = explode("\n", $headers); 
    $return = false; 
    if (isset ($arr_headers[0])) 
    { $return = strpos ($arr_headers[0], "404") === false; } 
    return $return; 
} 


$result = mysql_query("SELECT img_url FROM products") or die(mysql_error());;

while($row = mysql_fetch_array($result)) {

  $ex = remote_file_exists ($row['img-url']);
  switch($ex) {
  case 0:
    // Does not exist - delete it
    echo "deleting image: " . $row['img-url'] . "<br />";
    //mysql_query("DELETE FROM products WHERE id=".$row['id']);
    break;
  case 1:
    //File exists - do nothing
    break;
  case 2:
    //Bad server name -  shouldn't happen, best to do nothing.
    break;
  }

}
?>

Disclaimer: I cannot accept responsibility for any loss of data that occurs through use of this script.

Hi Oberon,

The script returns No Database Selected.

So I decided to add this basic connection string:

mysql_connect ('localhost', 'user', 'pass') ;
mysql_select_db ('db_name');

and that rendered a blank screen which nothing executed. Any ideas?

-Brian

Not really, not without knowing the structure of your database more. Try adding an echo statement to the case 1: and case 2: section, that prints the filename. It might be the case that the script thinks all the images exist!

I am sorry, but I don't follow you when you say:

try adding an echo statement to the case 1: and case 2: section, that prints the filename.

Doesn't it basically mean that there is no connection string to the program? Usually if a php script doesn't have a connection string, you get an error similar I thought.

thought that if I added the simple connection string that I showed in the last post, that would fix it, guess not...On a lighter note, I am leaving the office for awhile, will return later on this afternoon.

Thanks again,
-Brian