Tag Archives: code

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');

//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)
	$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)
		//trim and replace in one foul SQL swoop!
    $sql = "UPDATE `$table` SET `" . $column['Field'] . "` = TRIM(REPLACE(REPLACE(REPLACE(`" . $column['Field'] ."`,"'","'"),'"','"'),'~','''))";
    echo "$sql";
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! :)

jQuery: How to Expand an Accordion for Printing CodeUnit 05 MAY 2010

The jQuery UI library brings with it a host of cool effects and elements to apply to your project, one of which is the pretty cool accordion effect that basically turns a whole lot of sequential divs into an “accordion” – allowing you to click on a header to expand the selected div while the reset of them contract to hide away.

I did however come across a little problem that needed solving the other day, namely that when printing an accordion page, only the currently open, in other words visible, accordion segment prints, leaving the rest of the closed segments hidden on the final printed page.

Obviously this isn’t particularly useful when you are printing something out and needs to be addressed – which it turns out to be a pretty simple thing to achieve after all.

So let’s look at the code to expand and print our accordion page then shall we?

	var answer = confirm ("Do you wish to print this page's contents?");
	if (answer) {

The function above is called when the user clicks on the print button or whatever other element you have associated with the print functionality. The first step is a simple modal dialog to ask whether or not the user really wishes to print the current page. If we get a positive answer, when then move to dealing with the accordion, in other words expanding it so that all the information elements are showing.

To do this is a simple matter of grabbing all content divs and forcing them to show. Yup, as simple as that. The second line attempts to make the page look a little neater by changing the currently open header to look the same as all the closed headers – just for consistency of look of course.

We then run the javascript print function which initiates the browser’s print functionality and after that action has been done and control is handed back to the browser tab, we go and close all the accordion segments to make the page all neat and tidy again.


So as you can see, it is a pretty simple matter of expanding all those neat little jQuery UI accordions of yours after all! :)

PHP: Calculate the Number of Working Days in a Month CodeUnit 23 APR 2010

How to calculate the number of working days in a month, in other words discount Saturdays and Sundays from a month’s total number of days.

It sounds pretty simple, but because of the shifting nature of the calendar we use, one can’t just take a guess and average it you know. No, we need a function to work out this total for each month of any particular year.

Now of course I’m sure there are for more acute, beautiful or elegant solutions to this particular problem out there, but I for one quite like the idea of seeing the logic behind what is happening step by step, which is exactly why I came up with this little function to do the work for me.

So in order to calculate the number of working days in a month by removing Saturdays and Sundays from its day count, have a look at this simple PHP function:

function calculateWorkingDaysInMonth($year = '', $month = '')
	//in case no values are passed to the function, use the current month and year
	if ($year == '')
		$year = date('Y');
	if ($month == '')
		$month = date('m');
	//create a start and an end datetime value based on the input year 
	$startdate = strtotime($year . '-' . $month . '-01');
	$enddate = strtotime('+' . (date('t',$startdate) - 1). ' days',$startdate);
	$currentdate = $startdate;
	//get the total number of days in the month	
	$return = intval((date('t',$startdate)),10);
	//loop through the dates, from the start date to the end date
	while ($currentdate <= $enddate)
		//if you encounter a Saturday or Sunday, remove from the total days count
		if ((date('D',$currentdate) == 'Sat') || (date('D',$currentdate) == 'Sun'))
			$return = $return - 1;
		$currentdate = strtotime('+1 day', $currentdate);
	} //end date walk loop
	//return the number of working days
	return $return;

As you can see, the logic of the function is pretty straightforward to follow. To use the function in your code, simply call it and pass the month and year you want it to examine, meaning that


is going to result in 22.

Nice! :)

PHP: Select Random Web Color with One Line of Code CodeUnit 17 APR 2010

HTML or Web colors are defined using a hexadecimal (hex) notation for the combination of Red, Green and Blue color values, commonly known as RGB. The lowest color value possible for one of these “light” sources is 0 (hex 00) and the highest value is 255 (hex FF). The color codes are written as 3 double digit numbers prefixed with a # sign. Technically, the combination of 255 x 255 x 255 possible color codes means that there exists 16 million possible colors using this particular notation.

The quickfire tip for today is the generation of a random HTML color using just a single line of PHP color. Handy if you want to throw a bit of a surprise burst of changing color here and there…

$randomcolor = '#' . strtoupper(dechex(rand(0,10000000)));

The line above will produce a value that looks something like #240BFD, perfect for slotting in any web element’s style you can think of!

Simple, wasn’t it? ;)

My Face Hurts from all the Grinning My Life 17 MAY 2007

I cant stop grinning. This overbearing feeling of being a genius is difficult to get rid of at the moment :)

When I code stuff, I tend to try and make segments as flexible and reusable as possible, meaning I very seldom have any pieces of code that are exclusively written for a once-off usage.

Case in point: In the past I offered to host research surveys online. So I developed a hosting framework for this ongoing task and it still copes very well with smallish surveys to this day. I simply copy and paste from the supplied Word document into an Access database and the survey just appears from there.

Now I just got an urgent request from one of the lecturers to host their doctoral research project survey, the urgency arising out of the fact that it applies only to first year students, and that species will be leaving campus at the end of the month once the exam period kicks in. So I tell the lecturer, sure no problem, Ill have it up in a day – and then she hands me an 11 page document.


My framework likes smallish surveys, not one where the entire survey submission overflows the POST data buffer on a single page submit. *sigh* I hate tedious work and this meant that it was about to become tedious. And then I remembered a multi page survey I did a year ago, based on an older version of my framework.

I pulled up the pages, slotted the new database in and tada! = big grin on my face :)

agree and disagree survey