4

I'm trying to create a MySQL event so that I can have files automatically added to my MySQL database. Basically, I'm running an XAMPP server right now so all my website/database associated image files are inside the 'C:\Users\Mus\server\htdocs\images' directory. Whenever I put an image file into this directory; I need this file to be uploaded to the database automatically via this MySQL event which I'm trying to create. This means that the database needs to keep checking for new files at time intervals of say 5 seconds and if a new file has been added to said directory, a new entry will be automatically created for the image (within a specified table) and at the same time, the image will then be uploaded to that very same table within the database.

The SQL code I've used so far is:

CREATE EVENT 'eventnov' ON SCHEDULE EVERY 5 SECOND STARTS '2015-10-27 00:00:00' ENDS '2015-10-28 00:00:00' DO INSERT INTO 'images_11_nov' SELECT NOW();

I realize lines 5 and 6 are probably completely wrong but I've tried researching existing problems and I've found no luck. I've looked at the official MySQL documentation and unfortunately I can't find anything relevant to my specific problem. I don't know the syntax to have MySQL do all the following within a single MySQL event:

  1. Check the specified file directory (C:\Users\Mus\server\htdocs\images) at set time intervals of 5 seconds and IF there is a newly moved image into said directory....(number 2)
  2. Automatically create an entry within the 'images_11_nov' table as a place holder for a new image and....(number 3)
  3. Automatically upload these new image files (which had recently been moved to 'C:\Users\Mus\server\htdocs\images') and insert them into the 'images_11_nov' table.

Could someone help me finish off the above code to perform these necessary functions? If I need to provide more information, please do let me know and I shall oblige.

  • Edit 1: The column names for the 'images_11_nov' table are 'title' (varchar), 'imgdata' (BLOB), 'picid' (INT), 'datetime' (timestamp).
  • Edit 2: I've changed the SQL code in regard to Jayesh Dhandha's answer but I'm still getting syntax errors in MySQL. The updated code is as follows:

CREATE EVENT 'eventnov' ON SCHEDULE EVERY 5 SECOND STARTS '2015-10-27 00:00:00' ENDS '2015-10-28 00:00:00' SELECT count(*) 'images_11_nov' system(ls C:\Users\Mus\server\htdocs\images | wc -l) system(ls -t | head -1) DO INSERT INTO 'images_11_nov';

Mustafa Baha
  • 41
  • 2
  • Why not use a cron job that would run every 5 mins and check for new file? – Sougata Bose Oct 27 '15 at 06:00
  • I've been looking up tutorials on cron jobs but it seems more difficult to implement a cron job to perform these same functions. I already have some of the code required for this SQL query; it just needs an extra 2 or 3 lines for completion. – Mustafa Baha Oct 27 '15 at 12:41

1 Answers1

0

Yes it is very ease. You have to do following task.

  1. Get the count of rows from your table by using select count(*) table_name;

  2. use the system command on your mentioned directory

    system(ls C:\Users\Mus\server\htdocs\images | wc -l)

  3. This will give you the number of files available in mentioned directory.

  4. Now compare this count with the count of database and check both are equal or not.

  5. If it is increased by 1 the run simple system command to know the latest added file in directory.

  6. system(ls -t | head -1)

Here ls -t will list the files by time and pipe with head will give you the name of latest added file.

I hope this will help you.

Regards, Jayesh Dhandha

Community
  • 1
  • 1
Jayesh Dhandha
  • 1,983
  • 28
  • 50