How to avoid upload conflict

[phppup]

I want to allow users to upload images to a directory.

I want the images renamed sequentially and lumped together with each upload.

Eg: If Bob uploads 3 images to the BIRD directory, the files will be named BIRD1, BIRD2, and BIRD3.

When Tom then uploads 6 images to this directory, they will be sequential from 4 to 9.

And when Jane adds 5 the numbering will run from 10 to 14.

I suspect that in each instance I will need to SELECT an "image quantity" column and initiate a number sequence from the sum at that point.

But my question is more about maintaining the integrity of the sequences.

If Tom and Jane upload somewhat simultaneously, how can I control the outcome to prevent Tom from having numbers 4,5,8,11,13 and 14 with Jane's images interspersed in the sequence?

Obviously the two uploads will be competing based on image sizes, network speed, etc.

How can I keep the separate batches organized as a group?

 

[requinix]

Don't try to handle each image individually. Reserve a number range for your batch of files in a single statement, then use the numbers from that range when you move the files around.

[phppup]

I'm not entirely clear about what you mean.

If I try to determine the number of the last loaded file, won't I still be in the same predicament?

[kicken]

why do you want to try and generate a shared sequence?  Why not just use random names and/or give each user their own separate sequence?

[phppup]

@kicken How would I do that?

I ultimately want to be able to refer back to the sets of images and know that this set were from Bob and this set were from Joe (as a neat sequenced set within a sequential group).

In this way, if images 8,9, and 10 are outstanding, I can easily give credit to whomever is responsible for image 7 through 12 because there are no shuffled results

Edited November 1, 2022 by phppup

[Barand]

Use a database table to store and number your images for you.

Mostly we use InnoDB tables but MyISAM tables have a unique property - you can create a two-part primary key where the second part auto-increments.

Create table

CREATE TABLE `user_image` (
  `folder` varchar(20) NOT NULL,
  `image_no` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(45) DEFAULT NULL,
  `date_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`folder`,`image_no`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Add some image records

INSERT INTO user_image (folder, username) 
VALUES
('BIRD', 'Bob'), ('BIRD', 'Bob'), ('BIRD', 'Bob'), 
('BIRD', 'Tom'), ('BIRD', 'Tom'), ('BIRD', 'Tom'), ('BIRD', 'Tom'),
('DOG', 'Peter'),  ('DOG', 'Peter'), ('DOG', 'Peter'), ('DOG', 'Peter'),
('BIRD', 'Jane'), ('BIRD', 'Jane'),
('DOG', 'Mary'), ('DOG', 'Mary'), ('DOG', 'Mary'), ('DOG', 'Mary'),
('BIRD', 'Jane'), ('BIRD', 'Jane'), ('BIRD', 'Jane');

The data

mysql> select * from user_image;
+--------+----------+----------+---------------------+
| folder | image_no | username | date_added          |
+--------+----------+----------+---------------------+
| BIRD   |        1 | Bob      | 2022-11-01 10:30:58 |
| BIRD   |        2 | Bob      | 2022-11-01 10:30:58 |
| BIRD   |        3 | Bob      | 2022-11-01 10:30:58 |
| BIRD   |        4 | Tom      | 2022-11-01 10:30:58 |
| BIRD   |        5 | Tom      | 2022-11-01 10:30:58 |
| BIRD   |        6 | Tom      | 2022-11-01 10:30:58 |
| BIRD   |        7 | Tom      | 2022-11-01 10:30:58 |
| DOG    |        1 | Peter    | 2022-11-01 10:30:58 |
| DOG    |        2 | Peter    | 2022-11-01 10:30:58 |
| DOG    |        3 | Peter    | 2022-11-01 10:30:58 |
| DOG    |        4 | Peter    | 2022-11-01 10:30:58 |
| BIRD   |        8 | Jane     | 2022-11-01 10:30:58 |
| BIRD   |        9 | Jane     | 2022-11-01 10:30:58 |
| DOG    |        5 | Mary     | 2022-11-01 10:30:58 |
| DOG    |        6 | Mary     | 2022-11-01 10:30:58 |
| DOG    |        7 | Mary     | 2022-11-01 10:30:58 |
| DOG    |        8 | Mary     | 2022-11-01 10:30:58 |
| BIRD   |       10 | Jane     | 2022-11-01 10:30:58 |
| BIRD   |       11 | Jane     | 2022-11-01 10:30:58 |
| BIRD   |       12 | Jane     | 2022-11-01 10:30:58 |
+--------+----------+----------+---------------------+

 

[kicken]

Since you want "sets of images", I would say create a table for your sets of images.  You could even store metadata about the set itself that way if you wanted too.

create table image_set (
  id int not null primary key auto_increment,
  username varchar(100),
  createdOn datetime
);

create table image_set_image (
  id int not null primary key auto_increment,
  image_set int not null,
  sortOrder int not null,
  category varchar(100) not null,
  image_name varchar(100) not null
);

see example

Each time someone uploads a batch of images, create a new set for them.  Add each of the images to that set.  The image_set_image.sortOrder column is just a numeric value you can use to sort the images within that set, you can auto-generated it or let the user specify it somehow.  The image_set_image.category column would be your 'BIRD' / whatever value.  If you want all the images to be in one category you could move this to the image_set table instead.

When you then query the images for display you can then sort the results so each users images are grouped together an just auto-generate a sequential numbering system for the results.  With newer mysql versions you can do this right in the query itself.

 

[phppup]

Thank you @Barand @kicken @requinix

It's a lot to sift through.

i had another thought and would appreciate some feedback on it.

Previous lessons have indicated that trusting user data can be unwise. And in this instance, the possibility of duplicate file names from users submitting images of BIRDS is probably high. Therefore, renaming the files is very likely.

If a user is required to authenticate before upload, and is then entered into a separate table with an auto increment, can I use the following methods successfully:

auto increment field is 'order' 

rename image as order + TIMESTAMP (perhaps only month.day.time) [within calendar year]

In this way, having the order as the leading digit will automatically set a very large range for each user.

My thinking is that it will also create a set of numbers that will be sequential as a group.

Overkill or good idea? Pitfalls?

 

(If I use this, it is technically a hybrid of everyone's assistance. Who should get the solution mark?)

*Perhaps we can create a "community achievement trophy" for teamwork within a thread*

Edited November 1, 2022 by phppup
Forgot item