There is a module, let's say news, two related tables, news(id,text) and files(id,news_id,src) , while creating news, each user can upload photos. Photos are loaded with a multi-apload, ajax with thumbnails. pictures are loaded before the actual creation of the news, news_id unknown. How to organize all this correctly? Including, storing temporary files, cleaning, adding. Crown and triggers do not use.

3 answers 3


The idea is inspired by gmail as the pictures can be uploaded, only registered users, according to the new file upload, automatically create a draft, if the news is published automatically we clean the draft and convert it to the news, otherwise we add to drafts that the user can edit at his own discretion or delete. this way we expand the functionality and get rid of the crown, the customer jumps from happiness, we enjoy a beautiful solution.

  • Those. when creating news (typing text) from time to time save it in drafts? - Dobby007
  • @Dobby007 yes that way, or when you upload photos. - FLK
  • good idea ... - Dobby007

Alternatively, upload and after answering Ajax create in the form of an add

 <input type="hidden" name="files[]" value="<?=$new_file_rel_path?>" /> 

And actually everything) The only thing is, it is necessary to periodically clean unused files (or not clean, if "fragmentation").

  • But in cleaning it is just the same problem, how to organize it in a cunning way, but I did not understand what about the "fragmentation". - FLK
  • Yes, the story was, VKontakte refused to delete deleted pictures physically, because it "increases fragmentation." Well, in general, this is exactly what is done. You can: - make the "delete file" button even when creating a news item so that it is immediately deleted - write downloaded pictures to the user (in the database), delete what is added to the news when creating the news. And then at login / absence more than N hours to delete everything in this list (article is not created). - CZK (daily pass through all files for the last 24 hours with checking them in the list of files in the database) - Sh4dow
  • Another such perverted perversion is to make a knocker every 30 seconds in the form of a creation, and if, say, you don’t hear it for 2 minutes, delete the files. But this option is fraught with the fact that an Internet cliff turns the article into a mess. From this, too, you can escape if you close the add-on form on the client after the same 2 minutes after the last response of the knocker. But, honestly, it looks like the case when solving a problem breeds 2 new ones. - Sh4dow
  • @ Sh4dow is another cunning perversion, but has not yet learned how real it is, if it were possible to send files twice, first with an upload for the thumbnail, and a second time with the body of the news to be added. - FLK
  • @FLK, and if files of pieces 5 and all on 15 megabytes? Two times to send them to the user is not in a rush somehow will be. Yes, and you have a server disk space is not infinite. Plus @ Sh4dow wrote about fragmentation ... - Dobby007

It can create a table temporary_files (even if MEMORY), and write to it files with a temporary news id, which is generated by pressing the "Create News" button on the principle:

 SELECT MAX(news_id) FROM temporary_files 

Then, when you fill in all the fields and click Submit, do:

 INSERT INTO files SELECT id, $last_insert_news_id, src FROM temporary_files WHERE news_id = $id DELETE FROM temporary_files WHERE news_id = $id 

If you need to clean, then from time to time you can go through the table and delete these temporary files by loading time (if more than two hours, then they agree there’s nothing to do), for example. Those. if there is a date field in the table, then:

 SELECT id, src FROM temp_files WHERE date > NOW() + INTERVAL 2 HOUR; - чтобы удалить файлы и DELETE FROM temp_files WHERE id IN(implode($array)) 

It can of course be crooked, but this is the first thing that came to mind.


I apologize, but the SELECT MAX(news_id) FROM temporary_files request SELECT MAX(news_id) FROM temporary_files does not work, because there may be a situation when one user creating the news has not uploaded any files yet, and the other who pressed the button a little later has already loaded, then the first one will see list of other files. So, to avoid this, you will need to take the latest generated news_id from some other source. Generally speaking, this news_id best stored in memcache, for example, or in a file, as a last resort. Yeah ... Perhaps you can somehow do something different, something strongly somehow "tricked out" happened))


But when you upload a file (after adding a file to temp_files ) you will know its id. Anyway, the "Delete file" button will need to be done. Then you can simply after sending the files, insert these id files into the permanent table files. Then the news_id column in temp_files is not needed (we will manage id, src and date columns). Those. there must be something like:

 INSERT INTO files (SELECT id, $last_insert_news_id, src FROM temporary_files WHERE id IN (1,2,3,4)) 
  • frankly, I do not see much point in the additional table, you can simply specify user_id for example 0 and then periodically delete the unused files in the same way. - FLK