Advanced search  

News:

cpg1.5.48 Security release - upgrade mandatory!
The Coppermine development team is releasing a security update for Coppermine in order to counter a recently discovered vulnerability. It is important that all users who run version cpg1.5.46 or older update to this latest version as soon as possible.
[more]

Pages: [1] 2 3   Go Down

Author Topic: Exifier Sort Thumbnails based on the Exif date/time the picture was taken  (Read 47992 times)

0 Members and 1 Guest are viewing this topic.

macmiller

  • Contributor
  • Coppermine regular visitor
  • ***
  • Offline Offline
  • Posts: 80

Here is a script I wrote to be able to sort thumbnails based on the Exif DateTimeOriginal (ie the time the picture was really taken) and also optionally title the picture based on the same date.  Hope if you find it useful you can let me know.  I know at least it has saved me a bunch of time from renaming files, tweaking last mod dates etc.   Now the whole thing is automatic, upload pics, run script and they are sorted in the correct order.

If you read through the comments section you'll see additional comments regarding how to set up the script and exactly what it does.

cheers 

Code: [Select]
<?PHP
   ini_set('display_errors', 1);
   error_reporting(E_ALL);
/***************************************************
  PHP Script to Upload CSV Data to Coppermine SQL DB
  **************************************************
  v1.0 originally written by K Miller
  script name = exifier.php
  ********************************************
  $Source$
  $Revision: 0001 $
  $Author: k miller $
  $Date: 2006-12-30 12:10:47 +0200
**********************************************/
/**********************************************
  The script has the main purpose of accessing the Exif data related to your pictures
  and sorting the thumbnails according to the date the picture was taken.
  Before starting, make sure that you have the coppermine config Thumbnail view option
  set to 'Position ascending'.  Otherwise the thumbnails will continue to sort as before.
  Even if you have another sort option selected, you can test the script was successful by
  invoking the POSITION + - sort on the thumbnail display page. 

  One thing should probably be noted about Exif data, why it is useful, and how it is stored
  by coppermine.  Exif data is data that is stored as apart of your image files that come
  with the camera.  Exif data stores the date and time the picture was taken, camera model,
  exposure info and a bunch of other really good stuff.  The date and time the picture was
  taken turns out to be a very good sort critera, because the last mod date on the JPG file
  can get updated at several different points, making it almost useless.

  Coppermine has the ability to parse and display coppermine info and save
  it to a table, cpg1410_exif.  This information is stored only when an Info (detail) view is
  done on the detailed photo image.  You should also have the config option under
  'Files and thumbnails advanced settings' 'Read EXIF data in JPEG files' to Yes, for even
  this data to display.  If you look at the number of records you have in the cpg1410_exif
  table and pictures_exif table you will probably see the number of records is not the same.
  Why?  Because the picture has not yet been 'infoed' and the EXIF data hasn't be saved to
  the file yet.

  Sorry for the longwinded lead-up, this script looks for EXIF data in two ways.  First it looks
  to see if the data has already be saved off to the cpg1410_exif table.  If it hasn't then
  it calls the currently used coppermine Exif parse routines to parse and store the date to the
  Exif table and then continues on processing as normal.

  Another usefull thing this this script allows you to do is to create picture titles based on
  the EXIF picture taken and also optionally append the model name.  The title is created
  according to a date variable you set up.

  To get started put this script into your coppermine default directory.  So if you access
  your photo album at www.joesblowssite.com/myprettypics you would run this script out of there
  as www.joeblowssite.com/myprettypics/exifier.php.

  Other than that, read and set up the run time variables in the VARIABLE SET UP SECTION.  The
  final thing will be to set up the connection parameters in the mysql_connect line.  the xxxxx's
  need to be replaced by the username and password.  You can get these from the isp admin if
  you don't know them already.

  summary statistics are output at the end if everything goes well.

  If you use this script I would appreciate a forum note, let me know how it works for you.
  Likewise please let me know about any problems you encounter.  I've used it a lot and it
  really saves me a lot of time which before I was spending tweaking file mod dates and
  renaming JPG files with the date/time . 

  I have tested this script on three different camera models, Nokia N72, Canon SD500 and
  Sony DSC-T1 but the processing should be the same for any camera model, ie. I think it
  will work.
/**        VARIABLE SET UP SECTION                                                      **/
/** Set up how you want the script to operate here **/
// set up how you want the title generated
   $limaketitlefromdate = TRUE; //if set to TRUE creates titles based on Exif DateTimeOriginal
   $limaketitleonlyifblank = FALSE; //TRUE -> only process titles that are blank
   $limaketitleonlyifexifdate = FALSE; // Controls when titles should be generated.
//         If TRUE only valid EXIF dates will be used to generate titles.
//         If FALSE and there is not EXIF, the picture create date will be used.
   $lititlestring = "'n/j/Y G:i'"; //Make pic title look like this (PHP Date format)
   $liappendcameramodeltotitle = FALSE; //TRUE means that the title will consist of date + camera model
   $ligenerateexif = TRUE; //In the current coppermine architecture, Exif rows are only generated
//         once an info (detail list) is done on the picture.  Setting this to TRUE means that
//         this script will auto-create the Exif rows (the same as displayimage.php does).  The
//         advantage of this is you don't need to manually list each picture after uploading.
   
/***************************************************************/
   define('IN_COPPERMINE', true);
   define('DISPLAYIMAGE_PHP', true);
   define('INDEX_PHP', true);
   include("include/exif_php.inc.php");
   require('include/init.inc.php');


// various literals
   $lialbums = "albums/";
   $liexifdatestr = "DateTimeOriginal";
   $liexifmodelstr = "Model";
   $lilendatestr = 16;
   $lilenmodelstr = 5;
   $limodelpre = ":\"";
   $limodelterm = ";s";
   $lidatedataoffset = 8;
   $limodeldataoffset = 7;
   $lidatelen = 19;
   $lilastdate = "9999:99:99 23:59:59";
   $makeunique = -1;
   $exifdatear = array();
   $stattitlefieldsupdated = 0;
   $statpicrecsprocessed = 0;
   $statrecordsupdatedforsort = 0;
   $statexifrecsgenerated = 0;
   echo "starting coppermine Exif Sorter process ..... ",
        "<br>";
   
   $conn = mysql_connect("localhost","xxxxx","xxxxx") or die(mysql_error());
   $sel = mysql_select_db ("14_coppermine");

   $sql_command = "SELECT * FROM cpg1410_pictures";
   $sql_result = mysql_query($sql_command,$conn) or die(mysql_error());
   $sql_numrows = 0;
   $sql_numrows = mysql_num_rows($sql_result);
   if ($sql_numrows == 0) {
      die ("empty pictures table");
   }   
   while ($row = mysql_fetch_array($sql_result)) {
      ++$makeunique;
      ++$statpicrecsprocessed;
      $ecameramodel = "";
      $pfilepid = $row["pid"];
      $pfileaid = $row["aid"];
      $pfilepath = $row["filepath"];
      $pfilename = $row["filename"];
      $pfiletitle = $row["title"];
      $pfilectime = $row["ctime"];

// format variables to be used with this picture row
      $prettypid = makenbrpretty ($pfilepid, 8);
      $prettyaid = makenbrpretty ($pfileaid, 8);
      $prettyunique = makenbrpretty ($makeunique, 8);
      $exifdata = $prettypid . $pfilepath . $pfilename;

// make a date from the record create time
      $pcreatedate = date('Y:m:d H:i:s', $pfilectime);
      $goodexifdate = FALSE;
      $datesourcecreate = TRUE;
      $ekey = 'albums/' . $pfilepath . $pfilename;
      $sql_command = "SELECT * FROM cpg1410_exif WHERE cpg1410_exif.filename = '$ekey' LIMIT 1";
      $sql_result1 = mysql_query($sql_command,$conn) or die(mysql_error());
      $sql_numrows1 = mysql_num_rows($sql_result1);
      switch ($sql_numrows1) {
      case 0:
         echo "warning no Exif data found for -->",
               $ekey,
               "<br>";
         if ($ligenerateexif) {
            $rexif = exif_parser($ekey);
            if ($rexif === FALSE) {
               $exifkey = $prettyaid . $pcreatedate . $prettyunique;
               $exifdata = array($pfilepid, $pfilepath, $pfilename, $goodexifdate, $ecameramodel);
               $exifdatearr[$exifkey] = $exifdata;
            } else {
               $goodexifdate = TRUE;
               ++$statexifrecsgenerated;
               $exifkey = $prettyaid . $rexif[0] . $prettyunique;
               $exifdata = array($pfilepid, $pfilepath, $pfilename, $goodexifdate, $rexif[1]);
               $exifdatearr[$exifkey] = $exifdata;
            } // end if else if ($rexif === FALSE)
         } else {
            $exifkey = $prettyaid . $pcreatedate . $prettyunique;
            $exifdata = array($pfilepid, $pfilepath, $pfilename, $goodexifdate, $ecameramodel);
            $exifdatearr[$exifkey] = $exifdata;
         } // end if else if ($ligenerateexif) {
         break;
      case 1:
         $row1 = mysql_fetch_array($sql_result1);
         $exiftextdata = rtrim($row1["exifData"]);
         $exiflength = strlen($exiftextdata);
// parse out the create date
         $posdate = strpos($exiftextdata, $liexifdatestr);
         if ($posdate === false) {
            echo "warning no date string in Exif record -->",
                 $ekey,
                 "<br>";
            $exifkey = $prettyaid . $pcreatedate . $prettyunique;
            $exifdata = array($pfilepid, $pfilepath, $pfilename, $goodexifdate, $ecameramodel);
            $exifdatearr[$exifkey] = $exifdata;
            break;
         }
         $goodexifdate = TRUE;
         $posdatefc = $posdate + $lidatedataoffset + $lilendatestr;
         $posdatelc = $posdatefc + $lidatelen - 1;
         if ($posdatelc > $exiflength) {
            die ("bad exif record, date data corrupt -->" . $ekey);
         }
         $exifdate = substr($exiftextdata,$posdatefc,$lidatelen);


// parse out the model
         $posmodel = strpos($exiftextdata, $liexifmodelstr);
         if ($posmodel === false) {
            echo "warning no model string in Exif record -->",
                 $ekey,
                 "<br>";
            $exifkey = $prettyaid . $exifdate . $prettyunique;
            $exifdata = array($pfilepid, $pfilepath, $pfilename, $goodexifdate, $ecameramodel);
            $exifdatearr[$exifkey] = $exifdata;
            break;
         }
// to determine the last character of the model, start scan from the first char looking
// for the termination strings
         $lastcharofmodellit = $posmodel + $lilenmodelstr - 1;
         $startscanpre = $lastcharofmodellit + 1;
         $positionofpre = strpos($exiftextdata, $limodelpre, $startscanpre);
         if ($positionofpre === FALSE) {
            echo "warning expected prexif before model in Exif record not found -->",
                 $ekey,
                 "<br>";
            $exifkey = $prettyaid . $exifdate . $prettyunique;
            $exifdata = array($pfilepid, $pfilepath, $pfilename, $goodexifdate, $ecameramodel);
            $exifdatearr[$exifkey] = $exifdata;
            break;
         }
         $posmodelfc = $positionofpre + 2;
         $posmodellc = strpos($exiftextdata, $limodelterm, $posmodelfc) - 2;
         if ($posmodellc === FALSE) {
            echo "warning expected model terminator in Exif record not found -->",
                 $ekey,
                 "<br>";
            $exifkey = $prettyaid . $exifdate . $prettyunique;
            $exifdata = array($pfilepid, $pfilepath, $pfilename, $goodexifdate, $ecameramodel);
            $exifdatearr[$exifkey] = $exifdata;
            break;
         }
         if ($posdatelc > $exiflength) {
            die ("bad exif record, date data corrupt -->" . $ekey);
         }
         $posmodellen = $posmodellc - $posmodelfc + 1;
         if ($posmodellen <= 1) {
            die ("bad exif record, date data corrupt -->" . $ekey);
         }

         $exifmodel = substr($exiftextdata,$posmodelfc,$posmodellen - 1);

// complete parsing of model

         $exifkey = $prettyaid . $exifdate . $prettyunique;
         $exifdata = array($pfilepid, $pfilepath, $pfilename, $goodexifdate, $exifmodel);
         $exifdatearr[$exifkey] = $exifdata;
         break;
      default:
         die ("bad number of Exif matches -->" . $ekey . " matches-->" . $sql_numrows1);
         break;
      } /** end switch on matching exif data **/
   } // end while main loop on pictures file
   $sortgood = ksort($exifdatearr, SORT_STRING);
   if (!$sortgood) {
      die ("error sorting exif data array");
   }
   $lastalbum = 0;
   $lastposition = 100;
   $firstrec = TRUE;
   foreach ($exifdatearr as $key => $val) {
      $salbum = substr($key,0,8);
      $sdatetime = substr($key,8,$lidatelen);
      $spid = $val[0];
      $sfilepath = $val[1];
      $sfilename = $val[2];
      $sgoodexifdate = $val[3];
      $smodel = $val[4];
      if ($firstrec) {
         $firstrec = FALSE;
         $lastalbum = $salbum;
      } else {
         if ($lastalbum != $salbum) {
            $lastalbum = $salbum;
            $lastposition = 100;
         }
      } // end if ($firstrec)
      $sql_command = "UPDATE cpg1410_pictures SET cpg1410_pictures.position = '$lastposition' WHERE cpg1410_pictures.pid = $spid";
      $sql_result = mysql_query($sql_command,$conn) or die(mysql_error());
      ++$statrecordsupdatedforsort;

      $ruptitle = update_title($limaketitlefromdate,
                               $limaketitleonlyifblank,
                               $limaketitleonlyifexifdate,
                               $lititlestring,
                               $liappendcameramodeltotitle,
                               $spid,
                               $sdatetime,
                               $sgoodexifdate,
                               $smodel,
                               $conn);
      if ($ruptitle) {
         ++$stattitlefieldsupdated;
      }
      ++$lastposition;
   } // end foreach ($exifdatearr as $key => $val)
   echo "process completed successfully",
        "<br>",
        "picture rows processed -->",
        $statpicrecsprocessed,
        "<br>",
        "picture rows updated for sort position -->",
        $statrecordsupdatedforsort,
        "<br>",
        "Exif rows generated -->",
        $statexifrecsgenerated,
        "<br>",
        "Title fields updated -->",
        $stattitlefieldsupdated,
        "<br>";
       

function makenbrpretty ($inputstring, $outputlength) {
// Take the pass number and return it as
   $mpipstrlen = strlen($inputstring);
   $mpopstrlen = $outputlength;
   $mpstrzeros = "";
   for ($a = 0; $a <= $outputlength; $a++) {
      $mpstrzeros .= "0";
   }
   $nbrzerostoadd = $outputlength - $mpipstrlen;
   $leftzeros = substr($mpstrzeros, 1, $nbrzerostoadd);
   $mpret = $leftzeros . $inputstring;
   return ($mpret);
}

# Sanitize the data - to fix the XSS vulnerability - Aditya
function sanitize_data(&$value, $key)
{
if (is_array($value)) {
array_walk($value, 'sanitize_data');
} else {
# sanitize against sql/html injection; trim any nongraphical non-ASCII character:
$value = trim(htmlentities(strip_tags(trim($value,"\x7f..\xff\x0..\x1f")),ENT_QUOTES));
}
}

function exif_parser($filetoExif) {
   global $CONFIG, $CURRENT_PIC_DATA, $CURRENT_ALBUM_DATA, $THEME_DIR, $FAVPICS, $REFERER;
   global $album, $lang_picinfo, $lang_display_image_php, $lang_byte_units, $lastup_date_fmt;
   $liexifretdatelen = 19;
   $exif = exif_parse_file($filetoExif);
   $sizeofexifarray = sizeof($exif);
   if (($exif === FALSE) || ($sizeofexifarray == 0)) {
      return(FALSE);
   } else {
      $rawdatestring = $exif['DateTime Original'];

      $rawdatestringlen = strlen($rawdatestring);
      if ($rawdatestringlen <= $liexifretdatelen) {
          echo "bad exif return data -->",
               $filetoExif;
          return(FALSE);
      }
      $rawmodelstring = $exif['Model'];


      $rawdatestringt = substr($rawdatestring, 0, $liexifretdatelen);
      $modellen = strlen($rawmodelstring);
      if ($modellen < 1) {
          echo "bad exif return data -->",
               $filetoExif;
          return(FALSE);
      }
      $rawmodelstringt = substr($rawmodelstring, 0, $modellen - 1);


      $rawdateYYYY = substr($rawdatestringt, 0, 4);
      $rawdateMM = substr($rawdatestringt, 5, 2);
      $rawdateDD = substr($rawdatestringt, 8, 2);
      $rawtimeHH = substr($rawdatestringt, 11, 2);
      $rawtimeMM = substr($rawdatestringt, 14, 2);
      $rawtimeSS = substr($rawdatestringt, 17, 2);
      $tdate = checkdate($rawdateMM, $rawdateDD, $rawdateYYYY);
      $tdate1 = TRUE;
      $tdate2 = FALSE;
     
      if (!$tdate || !is_numeric($rawtimeHH) || !is_numeric($rawtimeMM) || !is_numeric($rawtimeSS)) {
         echo "bad date string -->",
              $rawdatestringt,
              " from -->",
              $filetoExif;
         return(FALSE);
      }
//                     0123456789x12345678
// date is returned in YYYY:MM:DD HH:MM:SS format

      return(array($rawdatestringt,$rawmodelstring));
   } // end else
} // end funtion

function update_title($tmaketitlefromdate,
                      $tmakeonlyifblank,
                      $tmaketitleonlyifexifdate,
                      $tlitdate,
                      $tappendmodel,
                      $tpid,
                      $tdatetime,
                      $tgoodexifdate,
                      $tmodel,
                      $tonn) {
   $tfmttitlestr = "";
   if (!$tmaketitlefromdate) {
      return(FALSE);
   }
   if ($tmaketitleonlyifexifdate && !$tgoodexifdate) {
      return(FALSE);
   }
   $tfmttitlestr = "";
   $tdYYYY = substr($tdatetime,0,4);
   $tdMM = substr($tdatetime,5,2);
   $tdDD = substr($tdatetime,8,2);
   $ttHH = substr($tdatetime,11,2);
   $ttMM = substr($tdatetime,14,2);
   $ttSS = substr($tdatetime,17,2);
   $tfmtdatetime = date($tlitdate, mktime($ttHH, $ttMM, $ttSS, $tdMM, $tdDD, $tdYYYY));
   $tfmtdatetimep = str_replace("'", "", $tfmtdatetime);
   if ($tappendmodel) {
      $tfmttitlestr = $tfmtdatetimep . " " . $tmodel;
   } else {
      $tfmttitlestr = $tfmtdatetimep;
   }
   $sql_command = "SELECT * FROM cpg1410_pictures WHERE cpg1410_pictures.pid = '$tpid' LIMIT 1";
   $sql_res = mysql_query($sql_command,$tonn) or die(mysql_error());
   $sql_numrs = mysql_num_rows($sql_res);
   if ($sql_numrs != 1) {
      die("fatal data inconsistency detected, missing pictures record, pid-->" . $tpid);
   }
   $r = mysql_fetch_array($sql_res);
   $tfilepid = $r["pid"];
   $tfiletitle = $r["title"];
   if ($tmakeonlyifblank) {
      $tfiletitlet = trim($tfiletitle);
      if ($tfiletitlet != "") {
         return (FALSE);
      }
   }

   $tfiletitlep = TRIM($tfiletitle);
   $tfmttitlestrp = TRIM($tfmttitlestr);
   if ($tfiletitlep == $tfmttitlestrp) {
      return(FALSE);
   }
   $sql_command = "UPDATE cpg1410_pictures SET cpg1410_pictures.title = '$tfmttitlestrp' WHERE cpg1410_pictures.pid = $tpid";
   $sql_result = mysql_query($sql_command,$tonn) or die(mysql_error());
   return(TRUE);
} // end funtion
« Last Edit: January 02, 2007, 07:28:43 am by GauGau »
Logged

bennettsweb

  • Coppermine newbie
  • Offline Offline
  • Posts: 7

THanks for this very usefull and requested mod!  I have a patched together mod for this but will impliment this when I do a clean install.

Thanks!
Logged

hoover

  • Coppermine newbie
  • Offline Offline
  • Posts: 10

HI
I tried the below script and it seems to run but it doenst sort my pics?

Ive been getting frustrated with coppermine and the fact that all pics uploaded end up in a reverse sort order. Ive tried everything else on this forum and came across your srcript, it looked like a good solution but it doesnt work for me.

I uploaded 6 pics via ftp and then added them using the batch add and then ran the exifier.
they still show up in the wrong order. I tried just for fun to sort them in the wrong order using the up down and then ran the exifier again and still the same order.

All i want it to be able to upload my pics and have them in the order they were taken.

any help appreciated

Gord
Logged

macmiller

  • Contributor
  • Coppermine regular visitor
  • ***
  • Offline Offline
  • Posts: 80
Re: Exifier Sort Thumbnails based on the Exif date/time the picture was taken
« Reply #3 on: February 01, 2007, 01:09:23 am »

Can you first check under the config tab, Thumbnail view option.  There will be a 'Default sort order for files'.  Set this to 'Position ascending', if it isn't already.
Logged

hoover

  • Coppermine newbie
  • Offline Offline
  • Posts: 10
Re: Exifier Sort Thumbnails based on the Exif date/time the picture was taken
« Reply #4 on: February 01, 2007, 09:13:14 pm »

Hi Macmiller

yup i tried both ascending and descending with the same results, the order stays the same.
Just curious when i run the exifier after uploading is anything supposed to happen after? i just get a white screen saying


starting coppermine Exif Sorter process .....

and thats where it stops.

thanks for your help
Gordon
Logged

macmiller

  • Contributor
  • Coppermine regular visitor
  • ***
  • Offline Offline
  • Posts: 80
Re: Exifier Sort Thumbnails based on the Exif date/time the picture was taken
« Reply #5 on: February 02, 2007, 01:02:41 am »

When you go to the screen that displays thumbnails, do you see the 'TITLE +-', 'FILE NAME + -' 'DATE + -' 'POSITION + -' in the upper right hand corner?  You can hit the position + - and this will sort the thumbnails, whether or not you have run the Exifier.  This should change the sort order of the thumbnails.

At the end of the Exifier run, there is a message output to the screen 'process completed successfully' with various statistics displayed.  If you aren't seeing this, and don't see any other error output from the script, it means PHP has a fatal error and you are not seeing it.  Did you configure the following line in the script?

Code: [Select]
$conn = mysql_connect("localhost","xxxxx","xxxxx") or die(mysql_error());
If so, can you check and see what the PHP error being produced is?
Logged

mini1400

  • Coppermine novice
  • *
  • Offline Offline
  • Gender: Male
  • Posts: 34
    • Minis & Megaliths
Re: Exifier Sort Thumbnails based on the Exif date/time the picture was taken
« Reply #6 on: February 02, 2007, 04:39:42 pm »

You also need to change all occurances of cpg1410_pictures to whatever your pictures file is called....

mini1400

  • Coppermine novice
  • *
  • Offline Offline
  • Gender: Male
  • Posts: 34
    • Minis & Megaliths
Re: Exifier Sort Thumbnails based on the Exif date/time the picture was taken
« Reply #7 on: February 02, 2007, 04:43:15 pm »

Sorry, should be all references to cpg1410_ to the prefix that your database has...

macmiller

  • Contributor
  • Coppermine regular visitor
  • ***
  • Offline Offline
  • Posts: 80
Re: Exifier Sort Thumbnails based on the Exif date/time the picture was taken
« Reply #8 on: February 03, 2007, 04:11:29 am »

Good point about the hard coded file prefixes (cpg1410_).  Use the the following version, it is generic with no hard coding so you don't have to change file names or enter connection passwords to the DB. ;)

Exifier version v 1.2 follows

Code: [Select]
<?PHP
   ini_set('display_errors', 1);
   error_reporting(E_ALL);
/***************************************************
  PHP Script to Sort Coppermine Thumbnails based on
  Exif DateTimeOriginal field and optionally title
  the pictures based on this same date.
  **************************************************
  v1.0 originally written by K Miller
  v1.2 3-Feb-07 Changed hard coded file references.
  script name = exifier.php
  ********************************************
  $Source$
  $Revision: 0002 $
  $Author: k miller $
  $Date: 2006-12-30 12:10:47 +0200
**********************************************/
/**********************************************
  The script has the main purpose of accessing the Exif data related to your pictures
  and sorting the thumbnails according to the date the picture was taken.
  Before starting, make sure that you have the coppermine config Thumbnail view option
  set to 'Position ascending'.  Otherwise the thumbnails will continue to sort as before.
  Even if you have another sort option selected, you can test the script was successful by
  invoking the POSITION + - sort on the thumbnail display page. 

  One thing should probably be noted about Exif data, why it is useful, and how it is stored
  by coppermine.  Exif data is data that is stored as apart of your image files that come
  with the camera.  Exif data stores the date and time the picture was taken, camera model,
  exposure info and a bunch of other really good stuff.  The date and time the picture was
  taken turns out to be a very good sort critera, because the last mod date on the JPG file
  can get updated at several different points, making it almost useless.

  Coppermine has the ability to parse and display coppermine info and save
  it to a table, cpg1410_exif.  This information is stored only when an Info (detail) view is
  done on the detailed photo image.  You should also have the config option under
  'Files and thumbnails advanced settings' 'Read EXIF data in JPEG files' to Yes, for even
  this data to display.  If you look at the number of records you have in the cpg1410_exif
  table and pictures_exif table you will probably see the number of records is not the same.
  Why?  Because the picture has not yet been 'infoed' and the EXIF data hasn't be saved to
  the file yet.

  Sorry for the longwinded lead-up, this script looks for EXIF data in two ways.  First it looks
  to see if the data has already be saved off to the cpg1410_exif table.  If it hasn't then
  it calls the currently used coppermine Exif parse routines to parse and store the date to the
  Exif table and then continues on processing as normal.

  Another usefull thing this this script allows you to do is to create picture titles based on
  the EXIF picture taken and also optionally append the model name.  The title is created
  according to a date variable you set up.

  To get started put this script into your coppermine default directory.  So if you access
  your photo album at www.joesblowssite.com/myprettypics you would run this script out of there
  as www.joeblowssite.com/myprettypics/exifier.php.

  Other than that, read and set up the run time variables in the VARIABLE SET UP SECTION.  The
  final thing will be to set up the connection parameters in the mysql_connect line.  the xxxxx's
  need to be replaced by the username and password.  You can get these from the isp admin if
  you don't know them already.

  summary statistics are output at the end if everything goes well.

  If you use this script I would appreciate a forum note, let me know how it works for you.
  Likewise please let me know about any problems you encounter.  I've used it a lot and it
  really saves me a lot of time which before I was spending tweaking file mod dates and
  renaming JPG files with the date/time . 

  I have tested this script on three different camera models, Nokia N72, Canon SD500 and
  Sony DSC-T1 but the processing should be the same for any camera model, ie. I think it
  will work.
/**        VARIABLE SET UP SECTION                                                      **/
/** Set up how you want the script to operate here **/
// set up how you want the title generated
   $limaketitlefromdate = TRUE; //if set to TRUE creates titles based on Exif DateTimeOriginal
   $limaketitleonlyifblank = FALSE; //TRUE -> only process titles that are blank
   $limaketitleonlyifexifdate = FALSE; // Controls when titles should be generated.
//         If TRUE only valid EXIF dates will be used to generate titles.
//         If FALSE and there is not EXIF, the picture create date will be used.
   $lititlestring = "'n/j/Y G:i'"; //Make pic title look like this (PHP Date format)
   $liappendcameramodeltotitle = FALSE; //TRUE means that the title will consist of date + camera model
   $ligenerateexif = TRUE; //In the current coppermine architecture, Exif rows are only generated
//         once an info (detail list) is done on the picture.  Setting this to TRUE means that
//         this script will auto-create the Exif rows (the same as displayimage.php does).  The
//         advantage of this is you don't need to manually list each picture after uploading.
   
/***************************************************************/
   define('IN_COPPERMINE', true);
   define('DISPLAYIMAGE_PHP', true);
   define('INDEX_PHP', true);
   include("include/exif_php.inc.php");
   require('include/init.inc.php');


// various literals
   $lialbums = "albums/";
   $liexifdatestr = "DateTimeOriginal";
   $liexifmodelstr = "Model";
   $lilendatestr = 16;
   $lilenmodelstr = 5;
   $limodelpre = ":\"";
   $limodelterm = ";s";
   $lidatedataoffset = 8;
   $limodeldataoffset = 7;
   $lidatelen = 19;
   $lilastdate = "9999:99:99 23:59:59";
   $makeunique = -1;
   $exifdatear = array();
   $stattitlefieldsupdated = 0;
   $statpicrecsprocessed = 0;
   $statrecordsupdatedforsort = 0;
   $statexifrecsgenerated = 0;
   echo "starting coppermine Exif Sorter process ..... ",
        "<br>";
   
   $sql_command = "SELECT * FROM {$CONFIG['TABLE_PICTURES']}";
   $sql_result = cpg_db_query($sql_command);
   $sql_numrows = 0;
   $sql_numrows = mysql_num_rows($sql_result);
   if ($sql_numrows == 0) {
      die ("empty pictures table");
   }   
   while ($row = mysql_fetch_array($sql_result)) {
      ++$makeunique;
      ++$statpicrecsprocessed;
      $ecameramodel = "";
      $pfilepid = $row["pid"];
      $pfileaid = $row["aid"];
      $pfilepath = $row["filepath"];
      $pfilename = $row["filename"];
      $pfiletitle = $row["title"];
      $pfilectime = $row["ctime"];

// format variables to be used with this picture row
      $prettypid = makenbrpretty ($pfilepid, 8);
      $prettyaid = makenbrpretty ($pfileaid, 8);
      $prettyunique = makenbrpretty ($makeunique, 8);
      $exifdata = $prettypid . $pfilepath . $pfilename;

// make a date from the record create time
      $pcreatedate = date('Y:m:d H:i:s', $pfilectime);
      $goodexifdate = FALSE;
      $datesourcecreate = TRUE;
      $ekey = 'albums/' . $pfilepath . $pfilename;
      $sql_command = "SELECT * FROM {$CONFIG['TABLE_EXIF']} WHERE {$CONFIG['TABLE_EXIF']}.filename = '$ekey' LIMIT 1";
      $sql_result1 = cpg_db_query($sql_command);
      $sql_numrows1 = mysql_num_rows($sql_result1);
      switch ($sql_numrows1) {
      case 0:
         echo "warning no Exif data found for -->",
               $ekey,
               "<br>";
         if ($ligenerateexif) {
            $rexif = exif_parser($ekey);
            if ($rexif === FALSE) {
               $exifkey = $prettyaid . $pcreatedate . $prettyunique;
               $exifdata = array($pfilepid, $pfilepath, $pfilename, $goodexifdate, $ecameramodel);
               $exifdatearr[$exifkey] = $exifdata;
            } else {
               $goodexifdate = TRUE;
               ++$statexifrecsgenerated;
               $exifkey = $prettyaid . $rexif[0] . $prettyunique;
               $exifdata = array($pfilepid, $pfilepath, $pfilename, $goodexifdate, $rexif[1]);
               $exifdatearr[$exifkey] = $exifdata;
            } // end if else if ($rexif === FALSE)
         } else {
            $exifkey = $prettyaid . $pcreatedate . $prettyunique;
            $exifdata = array($pfilepid, $pfilepath, $pfilename, $goodexifdate, $ecameramodel);
            $exifdatearr[$exifkey] = $exifdata;
         } // end if else if ($ligenerateexif) {
         break;
      case 1:
         $row1 = mysql_fetch_array($sql_result1);
         $exiftextdata = rtrim($row1["exifData"]);
         $exiflength = strlen($exiftextdata);
// parse out the create date
         $posdate = strpos($exiftextdata, $liexifdatestr);
         if ($posdate === false) {
            echo "warning no date string in Exif record -->",
                 $ekey,
                 "<br>";
            $exifkey = $prettyaid . $pcreatedate . $prettyunique;
            $exifdata = array($pfilepid, $pfilepath, $pfilename, $goodexifdate, $ecameramodel);
            $exifdatearr[$exifkey] = $exifdata;
            break;
         }
         $goodexifdate = TRUE;
         $posdatefc = $posdate + $lidatedataoffset + $lilendatestr;
         $posdatelc = $posdatefc + $lidatelen - 1;
         if ($posdatelc > $exiflength) {
            die ("bad exif record, date data corrupt -->" . $ekey);
         }
         $exifdate = substr($exiftextdata,$posdatefc,$lidatelen);


// parse out the model
         $posmodel = strpos($exiftextdata, $liexifmodelstr);
         if ($posmodel === false) {
            echo "warning no model string in Exif record -->",
                 $ekey,
                 "<br>";
            $exifkey = $prettyaid . $exifdate . $prettyunique;
            $exifdata = array($pfilepid, $pfilepath, $pfilename, $goodexifdate, $ecameramodel);
            $exifdatearr[$exifkey] = $exifdata;
            break;
         }
// to determine the last character of the model, start scan from the first char looking
// for the termination strings
         $lastcharofmodellit = $posmodel + $lilenmodelstr - 1;
         $startscanpre = $lastcharofmodellit + 1;
         $positionofpre = strpos($exiftextdata, $limodelpre, $startscanpre);
         if ($positionofpre === FALSE) {
            echo "warning expected prexif before model in Exif record not found -->",
                 $ekey,
                 "<br>";
            $exifkey = $prettyaid . $exifdate . $prettyunique;
            $exifdata = array($pfilepid, $pfilepath, $pfilename, $goodexifdate, $ecameramodel);
            $exifdatearr[$exifkey] = $exifdata;
            break;
         }
         $posmodelfc = $positionofpre + 2;
         $posmodellc = strpos($exiftextdata, $limodelterm, $posmodelfc) - 2;
         if ($posmodellc === FALSE) {
            echo "warning expected model terminator in Exif record not found -->",
                 $ekey,
                 "<br>";
            $exifkey = $prettyaid . $exifdate . $prettyunique;
            $exifdata = array($pfilepid, $pfilepath, $pfilename, $goodexifdate, $ecameramodel);
            $exifdatearr[$exifkey] = $exifdata;
            break;
         }
         if ($posdatelc > $exiflength) {
            die ("bad exif record, date data corrupt -->" . $ekey);
         }
         $posmodellen = $posmodellc - $posmodelfc + 1;
         if ($posmodellen <= 1) {
            die ("bad exif record, date data corrupt -->" . $ekey);
         }

         $exifmodel = substr($exiftextdata,$posmodelfc,$posmodellen - 1);

// complete parsing of model

         $exifkey = $prettyaid . $exifdate . $prettyunique;
         $exifdata = array($pfilepid, $pfilepath, $pfilename, $goodexifdate, $exifmodel);
         $exifdatearr[$exifkey] = $exifdata;
         break;
      default:
         die ("bad number of Exif matches -->" . $ekey . " matches-->" . $sql_numrows1);
         break;
      } /** end switch on matching exif data **/
   } // end while main loop on pictures file
   $sortgood = ksort($exifdatearr, SORT_STRING);
   if (!$sortgood) {
      die ("error sorting exif data array");
   }
   $lastalbum = 0;
   $lastposition = 100;
   $firstrec = TRUE;
   foreach ($exifdatearr as $key => $val) {
      $salbum = substr($key,0,8);
      $sdatetime = substr($key,8,$lidatelen);
      $spid = $val[0];
      $sfilepath = $val[1];
      $sfilename = $val[2];
      $sgoodexifdate = $val[3];
      $smodel = $val[4];
      if ($firstrec) {
         $firstrec = FALSE;
         $lastalbum = $salbum;
      } else {
         if ($lastalbum != $salbum) {
            $lastalbum = $salbum;
            $lastposition = 100;
         }
      } // end if ($firstrec)
      $sql_command = "UPDATE {$CONFIG['TABLE_PICTURES']} SET {$CONFIG['TABLE_PICTURES']}.position = '$lastposition' WHERE {$CONFIG['TABLE_PICTURES']}.pid = $spid";
      $sql_result = cpg_db_query($sql_command);
      ++$statrecordsupdatedforsort;

      $ruptitle = update_title($limaketitlefromdate,
                               $limaketitleonlyifblank,
                               $limaketitleonlyifexifdate,
                               $lititlestring,
                               $liappendcameramodeltotitle,
                               $spid,
                               $sdatetime,
                               $sgoodexifdate,
                               $smodel);
      if ($ruptitle) {
         ++$stattitlefieldsupdated;
      }
      ++$lastposition;
   } // end foreach ($exifdatearr as $key => $val)
   echo "process completed successfully",
        "<br>",
        "picture rows processed -->",
        $statpicrecsprocessed,
        "<br>",
        "picture rows updated for sort position -->",
        $statrecordsupdatedforsort,
        "<br>",
        "Exif rows generated -->",
        $statexifrecsgenerated,
        "<br>",
        "Title fields updated -->",
        $stattitlefieldsupdated,
        "<br>";
       

function makenbrpretty ($inputstring, $outputlength) {
// Take the pass number and return it as
   $mpipstrlen = strlen($inputstring);
   $mpopstrlen = $outputlength;
   $mpstrzeros = "";
   for ($a = 0; $a <= $outputlength; $a++) {
      $mpstrzeros .= "0";
   }
   $nbrzerostoadd = $outputlength - $mpipstrlen;
   $leftzeros = substr($mpstrzeros, 1, $nbrzerostoadd);
   $mpret = $leftzeros . $inputstring;
   return ($mpret);
}

# Sanitize the data - to fix the XSS vulnerability - Aditya
function sanitize_data(&$value, $key)
{
if (is_array($value)) {
array_walk($value, 'sanitize_data');
} else {
# sanitize against sql/html injection; trim any nongraphical non-ASCII character:
$value = trim(htmlentities(strip_tags(trim($value,"\x7f..\xff\x0..\x1f")),ENT_QUOTES));
}
}

function exif_parser($filetoExif) {
   global $CONFIG, $CURRENT_PIC_DATA, $CURRENT_ALBUM_DATA, $THEME_DIR, $FAVPICS, $REFERER;
   global $album, $lang_picinfo, $lang_display_image_php, $lang_byte_units, $lastup_date_fmt;
   $liexifretdatelen = 19;
   $exif = exif_parse_file($filetoExif);
   $sizeofexifarray = sizeof($exif);
   if (($exif === FALSE) || ($sizeofexifarray == 0)) {
      return(FALSE);
   } else {
      $rawdatestring = $exif['DateTime Original'];

      $rawdatestringlen = strlen($rawdatestring);
      if ($rawdatestringlen <= $liexifretdatelen) {
          echo "bad exif return data -->",
               $filetoExif;
          return(FALSE);
      }
      $rawmodelstring = $exif['Model'];


      $rawdatestringt = substr($rawdatestring, 0, $liexifretdatelen);
      $modellen = strlen($rawmodelstring);
      if ($modellen < 1) {
          echo "bad exif return data -->",
               $filetoExif;
          return(FALSE);
      }
      $rawmodelstringt = substr($rawmodelstring, 0, $modellen - 1);


      $rawdateYYYY = substr($rawdatestringt, 0, 4);
      $rawdateMM = substr($rawdatestringt, 5, 2);
      $rawdateDD = substr($rawdatestringt, 8, 2);
      $rawtimeHH = substr($rawdatestringt, 11, 2);
      $rawtimeMM = substr($rawdatestringt, 14, 2);
      $rawtimeSS = substr($rawdatestringt, 17, 2);
      $tdate = checkdate($rawdateMM, $rawdateDD, $rawdateYYYY);
      $tdate1 = TRUE;
      $tdate2 = FALSE;
     
      if (!$tdate || !is_numeric($rawtimeHH) || !is_numeric($rawtimeMM) || !is_numeric($rawtimeSS)) {
         echo "bad date string -->",
              $rawdatestringt,
              " from -->",
              $filetoExif;
         return(FALSE);
      }
//                     0123456789x12345678
// date is returned in YYYY:MM:DD HH:MM:SS format

      return(array($rawdatestringt,$rawmodelstring));
   } // end else
} // end funtion

function update_title($tmaketitlefromdate,
                      $tmakeonlyifblank,
                      $tmaketitleonlyifexifdate,
                      $tlitdate,
                      $tappendmodel,
                      $tpid,
                      $tdatetime,
                      $tgoodexifdate,
                      $tmodel) {

   global $CONFIG;

   $tfmttitlestr = "";
   if (!$tmaketitlefromdate) {
      return(FALSE);
   }
   if ($tmaketitleonlyifexifdate && !$tgoodexifdate) {
      return(FALSE);
   }
   $tfmttitlestr = "";
   $tdYYYY = substr($tdatetime,0,4);
   $tdMM = substr($tdatetime,5,2);
   $tdDD = substr($tdatetime,8,2);
   $ttHH = substr($tdatetime,11,2);
   $ttMM = substr($tdatetime,14,2);
   $ttSS = substr($tdatetime,17,2);
   $tfmtdatetime = date($tlitdate, mktime($ttHH, $ttMM, $ttSS, $tdMM, $tdDD, $tdYYYY));
   $tfmtdatetimep = str_replace("'", "", $tfmtdatetime);
   if ($tappendmodel) {
      $tfmttitlestr = $tfmtdatetimep . " " . $tmodel;
   } else {
      $tfmttitlestr = $tfmtdatetimep;
   }
   $sql_command = "SELECT * FROM {$CONFIG['TABLE_PICTURES']} WHERE {$CONFIG['TABLE_PICTURES']}.pid = '$tpid' LIMIT 1";
   $sql_res = cpg_db_query($sql_command);
   $sql_numrs = mysql_num_rows($sql_res);
   if ($sql_numrs != 1) {
      die("fatal data inconsistency detected, missing pictures record, pid-->" . $tpid);
   }
   $r = mysql_fetch_array($sql_res);
   $tfilepid = $r["pid"];
   $tfiletitle = $r["title"];
   if ($tmakeonlyifblank) {
      $tfiletitlet = trim($tfiletitle);
      if ($tfiletitlet != "") {
         return (FALSE);
      }
   }

   $tfiletitlep = TRIM($tfiletitle);
   $tfmttitlestrp = TRIM($tfmttitlestr);
   if ($tfiletitlep == $tfmttitlestrp) {
      return(FALSE);
   }
   $sql_command = "UPDATE {$CONFIG['TABLE_PICTURES']} SET {$CONFIG['TABLE_PICTURES']}.title = '$tfmttitlestrp' WHERE {$CONFIG['TABLE_PICTURES']}.pid = $tpid";
   $sql_result = cpg_db_query($sql_command);
   return(TRUE);
} // end funtion
Logged

macmiller

  • Contributor
  • Coppermine regular visitor
  • ***
  • Offline Offline
  • Posts: 80
Re: Exifier Sort Thumbnails based on the Exif date/time the picture was taken
« Reply #9 on: February 06, 2007, 11:36:15 am »

I've been using this on a wide variety of cameras, more than I originally tested it on.  One thing that showed up is that the exif.php and exif_php.inc.php which were original coppermine modules to deal with parsing exif data don't handle all camera types well.  I believe now that these modules were probably written before php added functions which all the parsing to be done by php.  So to make the thing work for me I've created a new version which does direct php function calls (exif_read_data) to get the exif headers. 

I just ran this on data from about 8 different camera modules and it seems to be working OK.

The PHP manual has this note about exif support.  My site admin didn't have to do anything, our PHP was already configured with this on, which I suspect is the default. 
Quote
To enable exif-support configure PHP with --enable-exif

Windows users must enable both the php_mbstring.dll and php_exif.dll DLL's in php.ini. The php_mbstring.dll DLL must be loaded before the php_exif.dll DLL so adjust your php.ini accordingly.
What this means is if this script errors out on these function calls you may need to have your admin tweak the configuration as stated.

Here is the new version.
Code: [Select]
<?PHP
   ini_set('display_errors', 1);
   error_reporting(E_ALL);
/***************************************************
  PHP Script to Sort Coppermine Thumbnails based on
  Exif DateTimeOriginal field and optionally title
  the pictures based on this same date.
  **************************************************
  v1.0 originally written by K Miller
  v1.2 3-Feb-07 Changed hard coded file references.
  v1.3 5-Feb-07 Corrected echo statements to have <br>.
  v2.0 6-Feb-07 Stripped out all of the existing code dealing with the exif.php and
  exif_php.inc.php.  These didn't work on several camera types and believe were coded
  before the php exif routines became available.  Now the Exif data is read from each
  image each time, the Exif data store is not accessed.
  script name = exifier2.php
  ********************************************
  $Source$
  $Revision: 0003 $
  $Author: k miller $
  $Date: 2006-12-30 12:10:47 +0200
**********************************************/
/**********************************************
  The script has the main purpose of accessing the Exif data related to your pictures
  and sorting the thumbnails according to the date the picture was taken.
  Before starting, make sure that you have the coppermine config Thumbnail view option
  set to 'Position ascending'.  Otherwise the thumbnails will continue to sort as before.
  Even if you have another sort option selected, you can test the script was successful by
  invoking the POSITION + - sort on the thumbnail display page. 

  Earlier versions of this script relied on the existing coppermine Exif parsing, which proved
  problematic.  Now the Exif tags are retrieved via direct PHP function calls.

  Another usefull thing this this script allows you to do is to create picture titles based on
  the EXIF picture taken and also optionally append the model name.  The title is created
  according to a date variable you set up.

  To get started put this script into your coppermine default directory.  So if you access
  your photo album at www.joesblowssite.com/myprettypics you would run this script out of there
  as www.joeblowssite.com/myprettypics/exifier.php.

  Other than that, read and set up the run time variables in the VARIABLE SET UP SECTION.

  summary statistics are output at the end if everything goes well.

  If you use this script I would appreciate a forum note, let me know how it works for you.
  Likewise please let me know about any problems you encounter.  I've used it a lot and it
  really saves me a lot of time which before I was spending tweaking file mod dates and
  renaming JPG files with the date/time . 

  I have tested this script on three different camera models, Nokia N72, Canon SD500 and
  Sony DSC-T1 but the processing should be the same for any camera model, ie. I think it
  will work.
/**        VARIABLE SET UP SECTION                                                      **/
/** Set up how you want the script to operate here **/
// set up how you want the title generated
   $limaketitlefromdate = TRUE; //if set to TRUE creates titles based on Exif DateTimeOriginal
   $limaketitleonlyifblank = FALSE; //TRUE -> only process titles that are blank
   $limaketitleonlyifexifdate = FALSE; // Controls when titles should be generated.
//         If TRUE only valid EXIF dates will be used to generate titles.
//         If FALSE and there is not EXIF, the picture create date will be used.
   $lititlestring = "'n/j/Y G:i'"; //Make pic title look like this (PHP Date format)
   $liappendcameramodeltotitle = FALSE; //TRUE means that the title will consist of date + camera model
   
/***************************************************************/
   define('IN_COPPERMINE', true);
   define('DISPLAYIMAGE_PHP', true);
   define('INDEX_PHP', true);
   require('include/init.inc.php');


// various literals
   $lidatelen = 19;
   $makeunique = -1;
   $exifdatearr = array();
   $stattitlefieldsupdated = 0;
   $statpicrecsprocessed = 0;
   $statrecordsupdatedforsort = 0;
   echo "starting coppermine Exif Sorter process ..... ",
        "<br>";
   
   $sql_command = "SELECT * FROM {$CONFIG['TABLE_PICTURES']}";
   $sql_result = cpg_db_query($sql_command);
   $sql_numrows = 0;
   $sql_numrows = mysql_num_rows($sql_result);
   if ($sql_numrows == 0) {
      die ("empty pictures table");
   }   
   while ($row = mysql_fetch_array($sql_result)) {
      ++$makeunique;
      ++$statpicrecsprocessed;
      $ecameramodel = "";
      $pfilepid = $row["pid"];
      $pfileaid = $row["aid"];
      $pfilepath = $row["filepath"];
      $pfilename = $row["filename"];
      $pfiletitle = $row["title"];
      $pfilectime = $row["ctime"];

// format variables to be used with this picture row
      $prettypid = makenbrpretty ($pfilepid, 8);
      $prettyaid = makenbrpretty ($pfileaid, 8);
      $prettyunique = makenbrpretty ($makeunique, 8);
      $exifdata = $prettypid . $pfilepath . $pfilename;

// make a date from the record create time
      $pcreatedate = date('Y:m:d H:i:s', $pfilectime);
      $goodexifdate = FALSE;
      $datesourcecreate = TRUE;
      $ekey = 'albums/' . $pfilepath . $pfilename;

      $exif = array();
      $exif = exif_read_data($ekey, 'ANY_TAG', TRUE, FALSE);
      if (($exif === FALSE) || (empty($exif)) || (empty($exif['EXIF']['DateTimeOriginal']))) {
         $exifkey = $prettyaid . $pcreatedate . $prettyunique;
         $exifdata = array($pfilepid, $pfilepath, $pfilename, $goodexifdate, $ecameramodel);
         $exifdatearr[$exifkey] = $exifdata;
      } else {
         $exifdate = $exif['EXIF']['DateTimeOriginal'];
         $goodexifdate = TRUE;
         $exifmodel = "";
         $exifmodel = $exif['IFD0']['Model'];
         $exifkey = $prettyaid . $exifdate . $prettyunique;
         $exifdata = array($pfilepid, $pfilepath, $pfilename, $goodexifdate, $exifmodel);
         $exifdatearr[$exifkey] = $exifdata;
      } // end if (($exif === FALSE).....
   } // end while main loop on pictures file
   $sortgood = ksort($exifdatearr, SORT_STRING);
   if (!$sortgood) {
      die ("error sorting exif data array");
   }
   $lastalbum = 0;
   $lastposition = 100;
   $firstrec = TRUE;
   foreach ($exifdatearr as $key => $val) {
      $salbum = substr($key,0,8);
      $sdatetime = substr($key,8,$lidatelen);
      $spid = $val[0];
      $sfilepath = $val[1];
      $sfilename = $val[2];
      $sgoodexifdate = $val[3];
      $smodel = $val[4];
      if ($firstrec) {
         $firstrec = FALSE;
         $lastalbum = $salbum;
      } else {
         if ($lastalbum != $salbum) {
            $lastalbum = $salbum;
            $lastposition = 100;
         }
      } // end if ($firstrec)
      $sql_command = "UPDATE {$CONFIG['TABLE_PICTURES']} SET {$CONFIG['TABLE_PICTURES']}.position = '$lastposition' WHERE {$CONFIG['TABLE_PICTURES']}.pid = $spid";
      $sql_result = cpg_db_query($sql_command);
      ++$statrecordsupdatedforsort;

      $ruptitle = update_title($limaketitlefromdate,
                               $limaketitleonlyifblank,
                               $limaketitleonlyifexifdate,
                               $lititlestring,
                               $liappendcameramodeltotitle,
                               $spid,
                               $sdatetime,
                               $sgoodexifdate,
                               $smodel);
      if ($ruptitle) {
         ++$stattitlefieldsupdated;
      }
      ++$lastposition;
   } // end foreach ($exifdatearr as $key => $val)
   echo "process completed successfully",
        "<br>",
        "picture rows processed -->",
        $statpicrecsprocessed,
        "<br>",
        "picture rows updated for sort position -->",
        $statrecordsupdatedforsort,
        "<br>",
        "Title fields updated -->",
        $stattitlefieldsupdated,
        "<br>";
       

function makenbrpretty ($inputstring, $outputlength) {
// Take the pass number and return it as
   $mpipstrlen = strlen($inputstring);
   $mpopstrlen = $outputlength;
   $mpstrzeros = "";
   for ($a = 0; $a <= $outputlength; $a++) {
      $mpstrzeros .= "0";
   }
   $nbrzerostoadd = $outputlength - $mpipstrlen;
   $leftzeros = substr($mpstrzeros, 1, $nbrzerostoadd);
   $mpret = $leftzeros . $inputstring;
   return ($mpret);
}

function update_title($tmaketitlefromdate,
                      $tmakeonlyifblank,
                      $tmaketitleonlyifexifdate,
                      $tlitdate,
                      $tappendmodel,
                      $tpid,
                      $tdatetime,
                      $tgoodexifdate,
                      $tmodel) {

   global $CONFIG;

   $tfmttitlestr = "";
   if (!$tmaketitlefromdate) {
      return(FALSE);
   }
   if ($tmaketitleonlyifexifdate && !$tgoodexifdate) {
      return(FALSE);
   }
   $tfmttitlestr = "";
   $tdYYYY = substr($tdatetime,0,4);
   $tdMM = substr($tdatetime,5,2);
   $tdDD = substr($tdatetime,8,2);
   $ttHH = substr($tdatetime,11,2);
   $ttMM = substr($tdatetime,14,2);
   $ttSS = substr($tdatetime,17,2);
   $tfmtdatetime = date($tlitdate, mktime($ttHH, $ttMM, $ttSS, $tdMM, $tdDD, $tdYYYY));
   $tfmtdatetimep = str_replace("'", "", $tfmtdatetime);
   if ($tappendmodel) {
      $tfmttitlestr = $tfmtdatetimep . " " . $tmodel;
   } else {
      $tfmttitlestr = $tfmtdatetimep;
   }
   $sql_command = "SELECT * FROM {$CONFIG['TABLE_PICTURES']} WHERE {$CONFIG['TABLE_PICTURES']}.pid = '$tpid' LIMIT 1";
   $sql_res = cpg_db_query($sql_command);
   $sql_numrs = mysql_num_rows($sql_res);
   if ($sql_numrs != 1) {
      die("fatal data inconsistency detected, missing pictures record, pid-->" . $tpid);
   }
   $r = mysql_fetch_array($sql_res);
   $tfilepid = $r["pid"];
   $tfiletitle = $r["title"];
   if ($tmakeonlyifblank) {
      $tfiletitlet = trim($tfiletitle);
      if ($tfiletitlet != "") {
         return (FALSE);
      }
   }

   $tfiletitlep = TRIM($tfiletitle);
   $tfmttitlestrp = TRIM($tfmttitlestr);
   if ($tfiletitlep == $tfmttitlestrp) {
      return(FALSE);
   }
   $sql_command = "UPDATE {$CONFIG['TABLE_PICTURES']} SET {$CONFIG['TABLE_PICTURES']}.title = '$tfmttitlestrp' WHERE {$CONFIG['TABLE_PICTURES']}.pid = $tpid";
   $sql_result = cpg_db_query($sql_command);
   return(TRUE);
} // end funtion
Logged

!!blue

  • Coppermine newbie
  • Offline Offline
  • Gender: Female
  • Posts: 8
Re: Exifier Sort Thumbnails based on the Exif date/time the picture was taken
« Reply #10 on: February 23, 2007, 04:09:02 pm »

I've been using this script with much success and I have a question. Is there a way to have the script include the image title that coppermine has you set when you upload an image along with the EXIF date/time that the script adds?

For example, I upload an image and name it "Daddy feeding Zoe" and then run the script which names the file "2/21/07 20:36". Can the script name the file "Daddy feeding Zoe | 2/22/07 20:36" or something to that effect?

Just wondering, I love the script! :D

thanks,
Zulema
http://foto.zoblue.com/

macmiller

  • Contributor
  • Coppermine regular visitor
  • ***
  • Offline Offline
  • Posts: 80
Re: Exifier Sort Thumbnails based on the Exif date/time the picture was taken
« Reply #11 on: February 23, 2007, 04:18:03 pm »

It is probably a good suggestion and would be fairly easy to do.   I have to tweak the script for some other stuff, if you don't mind waiting a couple of weeks I'll try to get this done. 
Logged

!!blue

  • Coppermine newbie
  • Offline Offline
  • Gender: Female
  • Posts: 8
Re: Exifier Sort Thumbnails based on the Exif date/time the picture was taken
« Reply #12 on: February 23, 2007, 08:21:47 pm »

Awesome! I patiently await your changes!  ;D

macmiller

  • Contributor
  • Coppermine regular visitor
  • ***
  • Offline Offline
  • Posts: 80
Re: Exifier Sort Thumbnails based on the Exif date/time the picture was taken
« Reply #13 on: February 26, 2007, 01:26:09 am »

Here is version 2.1.  It has a new parameter li-append which has settings of:
 0 = don't append anything (use date as the title)
 1 = make a new title from date + model
 2 = append the date to the existing title (unless it already has the date time)

Also added a check - prior to sorting it checks to see if the the update is necessary. 

Please let me know if this works for you......

Code: [Select]
/***************************************************
  PHP Script to Sort Coppermine Thumbnails based on
  Exif DateTimeOriginal field and optionally title
  the pictures based on this same date.
  **************************************************
  v1.0 originally written by K Miller
  v1.2 3-Feb-07 Changed hard coded file references.
  v1.3 5-Feb-07 Corrected echo statements to have <br>.
  v2.0 6-Feb-07 Stripped out all of the existing code dealing with the exif.php and
  exif_php.inc.php.  These didn't work on several camera types and believe were coded
  before the php exif routines became available.  Now the Exif data is read from each
  image each time, the Exif data store is not accessed.
  script name = exifier2.php
  v2.1 26-Feb-07 Added a parameter li-append mode which allows date to be appended
  to user title.  Also added check prior to update sort row check to see if it is
  a change and only update changes.  Added more logging.
  script name = exifier2a.php
  ********************************************
  $Source$
  $Revision: 0003 $
  $Author: k miller $
  $Date: 2006-12-30 12:10:47 +0200
**********************************************/
/**********************************************
  The script has the main purpose of accessing the Exif data related to your pictures
  and sorting the thumbnails according to the date the picture was taken.
  Before starting, make sure that you have the coppermine config Thumbnail view option
  set to 'Position ascending'.  Otherwise the thumbnails will continue to sort as before.
  Even if you have another sort option selected, you can test the script was successful by
  invoking the POSITION + - sort on the thumbnail display page. 

  Earlier versions of this script relied on the existing coppermine Exif parsing, which proved
  problematic.  Now the Exif tags are retrieved via direct PHP function calls.

  Another usefull thing this this script allows you to do is to create picture titles based on
  the EXIF picture taken and also optionally append the model name.  The title is created
  according to a date variable you set up.

  To get started put this script into your coppermine default directory.  So if you access
  your photo album at www.joesblowssite.com/myprettypics you would run this script out of there
  as www.joeblowssite.com/myprettypics/exifier.php.

  Other than that, read and set up the run time variables in the VARIABLE SET UP SECTION.

  summary statistics are output at the end if everything goes well.

  If you use this script I would appreciate a forum note, let me know how it works for you.
  Likewise please let me know about any problems you encounter.  I've used it a lot and it
  really saves me a lot of time which before I was spending tweaking file mod dates and
  renaming JPG files with the date/time . 

  I have tested this script on three different camera models, Nokia N72, Canon SD500 and
  Sony DSC-T1 but the processing should be the same for any camera model, ie. I think it
  will work.
/**        VARIABLE SET UP SECTION                                                      **/
/** Set up how you want the script to operate here **/
// set up how you want the title generated
   $limaketitlefromdate = TRUE; //if set to TRUE creates titles based on Exif DateTimeOriginal
   $limaketitleonlyifblank = FALSE; //TRUE -> only process titles that are blank
   $limaketitleonlyifexifdate = FALSE; // Controls when titles should be generated.
//         If TRUE only valid EXIF dates will be used to generate titles.
//         If FALSE and there is not EXIF, the picture create date will be used.
// DON'T CHANGE THE DATE FORMAT FOR NOW
   $lititlestring = "'n/j/Y G:i'"; //Make pic title look like this (PHP Date format)
// removed liappendcameramodeltotitle and made an liappendmode
// 0 = don't append anything -- use the date only
// 1 = appendcameramodeltotitle as before
// 2 = appenddatetouserdesc (new)
   $liappendmode = 2;
   
/***************************************************************/
   define('IN_COPPERMINE', true);
   define('DISPLAYIMAGE_PHP', true);
   define('INDEX_PHP', true);
   require('include/init.inc.php');


// various literals
   $lidatelen = 19;
   $makeunique = -1;
   $exifdatearr = array();
   $stattitlefieldsupdated = 0;
   $statpicrecsprocessed = 0;
   $statrecordsupdatedforsort = 0;
   echo "starting coppermine Exif Sorter process ..... ",
        "<br>";
   
   $sql_command = "SELECT * FROM {$CONFIG['TABLE_PICTURES']}";
   $sql_result = cpg_db_query($sql_command);
   $sql_numrows = 0;
   $sql_numrows = mysql_num_rows($sql_result);
   if ($sql_numrows == 0) {
      die ("empty pictures table");
   }   
   while ($row = mysql_fetch_array($sql_result)) {
      ++$makeunique;
      ++$statpicrecsprocessed;
      $ecameramodel = "";
      $pfilepid = $row["pid"];
      $pfileaid = $row["aid"];
      $pfilepath = $row["filepath"];
      $pfilename = $row["filename"];
      $pfiletitle = $row["title"];
      $pfilectime = $row["ctime"];

// format variables to be used with this picture row
      $prettypid = makenbrpretty ($pfilepid, 8);
      $prettyaid = makenbrpretty ($pfileaid, 8);
      $prettyunique = makenbrpretty ($makeunique, 8);
      $exifdata = $prettypid . $pfilepath . $pfilename;

// make a date from the record create time
      $pcreatedate = date('Y:m:d H:i:s', $pfilectime);
      $goodexifdate = FALSE;
      $datesourcecreate = TRUE;
      $ekey = 'albums/' . $pfilepath . $pfilename;

      $exif = array();
      $exif = exif_read_data($ekey, 'ANY_TAG', TRUE, FALSE);
      if (($exif === FALSE) || (empty($exif)) || (empty($exif['EXIF']['DateTimeOriginal']))) {
         $exifkey = $prettyaid . $pcreatedate . $prettyunique;
         $exifdata = array($pfilepid, $pfilepath, $pfilename, $goodexifdate, $ecameramodel);
         $exifdatearr[$exifkey] = $exifdata;
          echo "warning: bad exif date for ",
               $pfilepath,
               $pfilename,
               "<br>";
      } else {
         $exifdate = $exif['EXIF']['DateTimeOriginal'];
         $goodexifdate = TRUE;
         $exifmodel = "";
         $exifmodel = $exif['IFD0']['Model'];
         $exifkey = $prettyaid . $exifdate . $prettyunique;
         $exifdata = array($pfilepid, $pfilepath, $pfilename, $goodexifdate, $exifmodel);
         $exifdatearr[$exifkey] = $exifdata;
      } // end if (($exif === FALSE).....
   } // end while main loop on pictures file
   $sortgood = ksort($exifdatearr, SORT_STRING);
   if (!$sortgood) {
      die ("error sorting exif data array");
   }
   $lastalbum = 0;
   $lastposition = 100;
   $firstrec = TRUE;
   foreach ($exifdatearr as $key => $val) {
      $salbum = substr($key,0,8);
      $sdatetime = substr($key,8,$lidatelen);
      $spid = $val[0];
      $sfilepath = $val[1];
      $sfilename = $val[2];
      $sgoodexifdate = $val[3];
      $smodel = $val[4];
      if ($firstrec) {
         $firstrec = FALSE;
         $lastalbum = $salbum;
      } else {
         if ($lastalbum != $salbum) {
            $lastalbum = $salbum;
            $lastposition = 100;
         }
      } // end if ($firstrec)
// prior to update read pictures table to see if update necessary
      $sql_command = "SELECT * FROM {$CONFIG['TABLE_PICTURES']} WHERE {$CONFIG['TABLE_PICTURES']}.pid = $spid";
      $sql_r1 = cpg_db_query($sql_command);
      $sql_n1 = mysql_num_rows($sql_r1);
      if ($sql_n1 != 1) {
         die("fatal data inconsistency detected, missing pictures record, pid-->" . $spid);
      }
      while ($r1 = mysql_fetch_array($sql_r1)) {
         $pthispos = $r1["position"];
      }
      if ($lastposition != $pthispos) {
         $sql_command = "UPDATE {$CONFIG['TABLE_PICTURES']} SET {$CONFIG['TABLE_PICTURES']}.position = '$lastposition' WHERE {$CONFIG['TABLE_PICTURES']}.pid = $spid";
         $sql_result = cpg_db_query($sql_command);
         ++$statrecordsupdatedforsort;
         echo "sorted ",
              $spid,
              " old pos = ",
              $pthispos,
              " new pos = ",
              $lastposition,
              "<br>";
      }
// if maketitlefromdate = false the routine will return false immeadiately
// if maketitleonlyifblank = the title is only altered if blank
// if maketitleonlyifexifdate = the create date won't be used in the title
// if appenddatetouserdesc = will append the date to the user desc
      $ruptitle = update_title($limaketitlefromdate,
                               $limaketitleonlyifblank,
                               $limaketitleonlyifexifdate,
                               $liappendmode,
                               $lititlestring,
                               $spid,
                               $sdatetime,
                               $sgoodexifdate,
                               $smodel);
      if ($ruptitle) {
         ++$stattitlefieldsupdated;
      }
      ++$lastposition;
   } // end foreach ($exifdatearr as $key => $val)
   echo "process completed successfully",
        "<br>",
        "picture rows processed -->",
        $statpicrecsprocessed,
        "<br>",
        "picture rows updated for sort position -->",
        $statrecordsupdatedforsort,
        "<br>",
        "Title fields updated -->",
        $stattitlefieldsupdated,
        "<br>";
       

function makenbrpretty ($inputstring, $outputlength) {
// Take the pass number and return it as
   $mpipstrlen = strlen($inputstring);
   $mpopstrlen = $outputlength;
   $mpstrzeros = "";
   for ($a = 0; $a <= $outputlength; $a++) {
      $mpstrzeros .= "0";
   }
   $nbrzerostoadd = $outputlength - $mpipstrlen;
   $leftzeros = substr($mpstrzeros, 1, $nbrzerostoadd);
   $mpret = $leftzeros . $inputstring;
   return ($mpret);
}

function update_title($tmaketitlefromdate,
                      $tmakeonlyifblank,
                      $tmaketitleonlyifexifdate,
                      $tappendmode,
                      $tlitdate,
                      $tpid,
                      $tdatetime,
                      $tgoodexifdate,
                      $tmodel) {

   global $CONFIG;

   $tfmttitlestr = "";
   if (!$tmaketitlefromdate) {
      return(FALSE);
   }
   if ($tmaketitleonlyifexifdate && !$tgoodexifdate) {
      return(FALSE);
   }
   $tfmttitlestr = "";
   $tdYYYY = substr($tdatetime,0,4);
   $tdMM = substr($tdatetime,5,2);
   $tdDD = substr($tdatetime,8,2);
   $ttHH = substr($tdatetime,11,2);
   $ttMM = substr($tdatetime,14,2);
   $ttSS = substr($tdatetime,17,2);
   $tfmtdatetime = date($tlitdate, mktime($ttHH, $ttMM, $ttSS, $tdMM, $tdDD, $tdYYYY));
   $tfmtdatetimep = str_replace("'", "", $tfmtdatetime);
   $datelen = strlen($tfmtdatetimep);
   $sql_command = "SELECT * FROM {$CONFIG['TABLE_PICTURES']} WHERE {$CONFIG['TABLE_PICTURES']}.pid = '$tpid' LIMIT 1";
   $sql_res = cpg_db_query($sql_command);
   $sql_numrs = mysql_num_rows($sql_res);
   if ($sql_numrs != 1) {
      die("fatal data inconsistency detected, missing pictures record, pid-->" . $tpid);
   }
   $r = mysql_fetch_array($sql_res);
   $tfilepid = $r["pid"];
   $tfiletitle = $r["title"];
   if ($tmakeonlyifblank) {
      $tfiletitlet = trim($tfiletitle);
      if ($tfiletitlet != "") {
         return (FALSE);
      }
   }
// appendmode = 0 = don't append anything, use the date only
// appendmode = 1 = make a final description from date + model
// appendmode = 2 = use the existing desc and append the date
// for appendmode = 2 must check to see if the existing desc is already a date
// if so we just leave it alone

// here will will take the last 16 characters of the current desc to see if
// if already contains the date/time
   $currdesccontainsdate = FALSE;
   $tfiletitlep = TRIM($tfiletitle);
   $desclen = strlen($tfiletitlep);
   $strs = $desclen - $datelen;
   if ($desclen < $datelen) {
      $tdateport = $tfiletitlep;
   } else {
      $tdateport = substr($tfiletitlep, $strs, $datelen);
   }
   if ($tdateport == $tfmtdatetimep) {
      $currdesccontainsdate = TRUE;
   }

   $tfiletitlep = TRIM($tfiletitle);
   switch ($tappendmode) {
   case 0: // in this mode we don't append anything
      $tfmttitlestr = $tfmtdatetimep;
      break;
   case 1: // append the model to the date (date + model)
      $tfmttitlestr = $tfmtdatetimep . " " . $tmodel;
      break;
   case 2: // append the date after the description (desc + date)
      $tfmttitlestr = $tfiletitlep;
      if (!$currdesccontainsdate) {
         $tfmttitlestr = $tfiletitlep . " " . $tfmtdatetimep;
      }
      break;
   }

   $tfmttitlestrp = TRIM($tfmttitlestr);
   if ($tfiletitlep == $tfmttitlestrp) {
      return(FALSE);
   }
   $sql_command = "UPDATE {$CONFIG['TABLE_PICTURES']} SET {$CONFIG['TABLE_PICTURES']}.title = '$tfmttitlestrp' WHERE {$CONFIG['TABLE_PICTURES']}.pid = $tpid";
   $sql_result = cpg_db_query($sql_command);
   return(TRUE);
} // end funtion
Logged

!!blue

  • Coppermine newbie
  • Offline Offline
  • Gender: Female
  • Posts: 8
Re: Exifier Sort Thumbnails based on the Exif date/time the picture was taken
« Reply #14 on: February 26, 2007, 05:56:04 pm »

Hmm.. I ran it as is and got this error message....

Code: [Select]
starting coppermine Exif Sorter process .....
warning: bad exif date for userpics/10001/scattered_clouds.jpg
warning: bad exif date for userpics/10001/haze_sun.jpg
warning: bad exif date for userpics/10001/checker_clouds.jpg
warning: bad exif date for bluepics/IMG_0362.jpg
....snip....
warning: bad exif date for userpics/10001/crop4.jpg
warning: bad exif date for userpics/10001/crop3.jpg
warning: bad exif date for userpics/10001/sleepy_zoe2.jpg
warning: bad exif date for userpics/10001/sleepy_zoe.jpg
process completed successfully
picture rows processed -->794
picture rows updated for sort position -->0
Title fields updated -->0

Every picture is listed but I snipped the returned message. It looks like I have to rename each file manually myself then run this new version of the script. Is that right? Because right now the pictures already have the date so the script returns that error message above.  Or did I do something wrong ???

macmiller

  • Contributor
  • Coppermine regular visitor
  • ***
  • Offline Offline
  • Posts: 80
Re: Exifier Sort Thumbnails based on the Exif date/time the picture was taken
« Reply #15 on: February 27, 2007, 01:10:10 am »

Hi Blue:

I added in those warning messages.  What is is supposed to be telling you is for a particular pic whether or not there was Exif camera data stored in the jpeg image.  When you take an image from your camera, the digital camera software stores a whole bunch of camera specific data right in the image, such as the date the picture was taken and the type of camera it is.  You can see Exif data by opening up the picture in a image processing program like Misrosoft Office Picture Manager and viewing the Properties tab.  Or easier yet, in Windows at least you can simply float the cursor over the image and a pop up infomation box will show you the Exif data, if any.  (There is also an info key on coppermine that will show you the Exif data if you have the config set up to display Exif data).

I took some jpg images from your site, but didn't get the specific ones in the error messages (too many to look through).  The ones I looked at were:
normal_halfsun.jpg
and normal_haze_sun.jpg

normal_halfsun.jpg has all of the Exif data, was taken with a Canon A80 on 9/23/2004 at 9:14AM
normal_haze_sun.jpg on the other hand does not have any associate Exif data.  The actual date/time the picture was taken is lost.

Look at the messages coming out and examine the files to see if they actually still retain the Exif data.  If not, the warning is correct.

If the Exif data cannot be found the date the create date of the file is used, which is probably the next best option.
Logged

macmiller

  • Contributor
  • Coppermine regular visitor
  • ***
  • Offline Offline
  • Posts: 80
Re: Exifier Sort Thumbnails based on the Exif date/time the picture was taken
« Reply #16 on: February 27, 2007, 02:01:29 am »

blue :  yes you would need to change the title on the pics, since now the title is just the date.  If you want the desc to be 'My Brother 11/20/2006 11:40AM', you need to make sure the title is 'My Brother' before you start.
Logged

swishi

  • Contributor
  • Coppermine novice
  • ***
  • Offline Offline
  • Posts: 22

Ther is a little mistake in ver 2.1 of the script, you forgott the "<?PHP" at the verry beginning of the script... could be confusing for new users!

Here is the corected version of 2.1

Code: [Select]
<?PHP

/***************************************************
  PHP Script to Sort Coppermine Thumbnails based on
  Exif DateTimeOriginal field and optionally title
  the pictures based on this same date.
  http://forum.coppermine-gallery.net/index.php?topic=39745.msg188747
  **************************************************
  v1.0 originally written by K Miller
  v1.2 3-Feb-07 Changed hard coded file references.
  v1.3 5-Feb-07 Corrected echo statements to have <br>.
  v2.0 6-Feb-07 Stripped out all of the existing code dealing with the exif.php and
  exif_php.inc.php.  These didn't work on several camera types and believe were coded
  before the php exif routines became available.  Now the Exif data is read from each
  image each time, the Exif data store is not accessed.
  script name = exifier2.php
  v2.1 26-Feb-07 Added a parameter li-append mode which allows date to be appended
  to user title.  Also added check prior to update sort row check to see if it is
  a change and only update changes.  Added more logging.
  script name = exifier2a.php
  ********************************************
  $Source$
  $Revision: 0003 $
  $Author: k miller $
  $Date: 2006-12-30 12:10:47 +0200
**********************************************/
/**********************************************
  The script has the main purpose of accessing the Exif data related to your pictures
  and sorting the thumbnails according to the date the picture was taken.
  Before starting, make sure that you have the coppermine config Thumbnail view option
  set to 'Position ascending'.  Otherwise the thumbnails will continue to sort as before.
  Even if you have another sort option selected, you can test the script was successful by
  invoking the POSITION + - sort on the thumbnail display page. 

  Earlier versions of this script relied on the existing coppermine Exif parsing, which proved
  problematic.  Now the Exif tags are retrieved via direct PHP function calls.

  Another usefull thing this this script allows you to do is to create picture titles based on
  the EXIF picture taken and also optionally append the model name.  The title is created
  according to a date variable you set up.

  To get started put this script into your coppermine default directory.  So if you access
  your photo album at www.joesblowssite.com/myprettypics you would run this script out of there
  as www.joeblowssite.com/myprettypics/exifier.php.

  Other than that, read and set up the run time variables in the VARIABLE SET UP SECTION.

  summary statistics are output at the end if everything goes well.

  If you use this script I would appreciate a forum note, let me know how it works for you.
  Likewise please let me know about any problems you encounter.  I've used it a lot and it
  really saves me a lot of time which before I was spending tweaking file mod dates and
  renaming JPG files with the date/time . 

  I have tested this script on three different camera models, Nokia N72, Canon SD500 and
  Sony DSC-T1 but the processing should be the same for any camera model, ie. I think it
  will work.
/**        VARIABLE SET UP SECTION                                                      **/
/** Set up how you want the script to operate here **/
// set up how you want the title generated
   $limaketitlefromdate = TRUE; //if set to TRUE creates titles based on Exif DateTimeOriginal
   $limaketitleonlyifblank = FALSE; //TRUE -> only process titles that are blank
   $limaketitleonlyifexifdate = FALSE; // Controls when titles should be generated.
//         If TRUE only valid EXIF dates will be used to generate titles.
//         If FALSE and there is not EXIF, the picture create date will be used.
// DON'T CHANGE THE DATE FORMAT FOR NOW
   $lititlestring = "'n/j/Y G:i'"; //Make pic title look like this (PHP Date format)
// removed liappendcameramodeltotitle and made an liappendmode
// 0 = don't append anything -- use the date only
// 1 = appendcameramodeltotitle as before
// 2 = appenddatetouserdesc (new)
   $liappendmode = 2;
   
/***************************************************************/
   define('IN_COPPERMINE', true);
   define('DISPLAYIMAGE_PHP', true);
   define('INDEX_PHP', true);
   require('include/init.inc.php');


// various literals
   $lidatelen = 19;
   $makeunique = -1;
   $exifdatearr = array();
   $stattitlefieldsupdated = 0;
   $statpicrecsprocessed = 0;
   $statrecordsupdatedforsort = 0;
   echo "starting coppermine Exif Sorter process ..... ",
        "<br>";
   
   $sql_command = "SELECT * FROM {$CONFIG['TABLE_PICTURES']}";
   $sql_result = cpg_db_query($sql_command);
   $sql_numrows = 0;
   $sql_numrows = mysql_num_rows($sql_result);
   if ($sql_numrows == 0) {
      die ("empty pictures table");
   }   
   while ($row = mysql_fetch_array($sql_result)) {
      ++$makeunique;
      ++$statpicrecsprocessed;
      $ecameramodel = "";
      $pfilepid = $row["pid"];
      $pfileaid = $row["aid"];
      $pfilepath = $row["filepath"];
      $pfilename = $row["filename"];
      $pfiletitle = $row["title"];
      $pfilectime = $row["ctime"];

// format variables to be used with this picture row
      $prettypid = makenbrpretty ($pfilepid, 8);
      $prettyaid = makenbrpretty ($pfileaid, 8);
      $prettyunique = makenbrpretty ($makeunique, 8);
      $exifdata = $prettypid . $pfilepath . $pfilename;

// make a date from the record create time
      $pcreatedate = date('Y:m:d H:i:s', $pfilectime);
      $goodexifdate = FALSE;
      $datesourcecreate = TRUE;
      $ekey = 'albums/' . $pfilepath . $pfilename;

      $exif = array();
      $exif = exif_read_data($ekey, 'ANY_TAG', TRUE, FALSE);
      if (($exif === FALSE) || (empty($exif)) || (empty($exif['EXIF']['DateTimeOriginal']))) {
         $exifkey = $prettyaid . $pcreatedate . $prettyunique;
         $exifdata = array($pfilepid, $pfilepath, $pfilename, $goodexifdate, $ecameramodel);
         $exifdatearr[$exifkey] = $exifdata;
          echo "warning: bad exif date for ",
               $pfilepath,
               $pfilename,
               "<br>";
      } else {
         $exifdate = $exif['EXIF']['DateTimeOriginal'];
         $goodexifdate = TRUE;
         $exifmodel = "";
         $exifmodel = $exif['IFD0']['Model'];
         $exifkey = $prettyaid . $exifdate . $prettyunique;
         $exifdata = array($pfilepid, $pfilepath, $pfilename, $goodexifdate, $exifmodel);
         $exifdatearr[$exifkey] = $exifdata;
      } // end if (($exif === FALSE).....
   } // end while main loop on pictures file
   $sortgood = ksort($exifdatearr, SORT_STRING);
   if (!$sortgood) {
      die ("error sorting exif data array");
   }
   $lastalbum = 0;
   $lastposition = 100;
   $firstrec = TRUE;
   foreach ($exifdatearr as $key => $val) {
      $salbum = substr($key,0,8);
      $sdatetime = substr($key,8,$lidatelen);
      $spid = $val[0];
      $sfilepath = $val[1];
      $sfilename = $val[2];
      $sgoodexifdate = $val[3];
      $smodel = $val[4];
      if ($firstrec) {
         $firstrec = FALSE;
         $lastalbum = $salbum;
      } else {
         if ($lastalbum != $salbum) {
            $lastalbum = $salbum;
            $lastposition = 100;
         }
      } // end if ($firstrec)
// prior to update read pictures table to see if update necessary
      $sql_command = "SELECT * FROM {$CONFIG['TABLE_PICTURES']} WHERE {$CONFIG['TABLE_PICTURES']}.pid = $spid";
      $sql_r1 = cpg_db_query($sql_command);
      $sql_n1 = mysql_num_rows($sql_r1);
      if ($sql_n1 != 1) {
         die("fatal data inconsistency detected, missing pictures record, pid-->" . $spid);
      }
      while ($r1 = mysql_fetch_array($sql_r1)) {
         $pthispos = $r1["position"];
      }
      if ($lastposition != $pthispos) {
         $sql_command = "UPDATE {$CONFIG['TABLE_PICTURES']} SET {$CONFIG['TABLE_PICTURES']}.position = '$lastposition' WHERE {$CONFIG['TABLE_PICTURES']}.pid = $spid";
         $sql_result = cpg_db_query($sql_command);
         ++$statrecordsupdatedforsort;
         echo "sorted ",
              $spid,
              " old pos = ",
              $pthispos,
              " new pos = ",
              $lastposition,
              "<br>";
      }
// if maketitlefromdate = false the routine will return false immeadiately
// if maketitleonlyifblank = the title is only altered if blank
// if maketitleonlyifexifdate = the create date won't be used in the title
// if appenddatetouserdesc = will append the date to the user desc
      $ruptitle = update_title($limaketitlefromdate,
                               $limaketitleonlyifblank,
                               $limaketitleonlyifexifdate,
                               $liappendmode,
                               $lititlestring,
                               $spid,
                               $sdatetime,
                               $sgoodexifdate,
                               $smodel);
      if ($ruptitle) {
         ++$stattitlefieldsupdated;
      }
      ++$lastposition;
   } // end foreach ($exifdatearr as $key => $val)
   echo "process completed successfully",
        "<br>",
        "picture rows processed -->",
        $statpicrecsprocessed,
        "<br>",
        "picture rows updated for sort position -->",
        $statrecordsupdatedforsort,
        "<br>",
        "Title fields updated -->",
        $stattitlefieldsupdated,
        "<br>";
       

function makenbrpretty ($inputstring, $outputlength) {
// Take the pass number and return it as
   $mpipstrlen = strlen($inputstring);
   $mpopstrlen = $outputlength;
   $mpstrzeros = "";
   for ($a = 0; $a <= $outputlength; $a++) {
      $mpstrzeros .= "0";
   }
   $nbrzerostoadd = $outputlength - $mpipstrlen;
   $leftzeros = substr($mpstrzeros, 1, $nbrzerostoadd);
   $mpret = $leftzeros . $inputstring;
   return ($mpret);
}

function update_title($tmaketitlefromdate,
                      $tmakeonlyifblank,
                      $tmaketitleonlyifexifdate,
                      $tappendmode,
                      $tlitdate,
                      $tpid,
                      $tdatetime,
                      $tgoodexifdate,
                      $tmodel) {

   global $CONFIG;

   $tfmttitlestr = "";
   if (!$tmaketitlefromdate) {
      return(FALSE);
   }
   if ($tmaketitleonlyifexifdate && !$tgoodexifdate) {
      return(FALSE);
   }
   $tfmttitlestr = "";
   $tdYYYY = substr($tdatetime,0,4);
   $tdMM = substr($tdatetime,5,2);
   $tdDD = substr($tdatetime,8,2);
   $ttHH = substr($tdatetime,11,2);
   $ttMM = substr($tdatetime,14,2);
   $ttSS = substr($tdatetime,17,2);
   $tfmtdatetime = date($tlitdate, mktime($ttHH, $ttMM, $ttSS, $tdMM, $tdDD, $tdYYYY));
   $tfmtdatetimep = str_replace("'", "", $tfmtdatetime);
   $datelen = strlen($tfmtdatetimep);
   $sql_command = "SELECT * FROM {$CONFIG['TABLE_PICTURES']} WHERE {$CONFIG['TABLE_PICTURES']}.pid = '$tpid' LIMIT 1";
   $sql_res = cpg_db_query($sql_command);
   $sql_numrs = mysql_num_rows($sql_res);
   if ($sql_numrs != 1) {
      die("fatal data inconsistency detected, missing pictures record, pid-->" . $tpid);
   }
   $r = mysql_fetch_array($sql_res);
   $tfilepid = $r["pid"];
   $tfiletitle = $r["title"];
   if ($tmakeonlyifblank) {
      $tfiletitlet = trim($tfiletitle);
      if ($tfiletitlet != "") {
         return (FALSE);
      }
   }
// appendmode = 0 = don't append anything, use the date only
// appendmode = 1 = make a final description from date + model
// appendmode = 2 = use the existing desc and append the date
// for appendmode = 2 must check to see if the existing desc is already a date
// if so we just leave it alone

// here will will take the last 16 characters of the current desc to see if
// if already contains the date/time
   $currdesccontainsdate = FALSE;
   $tfiletitlep = TRIM($tfiletitle);
   $desclen = strlen($tfiletitlep);
   $strs = $desclen - $datelen;
   if ($desclen < $datelen) {
      $tdateport = $tfiletitlep;
   } else {
      $tdateport = substr($tfiletitlep, $strs, $datelen);
   }
   if ($tdateport == $tfmtdatetimep) {
      $currdesccontainsdate = TRUE;
   }

   $tfiletitlep = TRIM($tfiletitle);
   switch ($tappendmode) {
   case 0: // in this mode we don't append anything
      $tfmttitlestr = $tfmtdatetimep;
      break;
   case 1: // append the model to the date (date + model)
      $tfmttitlestr = $tfmtdatetimep . " " . $tmodel;
      break;
   case 2: // append the date after the description (desc + date)
      $tfmttitlestr = $tfiletitlep;
      if (!$currdesccontainsdate) {
         $tfmttitlestr = $tfiletitlep . " " . $tfmtdatetimep;
      }
      break;
   }

   $tfmttitlestrp = TRIM($tfmttitlestr);
   if ($tfiletitlep == $tfmttitlestrp) {
      return(FALSE);
   }
   $sql_command = "UPDATE {$CONFIG['TABLE_PICTURES']} SET {$CONFIG['TABLE_PICTURES']}.title = '$tfmttitlestrp' WHERE {$CONFIG['TABLE_PICTURES']}.pid = $tpid";
   $sql_result = cpg_db_query($sql_command);
   return(TRUE);
} // end funtion
Logged

swishi

  • Contributor
  • Coppermine novice
  • ***
  • Offline Offline
  • Posts: 22
Re: Exifier Sort Thumbnails based on the Exif date/time the picture was taken
« Reply #18 on: October 13, 2007, 10:57:22 am »

No chance to get this script working, what am i doing wrong...?

I uploaded the script v. 2.1. to my coppermine (1.4.10) directory, uploaded the pictures and called the script via browser... www.mydomain.xxx/mycopperminedirecory/exifer.php

everytime i am running the script i am getting following critical error:
"There was a mistake while runing a database request"

Any suggestions?
Logged

macmiller

  • Contributor
  • Coppermine regular visitor
  • ***
  • Offline Offline
  • Posts: 80
Re: Exifier Sort Thumbnails based on the Exif date/time the picture was taken
« Reply #19 on: October 13, 2007, 04:31:41 pm »

One thing is the pictures need to be added to coppermine prior to running this script.  It then takes the existing coppermine data and re sorts it (and optionally does retitles the pics based on criteria you give it). 

Can you tell which line of the script the error is getting generated from?  What is the exact error message?
Logged
Pages: [1] 2 3   Go Up
 

Page created in 0.041 seconds with 19 queries.