[SOLVED] Unique Id issue

[mcmuney]

For a file upload site, what is happening is when a file is uploaded, it's being renamed to the unique id. For example, if you upload file xyz.jpg and this is the very first upload, it will create record id 1 in the DB and will rename the file as 1.jpg.

 

Now, lets say that in my DB, I have 5 records and 5 files. Now, lets say that I delete record #5. For the next upload, you'd expect a 5th record in the DB, but the unique id will be 6 (since unique id 5 will not be used again). Here's the problem, it creates the 5th record with unique id 6, but the filename gets names 5.jpg. This creates a problem. Below is the code that's doing this step. Please help.

 

$pt=0;
$sqlPtFila = mysql_query("SELECT * FROM images ORDER BY ID DESC LIMIT 1");
$rowPtFila = mysql_fetch_array($sqlPtFila);
$pt = $rowPtFila[id];
$pt = $pt + 1;
$pt .= ".jpg"; 

if (isset($_POST['tags'])) {

$allowed_types = array( 

     'image/pjpeg', 

     'image/gif', 

     'image/png', 

     'image/jpeg'); 

     if(in_array($_FILES['filename']['type'], $allowed_types)) 

     {

$date = date("m-d-y");
$sql = mysql_query("SELECT * FROM images WHERE date = '$date'");
$row = mysql_fetch_row($sql);
if ($row == NULL) {
mkdir("photos/$date", 0777);
}
$target = "photos/$date/";
$target = $target . basename( $_FILES['filename']['name']) ;
$ok=1;
if(move_uploaded_file($_FILES['filename']['tmp_name'], $target))
{
echo "<div style='padding-left: 30px; padding-top: 15px; font-family:Tahoma; font-size:11px; color:#575757; font-weight: bold;'>URL: <a href='http://www.pickaloo.com/photos/".$date ."/" . $pt."' style=\"font-family:Tahoma; font-size:11px; color:#FF0000; font-weight: bold;\" target='_blank'>http://www.pickaloo.com/photos/".$date ."/" .$pt. "</a></div>";
}
else {

}
$filename = basename( $_FILES['filename']['name']);

$ip = $_SERVER['REMOTE_ADDR'];
if ($_POST['private']) {
$private = "yes";
}
else {
$private = "no";
}
rename("photos/$date/$filename", "photos/$date/$pt");
$tags=$_POST['tags'];
$sql = mysql_query("INSERT INTO images values('','$filename','$date','$ip','$private','$tags')");

}

[interpim]

mysql always increments rows in your database even if you delete that row.  Only thing I can recommend is build your script to check the database and assign the filename value based on your greatest unique ID and add 1 to it.

 

[mcmuney]

Thanks for the suggestion, but the script is already doing that. If row 5 is removed, the DB knows that the next row inserted will be 6, even though only 4 records exist. But when the file is renamed, it looks for the greatest unique ID and adds 1. In this case, the greatest existing id will be 4, then +1 = 5. So the file will be 5.jpg and the DB record 6. Somehow it needs to know what the next unique id the DB will assign.

[interpim]

write everything but the file name to the database, requery the database and update the largest unique ID with the filename. 

[mcmuney]

Good call. Actually writing everything to the DB first, then renaming the filename should fix the problem. But I just tried many variations of the code with no success. Help.

[nikefido]

what's your reason for changing the file name of the picture being uploaded? Would be just as easy to handle them separately IMO.

[mcmuney]

That's how I had it at first, but I thought duplicate filenames would cause a problem. I'm open to other solutions?

[mcmuney]

If I don't rename the original filename, can I simply add a time or random number at the beginning or end of the file? Would that make it easier?

[interpim]

I've built upload scripts that append a random 5 digit number onto the end of the original filename when you are saving it to the server.  I'm sure you could do the same, and save the filename / path in the database record.

[mcmuney]

Do you have the script available? I'd like to see how you added the random numbers to the filename. Thanks.