Tag Archives: trim

PHP: How to Remove or Replace Strings from the Front and End of a Value CodeUnit 02 JUL 2010

Today’s little PHP hint is a pretty simple one, but one worth tackling if you are looking for a quick and easy way to strip a particular string pattern off the front and end of a value.

What we will make use of is of course PHP’s built in preg_replace functionality, basically one of the wrapper functions that brings regular expression manipulation to PHP.

Now to start off, we need to specify the string pattern we wish to search for off the front of our value. To do this we make use of the ^ regex control character that denotes the search to begin from the start of a string. To locate a string pattern off the end of our value, we make use of the $ regex control character which likewise, tells the search to commence at the back of the given string value.

We then place our two patterns in a search array, before moving on to define our replace array. Now at this stage you can choose: if you want to simply strip these patterns off the value, then simply match the search patterns with the same amount of blank string values, otherwise insert the string values you wish to replace the patterns with.

Finally, we call the preg_replace function, feeding it our search and replace arrays, as well as the input value it is to work on.

In other words, we would have something like this:

$search = array ( "/^stringatstart /","/ stringatend$/");
$replace = array ('','');

echo preg_replace($search, $replace, "stringatstart Hello World! stringatend");

And there you go, a nice and simple way to strip off specific values at either end of a string! :)

PHP Script to Trim and Replace Apostrophes for Each Value in Each Column of Each Table in a MySQL Database CodeUnit 31 MAY 2010

Annoyingly, I got saddled with a database for a system which contained a whole lot of “uncleaned data”, in other words data with a lot of trailing and leading, uncessary whitespace characters, as well as a generous helping of apostrophes and quotation marks – the cause of many a headache when working on a web-based application using PHP and MySQL.

Needless to say, inherent relationships and thus joins were failing left, right and center thanks to this badly formed data and as such I needed to quickly whip up a script that would strip all leading and trailing whitespace plus replace any apostrophes or quotation marks with their web code equivalent for each and every value that appeared in every row, column and table in the database.

And this is the little PHP script that did it for me:

//just to help prevent the script from timing out
ini_set('memory_limit', '100M');
ini_set('default_socket_timeout',600);
ini_set('max_input_time',600);
ini_set('max_execution_time',600);

//give us something to look at on the screen
echo "Start Process...";

//create a database connection
$mysql_hostname = 'localhost';
$mysql_user = 'username';
$mysql_password = 'password';
$mysql_database = 'databaseName';
$conn = mysql_connect($mysql_hostname,$mysql_user,$mysql_password);
mysql_select_db($mysql_database, $conn);

//build up the list of tables to process
$sql = "SHOW TABLES";
$tables = array();
echo 'Building audit tables list...';
$tablestemp = $db->query($sql,2);
foreach ($tablestemp as $tabletemp)
{		
  $tables[] = $tabletemp;
}
echo 'Audit table list built.';

//run through each table, build up a column list and then run an update SQL statement against the column
foreach($tables as $table)
{
  set_time_limit(80);
	$sql = "SHOW COLUMNS FROM `$table`";
	$columns = array();
	echo 'Building audit columns list for ' . $table . '...';
	$columnstemp = $db->query($sql,2);
	foreach ($columnstemp as $columntemp)
	{		
			$columns[] = $columntemp;
	}
	echo 'Audit column list for ' . $table . ' built.';
	
	foreach($columns as $column)
	{
		set_time_limit(80);
		//trim and replace in one foul SQL swoop!
    $sql = "UPDATE `$table` SET `" . $column['Field'] . "` = TRIM(REPLACE(REPLACE(REPLACE(`" . $column['Field'] ."`,"'","'"),'"','"'),'~','''))";
    echo "$sql";
    mysql_query($sql);
	}
}
mysql_close($conn);
echo "End Process.";

And there you go, pretty handy little helper script to have in your possession when you are presented a database with some dirty data included inside! :)