Exifier Sort Thumbnails based on the Exif date/time the picture was taken

[swishi]

Hi macmiller and thanksa for your answer...
What exactly do you mean by "need to be added to coppermine prior to running this script"? I uploaded my pics using the xp publisher and they are online...

The error message I am getting does not give any line number... it seams to be a coppermine errormessage:

In German:
Kritischer Fehler 
Beim Ausführen einer Datenbank-Abfrage ist ein Fehler aufgetreten

What means in english:
Critical Error
There was a error while runing a database request

thanks for your help!

[Joachim Müller]

Enable debug_mode to see what query actually fails. Post the error message here (not the debug_output). Applies to all critical error messages.

[swishi]

Hi GauGau
and thanks for your involvement in this matter!

Here you have the requested error:
--------------------
While executing query "UPDATE cpg11d_pictures SET cpg11d_pictures.title = 'Freunde für's Leben.... 8/17/2003 8:45' WHERE cpg11d_pictures.pid = 689" on 0

mySQL 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 's Leben.... 8/17/2003 8:45' WHERE cpg11d_pictures.pid = 689' at
--------------------

further more I am getting several errors like this:

Notices
/exifer.php
Warning line 123: exif_read_data(P5291697.JPG): corrupt EXIF header: maximum directory nesting level reached

[Joachim Müller]

Seems like the function is not properly escaping the string (the single quote broke the query). This may be a potential bug. Please provide a link to your gallery and let us know if you modified the corresponding code.

[macmiller]

Yes I agree with GauGau

It looks like the SQL statement isn't escaped properly to handle the interim single quote character in the title.  Right now it is:

Freunde für's Leben.... 8/17/2003 8:45

Please remove all intermediate single quotes if not too much trouble and try another run to confirm the problem.  After this if you fix the code please post it or I can fix and post later.

[swishi]

Hi Guys... Thanks for your replys... I'll try your suggestions and I'lll come back with the outcome within the next days!

Thanks so long

[macmiller]

I tried the single quote scenario and put a output display in there.  Note the UPDATE statement below.  This works fine, so I still am unsure of what the problem was.

starting coppermine Exif Sorter process .....
UPDATE cpg1410_pictures SET cpg1410_pictures.title = 'my test's 7/4/2006 19:02' WHERE cpg1410_pictures.pid = 236
process completed successfully
picture rows processed -->319
picture rows updated for sort position -->0
Title fields updated -->1

[swishi]

Here I am with the outcom...

As per your suggestion I renamed the File "Freunde für's Leben" to Freunde ein lebenlang and run exifer again... Now I am getting follwowing Errors:

While executing query "UPDATE cpg11d_pictures SET cpg11d_pictures.title = 'Biker's Lust... wenn da keine Lust aufkommt...? 8/18/2003 1:06' WHERE cpg11d_pictures.pid = 741" on 0

mySQL 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 's Lust... wenn da keine Lust aufkommt...? 8/18/2003 1:06' WHERE

Datei: /home/www/web428/html/coppermine/include/functions.inc.php - Zeile: 249

Notices
/exifer.php
Warning line 123: exif_read_data(P5291697.JPG): corrupt EXIF header: maximum directory nesting level reached
Warning line 123: exif_read_data(P5291698.JPG): corrupt EXIF header: maximum directory nesting level reached
Warning line 123: exif_read_data(P5291699.JPG): corrupt EXIF header: maximum directory nesting level reached
Warning line 123: exif_read_data(P5291700.JPG): corrupt EXIF header: maximum directory nesting level reached
Warning line 123: exif_read_data(P5291703.JPG): corrupt EXIF header: maximum directory nesting level reached

So I renamed the Album "Biker's Lust... wenn da keine Lust aufkommt...?" to "Bikers Lust" ...Now if I am running exifer I am still getting above mentioned Errors even if The Album "Biker's Lust... wenn da keine Lust aufkommt...?" does not exist anymore...?

[macmiller]

This is quite strange you are getting the error.  I named one of my pictures with the same title as yours to test it and output the SQL sytax as you see below.

starting coppermine Exif Sorter process .....
UPDATE cpg1410_pictures SET cpg1410_pictures.title = 'Biker's Lust... wenn da keine Lust aufkommt...? 7/4/2006 19:02' WHERE cpg1410_pictures.pid = 236
process completed successfully
picture rows processed -->319
picture rows updated for sort position -->0
Title fields updated -->1

It worked fine. 

I believe it may be a different php version or mysql implementation perhaps?  In other programs I was using the call mysql_query, in this one I am using cpg_db_query, which is standard for coppermine programs.  If I add a mysql_real_escape_string to escape the string before the call, then I get an error:

The syntax of the escaped SQL call looks like this:
UPDATE cpg1410_pictures SET cpg1410_pictures.title = \'Biker's Lust... wenn da keine Lust aufkommt...? 7/4/2006 19:02\' WHERE cpg1410_pictures.pid = 236

All this to say I really don't know what your problem is -- I would pursue it further if I could recreate the problem here.

As far as the other points, remember that the title Biker's Lust... wenn da keine Lust aufkommt...? is the title to a picture.  The title is found in the coppermine database.  If you are going to tweak it, you should do it within the coppermine interface.

[kryspin]

Hi,
I just installed the script and it works great, thank you. I have a small problem though - I think it is possible to to run the script automatically just after batch adding of new pictures has finished, so I don't have to run it from the browser manually, however since I'm not a programer, I have no idea how to do it. I would be gratefull for the particular piece of code. My guess is, it should go somewhere to searchnew.php file...

[Y2KFirehawk]

I just wanted to drop in and mention that this works GREAT. I am using this with 1.4.18 and it works without a hitch

An excellent solution for those of you looking to sort your photos by EXIF data.

Thank you!!

[waynepyrah]

Thanks for spending the time and effort on this...
is there anyway to make the dates UK format as in DD/MM/YY HH:MM

[kryspin]

Hi,
thank you very much for this mod, I had exactly the same troubles tweaking dates, names etc. I've been using this for a while and if works really well. However, I thought it might be usefull to run the script only for certain album, if possible. For example the album the pictures were just added into. The script as it is now runs on the the whole gallery, which is more and more time consuming. Do you think it is possible?

[nikkidev]

I've run this script as is on my gallery and it worked brilliantly, many thanks! I now have original titles followed by the date and time but really would like the date format to be the UK format ie DD/MM/YY HH:MM. Can anyone point me in the right direction to achieve this so that I won't lose the original title or end up with two date formats appended?

[macmiller]

There should be a variable at the top entitled $lititlestring. 

Change it to:

   

Code Select Expand

$lititlestring = "'d/m/y G:i'";

[nikkidev]

Thanks for your prompt reply. I did this but now I have both date formats in the title eg

Male Southern Hawker 8/14/2011 9:01 14/08/11 9:01

Is there anyway of changing this without going through and manually deleting the dates!

[macmiller]

Yes, I can see the problem you are having.  The script looks to see if the date is already a part of the title so it doesn't add it twice, but since the format has been changed it thinks the date is not there.  Unfortunately at this point the easiest was to fix the existing records would be to either manually modify the description in each one or write a simple php script to take out the extra date.  It will work on new records that are added.

[nikkidev]

Manually deleted the dates and re-ran the script which worked a treat. Just as well I didn't have too many to do! Thank you for a great script which will certainly be a time saver for future uploads.

[macmiller]

Just in case anyone else is still interested in this script I am posting the most current version that I am using now.  It works on CPG 1.5.x.  Mostly the changes involved dealing with positioning, the previous absolute positioning didn't work well.  The positioning is only used if you want some of your pics sorted by EXIF date and others you want to specify the position manually.  TO do this you indicate which ones you want to position manually using the lipos variable.  Other changes involve correctly escaping the strings prior to sql updates and directly accessing the db rather than using the CPG calls.  There are a lot of echo statements which can be ignored/removed.  To start set up the lines 130/131 for database access.

To use this script you would need to change all of the cpg1410_ prefixes to match your database.  Also this version of the script uses this table:

Code Select Expand

CREATE TABLE IF NOT EXISTS `cpg1410_mytempdata` (
  `album` int(11) NOT NULL DEFAULT '0',
  `pid` int(11) NOT NULL DEFAULT '0',
  `orig_position` int(11) NOT NULL DEFAULT '0',
  `orig_ordinal` int(11) NOT NULL DEFAULT '0',
  `rec_position` int(11) NOT NULL DEFAULT '0',
  `rec_ordinal` int(11) NOT NULL DEFAULT '0',
  `final_position` int(11) NOT NULL DEFAULT '0',
  `final_ordinal` int(11) NOT NULL DEFAULT '0',
  `maxposforalbum` int(11) NOT NULL DEFAULT '0',
  `maxordinal` int(11) NOT NULL DEFAULT '0',
  `albumandposition` varchar(16) NOT NULL DEFAULT '',
  `exifdatearrkey` varchar(35) NOT NULL DEFAULT '',
  `filepath` varchar(20) NOT NULL DEFAULT '',
  `filename` varchar(20) NOT NULL DEFAULT '',
  `goodexifdate` char(1) NOT NULL DEFAULT '',
  `cameramodel` varchar(20) NOT NULL DEFAULT '',
  UNIQUE KEY `exifdatearrkey` (`exifdatearrkey`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

[cottage]

heya,

does one need to set up the temp table manually? I get the following error:

v10011a
starting coppermine Exif Sorter process .....
this run only process album 33
Table 'a_coppermine.cpg15x_mytempdata' doesn't exist

Or is it a case of my database reference being incorrect?

Cheers!