Problem with filename in Database

ehask

Hey All,

I have a spot on my site that allows the user to have 10 files uploaded to a directory and filename to the database.

In the chargeback table I have

userid
saleid
amount
dispdate
filenum <-- Keeps track of the amount of files assoc.
file1
file2
file3
file4
all the way to
file10

My problem is if the client uploaded 6 documents. filenum would = 6 and it would populate file1 - file6. If the client deletes say file3 my code deletes from db sets filenum -1 and unlinks the file,

No how do I look to find the first empty file (file3) location when he uploads a new file. So I can name the file accordingly and avoid??

Eric

bpat1434

Well... not sure if I can answer that question but perhaps offer a different way to achieve what you want:

Perhaps you should instead of using ids and relying on them, use the number of rows.

So say someone uploads 6/10 files, and then deletes 2. You would have two gaps. Instead of finding the lowest number to give the ID, just use the ID as a marker for download or viewing. Then when they go to upload files, just count the number of rows they have in that table. If the number of rows = 10, they can't upload any more, if it's less than 10, they can upload more.

Just another way to look at your setup, you don't have to follow it, but it's how I'd follow it.

~Brett

Sxooter

Note that if more than one person is going to use this app at the same time, reusing id numbers opens you to a race condition.

It's usually better to program around the holes in your sequences in the presentation layer than to try and get rid of them.

ehask

Well I only set up the db to allow 10 files.

The files are renamed based on a unique ordernumber and the order it was uploaded at that time. So if they delete -2 I would want to make sure I dont upload another -1

546372-1.jpg
546372-2.doc
ect.

Sxooter do you mean dont store filenames in the db? This is a chargeback interface to a Credit Card Gateway so in most cases the ordernumbers wont' reoccur as they are generally atleast 2 weeks old before the chargeback hits them.

Sxooter

No, I mean reusing is numbers is usually a bad idea. While there are times when it's the best solution, those times are rare. In your instance, this may be one of those times.

Just make sure you lock whatever rows you're updating so two clients don't try to update the same info at the same time is all.

devinemke

i suggest you store the filenames in a separate table that relates to the userid and use a simple COUNT() query to make sure thay cannot upload more than 10. use the primary key from the table as the filenames.

ehask

Ok cool!!

Yeah the database that holds this info is one row per chargeback/order & up to 10filenames. so there shouldnt be an issue of them inserting files at the sametime.

Thx for the help

Eric