I have a new website project for my photos and I want to learn a bit about MySQL. I don't think I really need to setup a database but I want to get things ready so I can grow into it in a few years.

My ambition is to upload photos from my phone via FTP to Folder A and have a cron job process the info such as time/date taken and maybe the location. It will also rename the files in numerical order and create a thumbnail image then move the photo to Folder B. The MySQL database would hold values such as the location, date, time and anything else retrievable over EXIF. There would be no user page for logging in and uploading.

The basis for my idea stems from the new iOS 8 and how it's opening up a little bit to allow apps to work with each other. I'm hoping my FTP app updates and I can upload photos just like I upload to Facebook: select photo(s), select Facebook, select album, define a description and it uploads. Except I'd be selecting FTP, selecting Folder A and letting it upload. Where a cron job will run a script to process the photos.

It's a bit more ambitious than what I've done before. Currently, I upload a thumbnail and a full-size photo and a script searches the respective folders each time the page loads. If a photo as a thumbnail, it's displayed. If it isn't it's skipped and not accessible by viewing the photos via the thumbnail list or the "Previous" & "Next" link (though you can change the URL to see each hidden photo individually). That's the core of my current photo portfolio script. I'm hoping by creating a MySQL database for this "photo blog" of sorts I can start revamping my photo portfolio to leverage it as well: Keywords, detailed photo info, etc. I don't need users.

Are there any functions I need to look into, both PHP & MySQL, in order to achieve success for this project? I will likely need to update from CentOS 5.4, should I upgrade to v7 or is there a preferable Linux distro? I chose COS 5.4 because my Web Host Manager (WHM) worked on it. Which, isn't the end of the world, I just need to find another WHM that isn't cPanel or Kloxo.

Sometime in October, this is now officially a Photo Journal rather than a Photo Portfolio.

PHP an be extended to read EXIF data from images [link]

Also, you said using cron for processing uploaded images, which will do what you want, but is not very timely and will have to keep rerunning. I may recommend looking into inotifywait in a bash script that will wait for files to be created and fully uploaded (which a cronjob may run while an image is mid-transfer) and immediately start processing them. You can have the script ran as a service to be started on boot.

PHP has image utilities[1][2] and can certainly make thumbnails.

SQL, just ask away!

Suggestions: Have PHP generate a unique name for the files uploaded to avoid overwriting existing files (SQL can record the original filename).

I knew of the EXIF data functions and have listed several on the front of the website to remind myself to look them up, I'll add the others as well. I was thinking cronjob because it'd be less intensive on the server. I'll look into that bash script. Can I just use it to trigger a PHP script? I'm not terribly interested in learning bash on top of more PHP functions.

This is pretty basic but I have a hard time making something I can't see. My IDE supports MySQL databases and plan on using that feature extensively throughout this process to visualize the table and the names of columns and the data within. As such, I was planning on the script putting the list of images into an array and using the names of images from the array to grab specific data from the DB. Would it be better to create truly unique names rather than 000001.jpg, 000002.jpg? I'd store the original files names in the DB like you mention then and retrieve the file names from the DB and use IMG_0103.jpg, IMG_1087.jpg in the URL but display fksgsjffsk2r54873.jpg.

The only issue I find with that is that I loose any sort of chronological order if I lost the DB. I'm incredibly skeptical about using a DB, as with my current website I can just copy and paste the website files and it works. Portfolios show the same on each server and I can keep a backup on a USB drive. I don't know how I can backup the MySQL DB locally yet and just upload everything to a new server.

Store all of the photos in a NoSQL database. They're the best choice.

(Linux kernel died, lost post contents, condensed version without full SQL examples)

inotify is less resource intensive by relying on filesystem events from the kernel to sleep/wake up code. PHP can use inotify as well. Cronjob will eat up more resources by running even with nothing to process.

For SQL, the unique names prevent something like 2 phones or image libraries both having, say, IMG_0010.jpg, uploading, then having undefined behavior when trying to copy the file over from Folder A to Folder B (FTP).

SQL layout :

TABLE image_uploads:
iid (image id, integer, primary key)
filename (varchar(128), unique filename, original)
filenameThumbnail (varchar(128) also unique, thumbnail)
timestamp (upload time from file creation date/time)

TABLE image_exif:
eid (exif id, integer, primary key)
iid (image id this field belongs with)
key (text, I don't remember if exif is key => value, you can modify this and the next)
value (blob, raw data)

image_uploads has a 1 to many relationship with image_exif on the iid field.

Well, chronological order is still saved since the files when moved to Folder B are in the same order uploaded (or at least can be if a conflict happens).

For SQL, backup/restore to/from a file is not bad, mysqldump will dump one or more databases to SQL, that can be fed back to mysql and the tables and data will be restored.

Unique filenames does make it hard to copy them out without having the DB. Unique names is one way to solve conflicting filenames. (You could do something like a unique directory and the orignal name, or reject the file, or ask to rename it, or store the image data directly in a database)

With unique names, you can use a bit of apache+php magic to serve the image uniquely but have the original filename if required.

About "Would it be better to create truly unique names rather than 000001.jpg, 000002.jpg?" , if you do something like an auto-inc field in the image table, you can (iirc) drop the filename (in the FS, not original) and use the auto-inc field since it keeps using unique numbers. Other ideas are sub-second timestamps and hashes.

(Sorry for the ramble, my post was much better, in a hurry to sleep and not forget what I had previously typed)

I am able to pick out the gist of it. Since I'm not going to have a backend GUI to manage this stuff, I feel like the image names should be somewhat easy to comprehend if looking through the folder but that's an issue for later. I'll probably get the script that display the pictures working first then work on the MySQL aspect and processing scripts then switch to that system later.

I've also decided to have a small authentication system where I only allow photos from authorized devices. I'd store the device ID (ID, which *should* be in the EXIF) and compare the dID to the one on file and if it matches, continue. Else, delete.

Having some troubles with the MySQL aspect. I've got a script that gets new uploads in a directory and gets EXIF/IPTC info from it and saves it to a MySQL table. Only problem is, it completes without error and without actually writing any of the data, whether it's one picture or more. I've checked the conditions and it successfully moves the files out of the upload folder and into another folder.

So?


Code:


I successfully see "exif - done" on the page I'm executing this from but no results in the table. I ran a Query with phpMyAdmin and copied the error into Google and revised my script to match the suggestion to someones question with the exact error but no data is still being written despite that I have every column accounted for. The "id" column auto-increments so I left that empty. Still, even if I force a value (such as 1) no data is written.

I haven't ran the revised query through phpMyAdmin because it returns an error "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''INSERT INTO..."

UPDATE:I got the error to vanish, and for the data to be inserted by replacing the variables with hardcoded alternatives.


Code:


Date & Upload are in the table as TIMESTAMP and despite being 10 & 13 they appear as "0000-00-00 00:00:00." The date is being saved as 0000:00:00 00:00:00" via the camera/phone and my script.

However, if I leave "id" blank, I get an error. I removed it from the query and I can press "Run Query" indefinitely and new rows get added with incremental ID numbers.

I could not get the date & time to save using the format the camera saves as. I changed all colons to dashes and got an error at the space between the date and time, added a dash and the Date & Time saved as a 4 digit number, I suppose the backend did the math (subtracting) rather than save the value. I suppose I'll need columns for hour, minute, second, month, day & year.

Removing timestamp & upload from the PHP page doesn't help either. Nothing writes via PHP, only over a direct query with phpMyAdmin.

I haven't tested, but the 'id = ,' would seem like a problem. If your table was set up with id being an int (or other number type) with auto increment, then you would omit setting id (or if id has a default value, unlikely). Can you echo $query (or whatever query you are using) and paste the SQL code?

Now, nothing will be inserted if you have $exif evaluating to false, so an else case would help with inserting without exif data.

It was indeed a problem after the first manual query. As stated after the update, I removed the value then eventually removed "id" from the variable. If $exif is false, I wouldn't see "exif - done" on the page, but I do. Secondly, I know each picture has EXIF data because when they show up on the home page, the data is there (and read from the image).

Here is the data in the $query & $exif arrays, as printed after script execution:


Code:

So are you still having some problems with using the EXIF data, or did you get everything straightened out now?

The PHP script doesn't write anything to the DB. I get no error but when I use the same code/output as a Query with phpMyAdmin (under the "exif - done" in the code box above) the data is successfully inserted. I really have no idea why. :/

Are you saying that your query is the following? If so, you at least need single-quotes around all the values, and I seriously hope you're passing them all through mysql_real_escape_string().
Code:

That's the output of print_r($query).

I changed the code above to add single quotes and it still won't write to the DB.The code is now is as follows (followed by the print_r($query):


Code:



Code:


Update: Just to ensure the $exif data is being read, I replaced "exif - " with "$exif["FILE"]["FileName"] . ' - '; which will tell me the file names followed by done when the loop is complete. It works, file names are present.

I had some unexpected events happen during the month of October and now the website is back up and running on a new server.

Managed to fix this issue today. I don't exactly know why it wasn't working before but when I redid the server I renamed the table as lowercase text and kept the table name uppercase in the code. Adding debugging things helped me figure that out. I suspect it might have been the same story on the old server. Now that image data saves to the database I'm rewriting the display script to query the database rather than read a directory and parse photos for the data. After that, I just need to create the pages to display single photos and I'll be (mostly) done with the backend.

Not sure on how I want the design to look. I found a font I like and have been playing with it but the overall site style and theme is rather bland.

The website is now functional. I can upload photos to my portfolios or to my journal. I'm just working on small improvements listed below as I wait for the website style/theme to arrive in my inbox. After which, I'll share the URL!

On my previous photo portfolio I had to manually upload photos to the portfolio directories, so "/image/portfolio/portrait" would be the portrait directory, and so on and so on. That got cumbersome after a while and but was incredibly easy to manage. Take a photo out of the directory and it's no longer in the portfolio.

With the new portfolio, I've introduced MySQL into the backend and I am taking advantage of existing information in my photos. Before I import photos into my catalog I keyword them. First it's keywords that apply to every photo then after import I'll add keywords that are for certain batches and each individual photo. So, it felt like the ideal solution to use these keywords to sort the photos into portfolios automatically.

When I upload and process the photos to my server, the keywords are stored to the database. When a user clicks on a portfolio the script matches the portfolio name to the keywords and displays the matches. The upside of this is that I can have photos in multiple portfolios without having to worry about duplicating files and taking up space. The import script also creates two versions, a square thumbnail and an image of the original dimension but a smaller size for "full-size" viewing. The original file is moved to an inaccessible folder for archiving or recovery later on.

I still have to work on the photo navigation so visitors can easily navigate from one image to the next and back to the portfolios. I plan to use the thumbnails as a quick jump when looking at a full-size photo. I also need to check against images not in portfolios. Right now if you are looking at a full-size image and manually change the portfolio in the URL you get a blank page. I need to catch that and direct the user to either the proper portfolio or something else. Also, due to the nature of how the portfolios work, any keyword could be manually typed into the portfolio segment of the URL and photos with matching keywords would popup. I prevented this by checking the names of the portfolios listed on the page the portfolios are accessed from. Now, if someone were to visit a portfolio named "Cemetech" they'd be told that portfolio is invalid and the accessible portfolios are displayed. I'll probably use something similar for individual photos as mentioned above.

Accomplished a few things. Importantly, since the journal will largely consist of photos from my phone I am utilizing the GD library to strip EXIF data. It's crude but works. I also realized it'd be beneficial to edit photos to a degree. Tried out Snapseed from Google and the photos retain the EXIF data (whereas if they are edited by iOS that data is "lost.").

There's a few more tweaks to add as time goes on. Auto-rotate images on display, crop thumbnails to squares, and more.

No huge progress, organized more code, renaming pages so I know what they do at a glance, and adding smaller features as I wait for the theme to arrive in my inbox. However, I have questions.

I bought more than one domain for this webpage - If there's one thing I learned from my managers is that you have to own your brand - and I finally stopped redirecting this secondary URL to the main URL. Thing is, it's one of the new gTLD domains so not every browser will support them and not everyone is going to be familiar with what they are and may attempt to type in ".com" afterwards or something. So, this URL points to the same directory on the server. Dishing up the same content but under a more recognizable .com URL.

I added a small link at the bottom of the page for now that let's the visitor know that they can visit the alternate URL when they are only on the secondary URL. I used this guide to aide me in the creation. Thing is, the $_SERVER['REQUEST_URI'] doesn't recognize ?thing=destintion, it instead grabs the URI for the last request of include(); in my PHP script. Is there anything I can do to grab that stuff after the domain name?

Made some amazing progress over the last few weekends. The script that displays photos and such is more or less complete. There's still a few tweaks but I'll finish those when I get the style implemented. I started work on the managing portal for the web.

Initially, to delete a photo I had to manually delete the entry from the database and then go delete the photos out of their respective directories. It was a pain in the bum. I've taught myself some rudimentary PHP authentication techniques and have an incredibly stable login and logout system. It's not secure, yet. I lack an SSL certificate and a few other security measures but for now it's not a huge concern. When logged in, users are presented with photos they've uploaded and an option to delete said photos. At the moment the photos are only deleted out of the table. I need to restructure my directories so the managing script can procedurally access photos from other users. Before I restructure a working directory I'll hard code a copy of my website to a test subdomain and get the script to delete photo files. I'll have to add some code to the photo processing - which create the thumbnail and "full size" copies of the photos - to also create micro-thumbs so users can visually see what photos they are deleting as well as the image names. Once all that is accomplished I'll refine the code a bit so it's cleaner and get started on different user levels; such as user and admin.

Of course, I'm doing things one step at a time. When I started the login system I just focused on creating a user session which was terminated when successful. If the attempt failed, a page was shown displaying the failed attempt message. I then removed the failed attempt page and consolidated the message to the same page the user logs in at. Took a few extra variables but now the user doesn't need to press back or click a link to go back. The same principle is followed when a user logs out.

When I implemented the delete routine, the script would some delete the picture I selected then auto select another picture once the page was loaded. All I had to do was keep pressing delete to delete every photo after selecting one. I added in code to clear the post variable after deleting the required data from the table, now it's impossible to delete unselected photos. After that I'll make a level for users like myself who will use this website not only for their photo journal but for a photo portfolio as well. There will be methods to name portfolios and of course delete photos in portfolios. Clean up the code again and I should be done. During this process I'll be teaching myself and reading over a new way to connect to MySQL databases using PDO instead of mysqli. I thought about stopping and doing this first but I figured it would be easier to convert existing mysqli functions to PDO.

It's amazing to see how a small idea to make a unique website for myself has become an ambitious project for others who enjoy photography. The more I wanted to automate things, such as deleting a photo, the more I realized this could be for more than just me. Realistically, I wanted to share this domain with people but not in the way I'm envisioning now and that's what is driving me to finish this project now. It's also incredibly rewarding to look at my website and say "I've coded all of this." It may not be the best yet but over time it'll only get better.

However, how well this received remains to be seen but it looks a bit gray at the moment. The few photographer-friends I've reached out to have either declined or not responded despite myself offering everything for free. I'm still going to try and hope I can get a few people on this. From them, I'll get feedback and improve the service and hopefully get more users. I'm not expecting this to be the next thing for photographers but I'm hoping at least there are some who are enthusiastic.

ok

Can't wait to see the finished product. It looks like you've put your whole heart into this and that's what makes this so amazing! Best of luck Comic! <3