<?php
/*
  dbfix.php
    A function used to do a global string replacement in a
    specific database table/column.

    $aReplacements = array("search"=>"home.dri.edu", "replace="=>"oldintranet.dri.edu");
    Sample usage: fnDB_Fix($hDB, "hr_menu_entries", $aReplacements);

  Written: 10/17/2011, Nicholas Kreidberg
  Revised: 10/17/2011, Nicholas Kreidberg

*/

// Connect to db
$hDB = pg_connect("host=$db_host port=$db_port dbname=$db_name user=$db_user password=$db_pass")
	    or die("Failed to connect to database.".pg_last_error());

function fnDB_Fix($hDB, $sTable, $sField, $aReplacements)
{
  if(pg_connection_status($hDB) != PGSQL_CONNECTION_OK) {
    die("Invalid database handle passed to function.\n");
  }

  if(empty($sTable)) {
    die("Missing database table parameter in call to function.\n");
  }

  if(empty($aReplacements) || !is_array($aReplacements)) {
    die("Missing or invalid replacements parameter in call to function.\n");
  }

  $rRes = pg_query($hDB, "SELECT * FROM $sTable");
  $aRes = pg_fetch_all($rRes);

  foreach($aRes as $aRec)
  {
    echo "Orig: ".$aRec['url']."\n";
    $aRec['url'] = str_replace($aReplacements['search'], $aReplacements['replace'], $aRec['url']);
    echo "Fixed: ".$aRec['url']."\n";
    $sSQL = sprintf("UPDATE %s SET %s ='%s' WHERE srecn='%s'",
      $sTable, $sField, $aRec['url'], $aRec['srecn']);
    echo "SQL: ".$sSQL."\n";
    pg_query($hDB, $sSQL);
  }
}

// Search/replace array
$aReplacements = array("search"=>"home.dri.edu", "replace"=>"oldintranet.dri.edu");

// Sample function call
fnDB_Fix($hDB, "hr_menu_entries", "url", $aReplacements);


// Close database connection
pg_close($hDB);
?>