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
<?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