forum.coppermine-gallery.net
No Support => Modifications/Add-Ons/Hacks => Mods: Visuals => Topic started by: macmiller on December 30, 2006, 12:55:14 pm
-
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
-
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!
-
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
-
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.
-
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
-
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?
$conn = mysql_connect("localhost","xxxxx","xxxxx") or die(mysql_error());
If so, can you check and see what the PHP error being produced is?
-
You also need to change all occurances of cpg1410_pictures to whatever your pictures file is called....
-
Sorry, should be all references to cpg1410_ to the prefix that your database has...
-
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
<?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
-
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.
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.
<?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
-
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/
-
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.
-
Awesome! I patiently await your changes! ;D
-
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......
/***************************************************
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
-
Hmm.. I ran it as is and got this error message....
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 ???
-
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.
-
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.
-
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
<?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
-
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?
-
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?
-
Hi macmiller and thanksa for your answer...
What exactly do you mean by "need to be added to coppermine prior to running this script"? I uploaded my pics using the xp publisher and they are online...
The error message I am getting does not give any line number... it seams to be a coppermine errormessage:
In German:
Kritischer Fehler
Beim Ausführen einer Datenbank-Abfrage ist ein Fehler aufgetreten
What means in english:
Critical Error
There was a error while runing a database request
thanks for your help!
-
Enable debug_mode to see what query actually fails. Post the error message here (not the debug_output). Applies to all critical error messages.
-
Hi GauGau
and thanks for your involvement in this matter!
Here you have the requested error:
--------------------
While executing query "UPDATE cpg11d_pictures SET cpg11d_pictures.title = 'Freunde für's Leben.... 8/17/2003 8:45' WHERE cpg11d_pictures.pid = 689" on 0
mySQL error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 's Leben.... 8/17/2003 8:45' WHERE cpg11d_pictures.pid = 689' at
--------------------
further more I am getting several errors like this:
Notices
/exifer.php
Warning line 123: exif_read_data(P5291697.JPG): corrupt EXIF header: maximum directory nesting level reached
-
Seems like the function is not properly escaping the string (the single quote broke the query). This may be a potential bug. Please provide a link to your gallery and let us know if you modified the corresponding code.
-
Yes I agree with GauGau
It looks like the SQL statement isn't escaped properly to handle the interim single quote character in the title. Right now it is:
Freunde für's Leben.... 8/17/2003 8:45
Please remove all intermediate single quotes if not too much trouble and try another run to confirm the problem. After this if you fix the code please post it or I can fix and post later.
-
Hi Guys... Thanks for your replys... I'll try your suggestions and I'lll come back with the outcome within the next days!
Thanks so long
-
I tried the single quote scenario and put a output display in there. Note the UPDATE statement below. This works fine, so I still am unsure of what the problem was.
starting coppermine Exif Sorter process .....
UPDATE cpg1410_pictures SET cpg1410_pictures.title = 'my test's 7/4/2006 19:02' WHERE cpg1410_pictures.pid = 236
process completed successfully
picture rows processed -->319
picture rows updated for sort position -->0
Title fields updated -->1
-
Here I am with the outcom...
As per your suggestion I renamed the File "Freunde für's Leben" to Freunde ein lebenlang and run exifer again... Now I am getting follwowing Errors:
While executing query "UPDATE cpg11d_pictures SET cpg11d_pictures.title = 'Biker's Lust... wenn da keine Lust aufkommt...? 8/18/2003 1:06' WHERE cpg11d_pictures.pid = 741" on 0
mySQL error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 's Lust... wenn da keine Lust aufkommt...? 8/18/2003 1:06' WHERE
Datei: /home/www/web428/html/coppermine/include/functions.inc.php - Zeile: 249
Notices
/exifer.php
Warning line 123: exif_read_data(P5291697.JPG): corrupt EXIF header: maximum directory nesting level reached
Warning line 123: exif_read_data(P5291698.JPG): corrupt EXIF header: maximum directory nesting level reached
Warning line 123: exif_read_data(P5291699.JPG): corrupt EXIF header: maximum directory nesting level reached
Warning line 123: exif_read_data(P5291700.JPG): corrupt EXIF header: maximum directory nesting level reached
Warning line 123: exif_read_data(P5291703.JPG): corrupt EXIF header: maximum directory nesting level reached
So I renamed the Album "Biker's Lust... wenn da keine Lust aufkommt...?" to "Bikers Lust" ...Now if I am running exifer I am still getting above mentioned Errors even if The Album "Biker's Lust... wenn da keine Lust aufkommt...?" does not exist anymore...?
-
This is quite strange you are getting the error. I named one of my pictures with the same title as yours to test it and output the SQL sytax as you see below.
starting coppermine Exif Sorter process .....
UPDATE cpg1410_pictures SET cpg1410_pictures.title = 'Biker's Lust... wenn da keine Lust aufkommt...? 7/4/2006 19:02' WHERE cpg1410_pictures.pid = 236
process completed successfully
picture rows processed -->319
picture rows updated for sort position -->0
Title fields updated -->1
It worked fine.
I believe it may be a different php version or mysql implementation perhaps? In other programs I was using the call mysql_query, in this one I am using cpg_db_query, which is standard for coppermine programs. If I add a mysql_real_escape_string to escape the string before the call, then I get an error:
The syntax of the escaped SQL call looks like this:
UPDATE cpg1410_pictures SET cpg1410_pictures.title = \'Biker's Lust... wenn da keine Lust aufkommt...? 7/4/2006 19:02\' WHERE cpg1410_pictures.pid = 236
All this to say I really don't know what your problem is -- I would pursue it further if I could recreate the problem here.
As far as the other points, remember that the title Biker's Lust... wenn da keine Lust aufkommt...? is the title to a picture. The title is found in the coppermine database. If you are going to tweak it, you should do it within the coppermine interface.
-
Hi,
I just installed the script and it works great, thank you. I have a small problem though - I think it is possible to to run the script automatically just after batch adding of new pictures has finished, so I don't have to run it from the browser manually, however since I'm not a programer, I have no idea how to do it. I would be gratefull for the particular piece of code. My guess is, it should go somewhere to searchnew.php file...
-
I just wanted to drop in and mention that this works GREAT. I am using this with 1.4.18 and it works without a hitch :)
An excellent solution for those of you looking to sort your photos by EXIF data.
Thank you!!
-
Thanks for spending the time and effort on this...
is there anyway to make the dates UK format as in DD/MM/YY HH:MM
-
Hi,
thank you very much for this mod, I had exactly the same troubles tweaking dates, names etc. I've been using this for a while and if works really well. However, I thought it might be usefull to run the script only for certain album, if possible. For example the album the pictures were just added into. The script as it is now runs on the the whole gallery, which is more and more time consuming. Do you think it is possible?
-
I've run this script as is on my gallery and it worked brilliantly, many thanks! I now have original titles followed by the date and time but really would like the date format to be the UK format ie DD/MM/YY HH:MM. Can anyone point me in the right direction to achieve this so that I won't lose the original title or end up with two date formats appended?
-
There should be a variable at the top entitled $lititlestring.
Change it to:
$lititlestring = "'d/m/y G:i'";
-
Thanks for your prompt reply. I did this but now I have both date formats in the title eg
Male Southern Hawker 8/14/2011 9:01 14/08/11 9:01
Is there anyway of changing this without going through and manually deleting the dates!
-
Yes, I can see the problem you are having. The script looks to see if the date is already a part of the title so it doesn't add it twice, but since the format has been changed it thinks the date is not there. Unfortunately at this point the easiest was to fix the existing records would be to either manually modify the description in each one or write a simple php script to take out the extra date. It will work on new records that are added.
-
Manually deleted the dates and re-ran the script which worked a treat. Just as well I didn't have too many to do! Thank you for a great script which will certainly be a time saver for future uploads.
-
Just in case anyone else is still interested in this script I am posting the most current version that I am using now. It works on CPG 1.5.x. Mostly the changes involved dealing with positioning, the previous absolute positioning didn't work well. The positioning is only used if you want some of your pics sorted by EXIF date and others you want to specify the position manually. TO do this you indicate which ones you want to position manually using the lipos variable. Other changes involve correctly escaping the strings prior to sql updates and directly accessing the db rather than using the CPG calls. There are a lot of echo statements which can be ignored/removed. To start set up the lines 130/131 for database access.
To use this script you would need to change all of the cpg1410_ prefixes to match your database. Also this version of the script uses this table:
CREATE TABLE IF NOT EXISTS `cpg1410_mytempdata` (
`album` int(11) NOT NULL DEFAULT '0',
`pid` int(11) NOT NULL DEFAULT '0',
`orig_position` int(11) NOT NULL DEFAULT '0',
`orig_ordinal` int(11) NOT NULL DEFAULT '0',
`rec_position` int(11) NOT NULL DEFAULT '0',
`rec_ordinal` int(11) NOT NULL DEFAULT '0',
`final_position` int(11) NOT NULL DEFAULT '0',
`final_ordinal` int(11) NOT NULL DEFAULT '0',
`maxposforalbum` int(11) NOT NULL DEFAULT '0',
`maxordinal` int(11) NOT NULL DEFAULT '0',
`albumandposition` varchar(16) NOT NULL DEFAULT '',
`exifdatearrkey` varchar(35) NOT NULL DEFAULT '',
`filepath` varchar(20) NOT NULL DEFAULT '',
`filename` varchar(20) NOT NULL DEFAULT '',
`goodexifdate` char(1) NOT NULL DEFAULT '',
`cameramodel` varchar(20) NOT NULL DEFAULT '',
UNIQUE KEY `exifdatearrkey` (`exifdatearrkey`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
-
heya,
does one need to set up the temp table manually? I get the following error:
v10011a
starting coppermine Exif Sorter process .....
this run only process album 33
Table 'a_coppermine.cpg15x_mytempdata' doesn't exist
Or is it a case of my database reference being incorrect?
Cheers!
-
Apologies - please delete post, I missed the last instructions from Macmiller.
-
Is the script meant run properly with linked files? I have found that in my gallery, liked images are sorted incorrectly; the only way of remedying the situation appears to upload copies of the files in the relevant album.
Any thoughts / good ideas?