There was a thread around about a CSV upload which was tied into the batch pic upload process, but I can't find it now. Anyway, what I needed was a stand alone batch upload and download process for the picture title, description and keywords -- so I provide these scripts which may be of interest to anyone wanting a batch update process to upload and download data info CSV format (which can be manipulated via Excel).
The upload process is a stand alone script: uploadcsvdata.php which looks at the file dataupload.csv, both placed in the default coppermine directory. Execution of the script causes the title, description and keywords to be updated on matching records (based on file location and file name).
The download process can be used to get a current snapshot of what data is out there now. The process is: downloadcsvdata.php and likewise is placed in the default directory. The file datadownload.csv is created and or overwritten with each execution of the script. The column layout is the same as for the upload process, so you can run it and then tweak the output and use it for updates with the upload process.
The only lines which should need to be changed in each script is the mysql_connect (with user name and password) and possibly the mysql_select_db (with database name).
upload script
<?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 = uploadscvdata.php
********************************************
$Source$
$Revision: 0001 $
$Author: k miller $
$Date: 2006-12-19 12:10:47 +0200
**********************************************/
/**********************************************
This file reads information from a CSV file you upload to
your default directory. The name of the file is 'dataupload.csv'.
The file contains 5 columns of data:
a.) filepath. This corresponds with the path to the file.
example: mypics/babymarina/zootripsandsuch/
b.) file name. This is the name of the jpeg image file that
you want to associate a title, comments and keywords with.
c.) title. The title to the pic.
d.) description. The desription.
e.) keywords
The program process through the input CSV file looking for
matching records to update. If the field (title, description,
or keywords) are blank they are ingored -- that is the pictures
table is not updated the original field is left intact. If you
wish to clear a field place 'clearme' in the field and the contents
will be spaced out. If the field (title, description, or keywords)
are the same as is in the file no update is made. Summary statistics
are output at the end if everything goes well.
..
***************************************************************/
echo "starting CSV coppermine upload script ..... ",
"<br>";
/** text file location and other static variables **/
$csvfile = 'dataupload.csv';
$fullyresolvedfilename = realpath($csvfile);
if (file_exists($csvfile) == FALSE) {
die ("convert file doesn't exist");
}
$newfile = fopen($csvfile, "r");
$conn = mysql_connect("localhost","xxxxx","xxxxx") or die(mysql_error());
$sel = mysql_select_db ("14_coppermine");
$textfilerowsprocessed = 0;
$sqlfilerowsprocessed = 0;
$sqlfilefieldsprocessed = 0;
$filedata = file($csvfile);
while (($filedataline = fgetcsv($newfile)) !== FALSE) {
++$textfilerowsprocessed;
$numfields = count($filedataline);
if ($numfields < 3) {
die ("bad csv data line " . $textfilerowsprocessed);
}
$jpegfileprefix = '';
$jpegfilename = '';
$pictitle = '';
$piccaption = '';
$pickeywords = '';
$deletepictitle = FALSE;
$deletepiccaption = FALSE;
$deletepickeywords = FALSE;
if ($numfields > 0) {
$jpegfileprefix = $filedataline[0];
}
if ($numfields > 1) {
$jpegfilename = $filedataline[1];
}
if ($numfields > 2) {
$pictitle = $filedataline[2];
}
if ($numfields > 3) {
$piccaption = $filedataline[3];
}
if ($numfields > 4) {
$pickeywords = $filedataline[4];
}
if ($pictitle == "clearme") {
$deletepictitle = TRUE;
}
if ($piccaption == "clearme") {
$deletepiccaption = TRUE;
}
if ($pickeywords == "clearme") {
$deletepickeywords = TRUE;
}
$xpictitle = mysql_real_escape_string($pictitle);
$xpiccaption = mysql_real_escape_string($piccaption);
$xpickeywords = mysql_real_escape_string($pickeywords);
$sql_command = "SELECT * FROM cpg1410_pictures WHERE filepath = '$jpegfileprefix' AND filename = '$jpegfilename' LIMIT 1";
$sql_result = mysql_query($sql_command,$conn) or die(mysql_error());
$sql_numrows = 0;
$sql_numrows = mysql_num_rows($sql_result);
if ($sql_numrows != 1) {
echo "warning: ",
$jpegfileprefix,
$jpegfilename,
" not found in pictures table",
"<br>";
break;
}
/** the while loop executes only once **/
$pfilepid = -1;
$pfilepath = '';
$pfilename = '';
$pfiletitle = '';
$pfilecaption = '';
$pfilekeywords = '';
while ($row = mysql_fetch_array($sql_result)) {
$pfilepid = $row["pid"];
$pfilepath = $row["filepath"];
$pfilename = $row["filename"];
$pfiletitle = $row["title"];
$pfilecaption = $row["caption"];
$pfilekeywords = $row["keywords"];
}
$xpfiletitle = htmlspecialchars_decode($pfiletitle,ENT_QUOTES);
$xpfilecaption = htmlspecialchars_decode($pfilecaption,ENT_QUOTES);
$xpfilekeywords = htmlspecialchars_decode($pfilekeywords,ENT_QUOTES);
$updatepic = FALSE;
$updatepictitle = FALSE;
$updatepiccaption = FALSE;
$updatepickeywords = FALSE;
if ($deletepictitle) {
if (trim($pfiletitle != '')) {
$updatepic = TRUE;
$updatepictitle = TRUE;
$xpictitle = '';
}
} else {
if (($pictitle != $pfiletitle) and (TRIM($pictitle) != '')) {
$updatepic = TRUE;
$updatepictitle = TRUE;
}
}
if ($deletepiccaption) {
if (trim($pfilecaption != '')) {
$updatepic = TRUE;
$updatepiccaption = TRUE;
$xpiccaption = '';
}
} else {
if (($piccaption != $pfilecaption) and (TRIM($piccaption) != '')) {
$updatepic = TRUE;
$updatepiccaption = TRUE;
}
}
if ($deletepickeywords) {
if (trim($pfilekeywords != '')) {
$updatepic = TRUE;
$updatepickeywords = TRUE;
$xpickeywords = '';
}
} else {
if (($pickeywords != $pfilekeywords) and (TRIM($pickeywords) != '')) {
$updatepic = TRUE;
$updatepickeywords = TRUE;
}
}
if ($updatepic) {
++$sqlfilerowsprocessed;
$fieldsupdated = 0;
if ($updatepictitle) {
$sql_command = "UPDATE cpg1410_pictures SET cpg1410_pictures.title = '$xpictitle' WHERE cpg1410_pictures.pid = $pfilepid";
$sql_result = mysql_query($sql_command,$conn) or die(mysql_error());
++$fieldsupdated;
++$sqlfilefieldsprocessed;
}
if ($updatepiccaption) {
$sql_command = "UPDATE cpg1410_pictures SET cpg1410_pictures.caption = '$xpiccaption' WHERE cpg1410_pictures.pid = $pfilepid";
$sql_result = mysql_query($sql_command,$conn) or die(mysql_error());
++$fieldsupdated;
++$sqlfilefieldsprocessed;
}
if ($updatepickeywords) {
$sql_command = "UPDATE cpg1410_pictures SET cpg1410_pictures.keywords = '$xpickeywords' WHERE cpg1410_pictures.pid = $pfilepid";
$sql_result = mysql_query($sql_command,$conn) or die(mysql_error());
++$fieldsupdated;
++$sqlfilefieldsprocessed;
}
if ($fieldsupdated = 0) {
echo "csv file row not processed ",
$jpegfileprefix,
$jpegfilename;
}
}
}
echo "script completed processing from filename-->",
$fullyresolvedfilename,
"<br>";
echo $textfilerowsprocessed,
" text file rows processed ",
$sqlfilerowsprocessed,
" sql file rows processed ",
$sqlfilefieldsprocessed,
" sql file fields processed",
"<br>";
?>
download script
<?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 = downloadscvdata.php
********************************************
$Source$
$Revision: 0001 $
$Author: k miller $
$Date: 2006-12-19 12:10:47 +0200
**********************************************/
/**********************************************
This script works in conjuntion with script uploadcsvdata.
This sript extracts data columns from the existing picture
table and formats them in the following columns.
a.) filepath. This corresponds with the path to the file.
example: mypics/babymarina/zootripsandsuch/
b.) file name. This is the name of the jpeg image file.
c.) title. The title to the pic.
d.) description. The desription.
e.) keywords.
summary statistics are output at the end if everything goes well.
***************************************************************/
echo "starting CSV coppermine download script ..... ",
"<br>";
/** text file location and other static variables **/
$csvfile = 'datadownload.csv';
$newfile = fopen($csvfile, "w");
if (file_exists($csvfile) == FALSE) {
die ("download file not created");
}
$fullyresolvedfilename = realpath($csvfile);
$conn = mysql_connect("localhost","xxxxx","xxxxx") or die(mysql_error());
$sel = mysql_select_db ("14_coppermine");
$textfilerowsprocessed = 0;
$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 table");
}
$textfilerowscreated = 0;
while ($row = mysql_fetch_array($sql_result)) {
$pfilepid = $row["pid"];
$pfilepath = $row["filepath"];
$pfilename = $row["filename"];
$pfiletitle = $row["title"];
$pfilecaption = $row["caption"];
$pfilekeywords = $row["keywords"];
$xpfiletitle = htmlspecialchars_decode($pfiletitle,ENT_QUOTES);
$xpfilecaption = htmlspecialchars_decode($pfilecaption,ENT_QUOTES);
$xpfilekeywords = htmlspecialchars_decode($pfilekeywords,ENT_QUOTES);
$csvdataline = array ($pfilepath, $pfilename, $xpfiletitle, $xpfilecaption, $xpfilekeywords);
fputcsv($newfile, $csvdataline);
++$textfilerowscreated;
}
echo "script completed processing extract to filename-->",
$fullyresolvedfilename,
"<br>";
echo $textfilerowscreated,
" text file rows processed ",
"<br>";
?>