#!/usr/local/bin/php
<?PHP
/*

THIS SOFTWARE IS FREE AND COMES WITH NO GUARANTEE OR WARRANTY.
BY USING THIS SOFTWARE, YOU AGREE TO RELEASE THE AUTHOR OF ALL LIABILITY ARISING FROM ITS USE OR MISUSE.
USE AT YOUR OWN RISK

Safety Guidelines:
- Always back up your database before running this - or any other - script.
- Always test run this script first by setting "ReportOnly" to true.

Purpose
This script is used to search-and-replace 1 or more fields of a database.
It is especially designed for UBB Threads but can be used with other databases as well.

Typical Usage
This is a PHP CLI script. To use it:
1. Make sure PHP CLI is installed on your system.
2. Update all necessary configuration variables below.
3. Run this script from the command line:
	./update-db

Notes
If you receive one or more "depreciated" messages when running this script, you can normally safely ignore them.

Author
Russ Tanner
Please report bugs to russ@russtanner.com

From the Author
I wrote this for our own use but decided to share it because others may find it useful.
If you like it, feel free to drop me a line and let me know.
You may also visit:
	GlobalSkywatch.com
	OrbisVitae.com

Version History
1.0 2012-03-06 (March 06) - Tested on PHP 5.3.10.

*/



// Configuration //



// Database Credentials

// The name of the server this script is running on.
// If the database is on the same server, you will normally use "localhost".
$DBServer = 'localhost';

// The username and password used to log into the database you want to use.
$DBUsername = 'MyUsername';
$DBPassword = 'MyPassword';

// The name of the database you want to work with.
$DBDatabase = 'MyDatabase';



// Text Replacement Configuration //

// The name of the database table we will search-and-replace in.
// It's important that you set this correctly because you specified a table prefix when you installed UBB Threads.
$TableName = 'ubbt_POSTS';

// A list of fields we will search-and-replace in.
// This can contain 1 or more field names.
// Normally, if you want to update all the posts in UBB Threads, you will use POST_BODY and POST_DEFAULT_BODY.
$FieldNames = array('POST_BODY', 'POST_DEFAULT_BODY');

// This is the search and replace text.
// Every field listed in "FieldNames" in the table "TableName" will be scanned for "SearchText".
// If found, the search text will be replaced with "ReplaceText".
// If you want to delete the search text, make "ReplaceText" zero-length ("").
$SearchText = 'replace this';
$ReplaceText = 'with this';

// Report only. Don't update the database.
// If this is true, then the database is not updated:
// 		- Prospective changes are still logged to the log file (if enabled), and 
//		- tallies are still reported at the end.
// If this is false, then the database will be updated.
// This is useful for making a test run of this script to see how many records will be affected.
$ReportOnly = true;



// Post ID Range To Process //

// The first and last Post ID that we will process.
// This provides a limit so we can shorten runtimes.
// If PostID_Stop is too high, this will extend the run time even if those post IDs don't exist. Please set wisely.
// PostID_Stop must be higher than PostID_Start.
$PostID_Start = 1;
$PostID_Stop = 10000;

// This is the name of the field that holds the ID for each record.
// For most versions of UBB Threads, this should be set to "POST_ID".
// By having this configurable, this script becomes much more useful for applications other than UBB Threads.
$PostID = 'POST_ID';



// Logging //

// The filespec of the file we will log to.
// This log only records the post ID and text of records that have been changed.
// The text before AND after text replacement is logged.
// If this is just a file name, then the file will appear in the current directory.
// If the file exists, it will be appended (logging will be added to the end).
// This can also be an absolute path.
// To disable logging, leave this blank.
$LogFile = 'update.log';



// End Configuration //

// DO NOT CHANGE ANYTHING BELOW THIS LINE UNLESS YOU KNOW WHAT YOU ARE DOING!
// ==========================================================================



// PHP Settings //

// Use utf-8 for all pages
//ini_set('default_charset', cfg::$CharSet);

// Don't display sensitive information on the screen
ini_set('display_errors', 0);
ini_set('log_errors', 1);




// Functions //


function BuildClause($hDB, $AA, $Relation = ' = ', $Conjunction = ' AND ', $ValueDelim = "'") {
	/*
	Purpose
		Builds a clause from an associative array.
	Parameters
		hDB (Resource) = Handle to the database connection we're working with.
		AA (Associative Array) = The array containing the data to build the clause with.
			The clause is costructed in the following way:
				Using this array: $Person['FirstName'] = 'Russ',$Person['LastName'] = 'Tanner'
				This clause is returned: "FirstName< relation >'Russ'< conjunction >LastName< relation >'Tanner'"
			Any special characters in the string are automatically quoted.
		Relation (Optional String) = This is the string that will be between each array element name and its value.
			This is a MySQL operator like: = < > Like
			Default: " = "
		Conjunction (Optional String) = The is the string that will be between each name/value pair.
			This is a MySQL operator like: AND OR
			Default: " AND "
		ValueDelim (Optional String) = The character that will serve as the delimiter for the value of the name/value pair.
			Default: "'"
	Return (String)
		Returns a complete clause as described above.
	Entry Conditions
		The database must be open.
	Version History
		1.0 12/24/2003 Tested by Russ Tanner
		1.1	6/20/2008 1:20am Update by Russ Tanner
			Made the following parameters optional: Relation, Conjunction, ValueDelim.
			Made all AA keys escaped for better injection protection.
			Also, cleaned up the code a bit.
	*/

	// Initaite variables
	$r = ''; // The return buffer
	
	// Loop through each element in the specified array
	foreach ($AA as $k => $v) {

		// Append the relation if this isn't the first iteration	
		if ($r != '') $r .= $Conjunction; 
	
		// Append the next field/value pair	
		$r .= mysql_real_escape_string($k) . $Relation . 
			$ValueDelim . mysql_real_escape_string($v, $hDB) . $ValueDelim; 
	
	}
	
	// Return the result
	return $r;

}


function BuildClause_SET($hDB, $AA, $Keyword = true) {
	/*
	Purpose
		Builds a SQL "SET" clause from an associative array.
	Parameters
		hDB (Resource) = Handle to the database connection we're working with.
		AA (AA) = The array containing the data to build the clause with.
			Key names are the actual field names we want to use.
			Values contain the actual data we want to use.
			Example:
				This array: $Person['FirstName'] = 'Russ',$Person['LastName'] = 'Tanner'
				will build the following SET clause: "FirstName = 'Russ',LastName = 'Tanner'"
		Keyword (Optional Boolean) = If true, the SET keyword is included with the returned phrase.
			If false, the SET keyword is not returned.
			If the resulting clause is zero-length, the SET keyword is never returned regardless of
				the value of this parameter.
			Default: true
	Return (String)
		Returns a SQL SET clause as described above.
	Entry Conditions
		The database must be open.
	Version History
		1.0 12/24/2003 Tested by Russ Tanner
		1.1 7/21/2008 Added Keyword parameter.
	*/

	$Clause = BuildClause($hDB, $AA, ' = ', ',', "'");
	if (strlen($Clause) > 0 && $Keyword) $Clause = "SET $Clause";
	return $Clause;

}




// Main //

print "Begining search-and-replace on database $DBDatabase.";					// Opening message.

print "\n\nOpening database...";												// Say status.

// Connect to the database.
$hDB = mysql_connect($DBServer, $DBUsername, $DBPassword);
// Error Trap
if ($hDB === false) exit('Error connecting to database: ' . mysql_error($hDB));

// Open the database.
$e = mysql_select_db($DBDatabase, $hDB);
// Error Trap
if (! $e) exit("\nError opening to database:\n" . mysql_error($hDB));

print ' OK';																	// Say status.


// Prepare for main loop.

// Create a comma-separated list of fields we will operate on.
// We pre-build this because it's used continously in the SQL statement in the main loop.
$FieldNamesSQL = implode(',', $FieldNames);

// Track the total number of records processed and the total number of records changed.
$RecsChangedCount = 0;
$RecsProcessedCount = 0;


// Main Loop: Iterate all posts in the range specified by PostID_Start and PostID_Stop.
for ($curPostID = $PostID_Start; $curPostID <= $PostID_Stop; $curPostID++) {

	print "\n\nPost ID: $curPostID";											// Say status.

	//// We are configured to log.
	//if (strlen($LogFile) > 0) {
	//	file_put_contents($LogFile, "\n\nPost ID: $curPostID", FILE_APPEND);
	//}

	// Flag to indicate if the current record was changed by the search-and-replace.
	// This is used to determine if the database will be written back to the database or not.
	// We're processing a new record so reset this flag now.
	// This variable is an optimization reducing the number of database UPDATEs.
	$RecChanged = false;

	// Query for the record containing the current post ID.
	print "    Querying Database";
	$SQL = "SELECT $FieldNamesSQL FROM $TableName WHERE $PostID = $curPostID";
	$q = mysql_query($SQL, $hDB);
	// Error Trap: Query error. Display error and exit now.
	if (! $q) exit("\nError querying database:\n" . mysql_error($hDB));

	
	// A row was returned in the query.
	// Update the row with the text after it's been searched and replaced.
	if (mysql_num_rows($q)) {

		print "    Replacing Text";												// Say status.

		// Fetch the data for the matching row.
		$row = mysql_fetch_assoc($q);

		// Search and replace target field text.
		foreach ($FieldNames as $curField) {
			// Search-and-replace the current field in the buffer.
			$x = str_replace($SearchText, $ReplaceText, $row[$curField]);
			// Search-and-replace has changed the text.
			if ($x != $row[$curField]) {
				// We are configured to log. Make a log entry.
				if (strlen($LogFile) > 0) {
					file_put_contents($LogFile, "\n\n\n\nPost ID: $curPostID\n\n --- $curField Before Replace ---\n" . $row[$curField] . "\n\n--- $curField After Replace ---\n$x", FILE_APPEND);
				}
				// Save the updated field value back to the row buffer.
				$row[$curField] = $x;
				// Set the "record has changed" flag. This will cause us to update the table.
				$RecChanged = true;
			}
		}

		// The search-and-replace operation has changed a field.
		// Update the current record.
		if ($RecChanged) {
			print "    Updating Database";											// Say status.
			$SETClause = BuildClause_SET($hDB, $row);								// Build the SQL SET clause.
			$SQL = "UPDATE $TableName $SETClause WHERE $PostID = '$curPostID'";		// Build the SQL statement.
			//print "\nSQL: $SQL";													// Debugging only.
			//sleep(10);
			// Only update the database if we're NOT in ReportOnly mode.
			if (! $ReportOnly) {
				$q = mysql_query($SQL, $hDB);
				// Error Trap: Query error. Display error and exit now.
				if (! $q) exit("\nError querying database:\n" . mysql_error($hDB));
			}
			// Increment the total number of records that were changed.
			$RecsChangedCount++;
		}
		// The search-and-replace did not change a field.
		// No need to update the current record.
		else {
			print "   Post Unchanged";
		}

		// Increment the total number of records we've processed.
		$RecsProcessedCount++;

	}

	// No row was returned in the query.
	else {
		print "    This post ID does not exist.";
	}



}

	// Say stats.
	print "\n\n" . ($PostID_Stop - $PostID_Start + 1) . " posts scanned.";
	print "\n$RecsProcessedCount posts found and processed.";
	print "\n$RecsChangedCount posts changed.";

	print "\n\nComplete.\n";

?>