cartoons2csv.php

<?php

/**
 * Script to export entries from the SFU Editorial Cartoons database
 * for importing into Islandora.
 */

$db['user'] = '****';
$db['password'] = '****';
$db['name'] = 'Cartoons';

$outfile = 'cartoons.csv';

// Define our base SQL.
$cartoons_sql = "SELECT CartoonID, CartoonKey, Cartoonists.Name, DateOnCartoon,
  CartoonText, PhysicalDescription, PublicationInfo, DisplayNotes, MsCCode, Checksum 
  FROM Cartoons, Cartoonists
  WHERE Cartoonists.CartoonistID = Cartoons.CartoonistID 
  AND ShowOnPublicPage = 'Y'";

// Write a header row to the TSV file.
$fp = fopen($outfile, 'w');

// Write the header row.
fwrite($fp, "CartoonID,CartoonKey,Cartoonist,Date,CartoonText,PhysicalDescription,PublicationInfo,DisplayNotes,MsCCode,MD5Checksum,Subjects,Subjects_TGM1,Subjects_LCSH,File\n");

$dbh = new PDO('mysql:host=localhost;dbname=' . $db['name'], $db['user'], $db['password']);
$cartoons_sth = $dbh->prepare($cartoons_sql);
$cartoons_sth->execute();
$cartoons = $cartoons_sth->fetchAll(PDO::FETCH_ASSOC);
foreach ($cartoons as &$row) {
  foreach ($row as $name => &$value) {
    $value = trim($value);
  }

  // Clean up specific values.
  $row['CartoonText'] = preg_replace('/\x97/', ' -- ', $row['CartoonText']);
  $row['CartoonText'] = preg_replace('/\x92/', "'", $row['CartoonText']);
  $row['CartoonText'] = preg_replace('/\x93/', '"', $row['CartoonText']);
  $row['CartoonText'] = preg_replace('/\x94/', '"', $row['CartoonText']);
  $row['CartoonText'] = preg_replace('/\r\n/', PHP_EOL, $row['CartoonText']);
  $row['DisplayNotes'] = preg_replace('/\r\n/', PHP_EOL, $row['DisplayNotes']);
  $row['DisplayNotes'] = trim(strip_tags($row['DisplayNotes']));
  if (!preg_match('/^19|^20/', $row['DateOnCartoon'])) {
    $row['DateOnCartoon'] = '';
  }

  // Get the subjects associated with each cartoon that do not have 'TGM 1' or
  // 'LCSH' as Source values.
  $subjects_sth = $dbh->prepare("SELECT Subject FROM Subjects, CartoonsPlusSubjects WHERE
  	CartoonsPlusSubjects.SubjectID = Subjects.SubjectID
    AND Subjects.Source != 'TGM 1'	
    AND Subjects.Source != 'LCSH'	
    AND CartoonsPlusSubjects.CartoonID = :cartoonid");
  $subjects_sth->bindParam(':cartoonid', $row['CartoonID'], PDO::PARAM_INT);
  $subjects_sth->execute();
  $subjects = $subjects_sth->fetchAll(PDO::FETCH_COLUMN);
  // Add the result to the Subjects column in $row.
  $subject_string = implode(';', $subjects);
  $row['Subjects'] = $subject_string;

  // Get the subjects associated with each cartoon that have a Source value of
  // 'TGM 1'.
  $subjects_tgm1_sth = $dbh->prepare("SELECT Subject FROM Subjects, CartoonsPlusSubjects WHERE
  	CartoonsPlusSubjects.SubjectID = Subjects.SubjectID
    AND Subjects.Source = 'TGM 1'	
    AND CartoonsPlusSubjects.CartoonID = :cartoonid");
  $subjects_tgm1_sth->bindParam(':cartoonid', $row['CartoonID'], PDO::PARAM_INT);
  $subjects_tgm1_sth->execute();
  $tgm1_subjects = $subjects_tgm1_sth->fetchAll(PDO::FETCH_COLUMN);
  // Add the result to the Subjects_TGM1 column in $row.
  $tgm1_subject_string = implode(';', $tgm1_subjects);
  $row['Subjects_TGM1'] = $tgm1_subject_string;

  // Get the subjects associated with each cartoon that have a Source value of
  // 'LCSH'.
  $subjects_lcsh_sth = $dbh->prepare("SELECT Subject FROM Subjects, CartoonsPlusSubjects WHERE
  	CartoonsPlusSubjects.SubjectID = Subjects.SubjectID
    AND Subjects.Source = 'LCSH'	
    AND CartoonsPlusSubjects.CartoonID = :cartoonid");
  $subjects_lcsh_sth->bindParam(':cartoonid', $row['CartoonID'], PDO::PARAM_INT);
  $subjects_lcsh_sth->execute();
  $lcsh_subjects = $subjects_lcsh_sth->fetchAll(PDO::FETCH_COLUMN);
  // Add the result to the Subjects_LCSH column in $row.
  $lcsh_subject_string = implode(';', $lcsh_subjects);
  $row['Subjects_LCSH'] = $lcsh_subject_string;

  $row['File'] = $row['CartoonKey'] . '.tif';

  fputcsv($fp, $row);
}

$dbh = null;
fclose($fp);
?>