PHP organize uploaded photos

Question

I am building a PHP application that lets users upload photos. To make it manageable, a folder shall have maximum of 5000 photos. Each uploaded photo will be assigned an ID in the database, and the photo will be renamed to the ID.

How do I check if a certain folder has 5000 photos? Should I check the ID of the last row in the photo table?

Folders shall be named incrementally. eg. folder_1, folder_2, etc.

My proposed steps:

1. Retrieve last insert ID of photo table
2. (Last_insert_ID + 1) % 5000 = Some_number (The folder number it should be saved in)
3. Save photo to folder_some_number. If folder not exist, create new one.

Or is there a better way to do this?

Answer 1

I'm guessing database reads are going to be faster than file system reads like this.

You'd be better off running an sql query and getting a count per folder, grouped.

// Example Query
SELECT COUNT(file), folderId As Count FROM photos WHERE folderId IN ('1', '2') GROUP BY folder
// It would be beneficial to have a flag on the folders that would enable or disable them
// that way you're not iterating through folders that we already know are > 5k
// You would run this and have seperate query that would pull in these folder names
// and passing them to the above query.
SELECT foldername, folderId FROM folders WHERE countFlag = 0;


//Example Conditional.
if($Count > 5000):
  // Over 5k Do Something
  //Since were over 5k, set this folders flag to 1
  // that way we arent iterating through it again
  $countFlag = 1;
else:
  // Under 5k Do Something else
endif;

Note: If you need actual code samples, i can whip something up real quick. The above examples leave out actual working code and are just for theory purposes. You will need to iterate through the returned rows as they are grouped by folder.

Answer 2

Using the insert ID might not be very accurate, as there a number of error conditions that can cause an ID to be "skipped". You could store the folder number in a separate column, called "folder_number" or similar. That way you can get the highest folder number, and then do a count on the number of records in that folder, if it's less than 5000, add it in the same folder, otherwise run your logic to increment the folder count (creating the physical folder at the same time).

That should be faster than using the file system to check, which could be quite slow for the volume of files you're talking about.

Answer 3

Let me suggest another approach: Use a hash on the picture's ID and use the first few characters as a path for example, lets assume the image ID is 1 for which the hash is c4ca4238a0b923820dcc509a6f75849b. Create the directory structure /rootpath/images/c/4/c/ and in it save the image. You can use mkdir() to create nested directories like mkdir( '/dir/c/4/c/', 0777, true );.

This way you automatically distribute the load across many folders and the ID itself is the path.

If my memory serves, WordPress uses something similar...

Answer 4

$last_id; // for example 9591
$current_folder = floor($last_id/5000);
$picture_num_in_folder = $last_id-($current_folder*5000);
if ($picture_num_in_folder == 5000)
    // new dir and such (new folderid = $current_folder+1 and $picture_num_in_folder = 1)
else
    // just place the picture with unique ID $last_id+1 called image_$picture_num_in_folder+1 in folder $current_folder

Answer 5

Don't use autoincrement ids for calculations. When you delete files, you'll get holes in your ID sequences which will throw off your math. Save the filepath to your db table. Then do a simple COUNT:

SELECT filepath, COUNT(filename) AS num FROM mytable
GROUP BY filepath;

You can save new files to filepaths where num < 5000.