This post is the first in what I hope will be many describing how I enhanced my media library and turned a WordPress install into a powerful photo organizing platform.
I love WordPress. It’s an amazingly powerful open-source CMS and it’s been my go-to for years. The one drawback I keep running into is how best to utilize its media library.
I have tons of photos and would love to organize them, but most plugins seem to have more than I need. I would rather improve my media library rather than install something that saves photos in a completely separate area.
Hi there. I absolutely adore this plugin called Media Library Assistant. It makes organizing photos so much easier than I ever thought possible. A big thank-you to David Lingren for creating such a genius piece of code!
Having said that, I wanted to assist in an issue I’ve tried to tackle quite a few times:
Convert the Upload Date to match the Photo’s Creation Date
Tonight, with the help of this plugin, I was successful! Please note, anyone who tries this, it is not for the faint of heart, as it required me to go into my database via PHPMyAdmin and make changes with a SQL statement (a harmless statement, but you have been warned). I highly recommend backing up your database just in case before trying this. 🙂
OK, here goes. I’ve wanted the upload date to match the photo date ever since I uploaded my first photo to WordPress when it was already past the date it was taken (which is forever). With the help of MLA, I was able to map the creation date, then update the upload date to match the newly saved creation date. Here’s how I did it:
Under Settings > Media Library Assistant, I went to the IPTC/EXIF tab and created a new mapping. These settings belong under Add a new Field and Mapping Rule:
- I entered a Field Title of “Date Taken”.
- The EXIF/Template Value was set to template:
- Priority was set to “EXIF”.
- Existing Text was set to “Replace” (this is a preference, not a requirement).
I then clicked “Add Rule” and then “Map All Attachments, Custom Fields Now” button, and my dates were entered! Below is what my settings look like for this item:
Next, I had the problem of getting that date into the upload date field. Here’s where the tricky stuff starts to happen.
The post information is inside the “wp_posts” table, while the data for each post is inside the “wp_postmeta” table. So, the date is saved in one and displayed from the other. Here’s the SQL statement I wrote:
UPDATE `wp_posts`, `wp_postmeta`
SET `wp_posts`.`post_date` = DATE_FORMAT(STR_TO_DATE(`wp_postmeta`.`meta_value`, "%Y:%m:%d %H:%i:%s"), "%Y:%m:%d %H:%i:%s"),
`wp_posts`.`post_date_gmt` = CONVERT_TZ(DATE_FORMAT(STR_TO_DATE(`wp_postmeta`.`meta_value`, "%Y:%m:%d %H:%i:%s"), "%Y:%m:%d %H:%i:%s"),'+00:00','+04:00')
WHERE `wp_posts`.`ID` = `wp_postmeta`.`post_id`
AND `wp_posts`.`post_type` = 'attachment'
AND `wp_postmeta`.`meta_key` = 'Date Taken';
Now, there is a lot going on here. First, we’re telling the database to find the new Field Title (that’s the “meta_key” in the wp_postmeta table), take that date, and then update “post_date” in the wp_posts table to match. We’re also telling it to convert the format from
YY:MM:DD hh:mm:ss to
YY-MM-DD hh:mm:ss so that WordPress can have the correct date format. Then, since I’m in the EST time zone, change the “post_date_gmt” field to add four hours (see the
+04:00 bit). Lastly I tell it to only do this with items that are attachments (this keeps it from accidentally updating a post). It worked wonderfully!
My goal is to try and get as much of this into MLA as possible. That way, I don’t have to keep updating the tables manually.
My only wish now is that I could do something similar to what Media Library Plus has available, which is allowing someone to upload images into any directory within wp_content/uploads. I ask for this, because I’d like my images from September 2015 to show up in the 2015/09 directory without me having to do it manually. If only there were a bit of code for that as well…. Maybe I’ll have a part 2 covering that bit 🙂