longtext select

[KryptoS]

Hey,

I upload automatic some pictures from a directory to my database. But after uploading them all I want to check if I don't have double images in my database. (you never know, some images can have different filename but are the same)

Now, the data of my images are a longtext in my database. If they are the same, the longtext will be the same.

so I did:
$sql = ("SELECT sixfourdata, Count(imgid) FROM tbl_images GROUP BY sixfourdata");
$result = mysql_query($sql, $link);

If there are 2 images the same, count(imgid) will give 2 and then I know I have to delete one of them.
Now I need the imgid from those 2. So I thought of doing:

for($i=1;$i < mysql_num_rows($result);$i++) {
if (mysql_result($result,$i,1) > 1) {
$sql1 = ("SELECT imgid FROM tbl_images WHERE sixfourdata='" . $mysql_result($result,$i,'sixfourdata') . "'");
$result1 = mysql_query($sql, $link);
}}

but it give me the following error:
Notice: Undefined variable: mysql_result in ... on line 42
Fatal error: Call to undefined function: () in ... on line 42
I think you can't do a select where with a longtext? Or is this not the problem? does somebody has a solution?

The One And Only KryptoS

 

[Bastien]

why not just

$$sql = ("SELECT a.imgid from 
tbl_images as a, 
tbl_images as b 
where a.sixfourdata = b.sixfourdata
order by imgid");

$result = mysql_query($sql, $link);

while ($rows=mysql_fetch_array($result)){
   echo "duplicate image id: ".$rows[0];
}

Bastien

Cat, the other other white meat

 

[DRJ478]

Here's an alternate solution:

1. Store a MD5 hash for the image data when the image is entered into the table
2. Compare for identical MD5 hashes.

This way you don't have to deal with the overhead of digesting the entire image data. The accuracy stays the same - neither way will find images to be identical if even one bit is off.