The dateFormat function is one that I love. Throw any kind of date format at it, request any format back, it just works.

Examples of usage:

$humanDisplay = dateFormat('2013-08-22 17:52:33','human');
$tomorrow = dateFormat('now + 1 day','human');
$mysql_date = dateFormat('8/30/2013','mysqldate');
$mysql_datetime = dateFormat('8/30/2013 5:00 PM','mysql');
$unix_timestamp = dateFormat('8/30/2013 5:00 PM','php');

if(dateFormat('now','php')>dateFormat($someVariable,'php')) {
// do stuff
}

The dateFormat function (and related supporting functions):

function parseTimezone($inputDate) {
	if(	   strpos($inputDate,'Eastern')>0
		|| strpos($inputDate,'Central')>0
		|| strpos($inputDate,'Mountain')>0
		|| strpos($inputDate,'Pacific')>0
		|| strpos($inputDate,'Alaska')>0
		|| strpos($inputDate,'Hawaii-Aleutian')>0
		|| strpos($inputDate,'Arizona')>0 
		|| strpos($inputDate,'America/')>0 
		|| strpos($inputDate,'Pacific/')>0 
		|| strpos($inputDate,'GMT')>0
		) {
		$parts = explode(' ',$inputDate);
		$timezoneName = array_pop($parts);
		$inputDate = implode(' ',$parts);
	}
	return $inputDate;
}


function isValidDate($inputDate) {
	if($inputDate=="01/01/0001"
		|| $inputDate=="01/01/01"
		|| $inputDate=="0001-01-01"
		|| $inputDate=="0000-01-01"
		|| $inputDate=="0000-00-00"
		|| $inputDate==""
		|| strlen($inputDate)<3
		) {
		return false;
		// don't increase the required length by more than 3 so "now" doesn't get cut
	}
	
	$inputDate = parseTimezone($inputDate);
	
	
	if(is_numeric($inputDate) && $inputDate>100000000) {
		// 100000000 = 1973-03-03 01:46:40
		// this should catch all integers that are errantly caught as dates
		$phpTime = $inputDate;
	} elseif($inputDate!='' && ( strpos(strtolower($inputDate),'now')!==false 
									|| strpos($inputDate,'/') 
									|| strpos($inputDate,'-')
									|| strpos($inputDate,'day')
									|| strpos($inputDate,'week')
									|| strpos($inputDate,'month')
									|| strpos($inputDate,'year')
									) ) {
		$phpTime = strtotime($inputDate);
	} else {
		return false;
	}
	
	if($phpTime==-62135568000) { // 0000-00-00
		return false;
	}
	if($phpTime==NULL) {
		return false;
	}
	$month = date('n',$phpTime);
	$day = date('j',$phpTime);
	$year = date('Y',$phpTime);
	
	return checkdate($month,$day,$year);
}


function dateFormat($inputDate, $format=NULL, $params=array()) {
	if(!isValidDate($inputDate)) {
		$inputDate=NULL;
	}
	if(strtolower($inputDate)=="now" || strtolower($inputDate)=="now()") {
		$inputDate = "now";
	}
	if($format==NULL) {
		$format="human";
	}
	
	$inputDate = parseTimezone($inputDate);
	
	if(is_numeric($inputDate) && (int)$inputDate==$inputDate) {
		$phpTime = $inputDate;
	} elseif($inputDate!='') {
		$phpTime = strtotime($inputDate);
	}
	
	if($phpTime) {
		if($format=="human") {
			return date("m/d/Y",$phpTime);
		} else if(strtolower($format)=="mysql") {
			return date("Y-m-d H:i:s",$phpTime);
		} else if(strtolower($format)=="mysqldate" || ($format=="%Q") ) {
			return date("Y-m-d",$phpTime);
		} else if(strtolower($format)=="mysqltime" || ($format=="%T") ) {
			return date("H:i:s",$phpTime);
		} else if(strtolower($format)=="solr") {
			return gmdate('Y-m-d\TH:i:s\Z',$phpTime);
		} else if(strtolower($format)=="php") {
			return $phpTime;
		} else if(strtolower($format)=="human datetime") {
			return date("n/j/y g:i A",$phpTime);

		} else if($format!='') {
			return date($format,$phpTime);
		}
	} else {
		return NULL;
	}
}

September 13th, 2013

Posted In: Functions

Leave a Comment

When writing code, if you find yourself copy/pasting a chunk of code from one place to another that is something other than “include: ‘header.php'” and “include: ‘footer.php'”, you might want to double-check what you’re doing.

Instead of copy/pasting the code that displays a widget for example, make that code into a function. Call is something like “createWidget()” and then reference that in your code.

In a recent example, I needed to show a notes list on two different view pages. The code was the exact same between the two so I created a function to do it instead. That function looked something like this:

function qt_getNotes($params=array()) {
	$id = $params['id'];
	$query = "SELECT * FROM table WHERE id='".encodeSQL($id)."'";
	$result = getMySQLData($query);
	$out = '';
	while($row = @mysql_fetch_assoc($result)) {
		$out .= 'Author: '.$row['author'].'<br>';
		$out .= $row['body'];
		$out .= '<hr>';
	}
	return $out;
}

if($command=='adminView') {
	echo "Show admin stuff here.";
	echo qt_getNotes(array('id'=>$id));
	echo "Show admin footer here.";
}

if($command=='workerView') {
	echo "Show worker stuff here.";
	echo qt_getNotes(array('id'=>$id));
	echo "Show worker footer here.";
}

This is obviously a pretty simple example in terms of visual sophistication, I just wanted to show how it could be used.

Why did I preface my function with “gt_”? Because this function was designed for use within our “Quality Ticket” area and would not be used outside of that area. Also, I didn’t want to try to double-define any function that may have been previously called “getNotes”.

And why did I use an entire array for passing the $id? Because you’ll likely need to pass other variables to that function at some point in time (like whether or not to show a delete button) and you can now add those as parameters in the array rather than defining additional in-line inputs to the function. Keeps it clean when you have a variable number of possible inputs.

You’ll also notice I used “$out” to store the view components of this rather than just echo’ing it straight onto the page. This comes in handy if you need to store the returned HTML or have it displayed in an email or something like that.

The benefits of using a function like this is that you can change the code once and it effectively is updated in multiple places. You also have your own variable namespace within the function so you can re-use variables like $id, $data, $result, and $row which are commonly used in the body of a page without concern that you’ll overwrite an existing loop you might be in.

The downsides of a function are that they don’t inherit page variables and must be carefully defined once in your system. Variable inheritance is helpful if you have usernames, permissions, or display variables on your page that you would want your function to utilize. To use those variables, you’d need to pass them in your $params array.

August 20th, 2013

Posted In: Functions, Syntax

Leave a Comment

The audit log tells you who changed every value on every item in your system. It tells you when they did it and what page they were on when they did it.

This is used all the time in my daily work. We can see who change any record in the system, what page they made those changes on, and what other values were changed at the same time. We can see a complete history of changes made, and that’s incredibly helpful when you need to figure out, “What Happened?!?”

function writeAuditLog($params) {
	$databaseName = $params['databaseName'];
	$tableName = $params['tableName'];
	$primaryKeyName = $params['primaryKeyName'];
	$primaryKeyValue = $params['primaryKeyValue'];
	$newDataArray = $params['newDataArray'];
	$arbitraryValue = $params['arbitraryValue'];
	
	$id_user = $_SESSION['session_id_user'];
	$page = curPageURL();
	
	if($arbitraryValue!='' || (is_array($newDataArray) && count($newDataArray)>0)) {
		
		$performQueries = true;
		mysql_query("SET NAMES utf8",$GLOBALS['standardDBlink']);
		$query = "UPDATE audit_log SET database_name='".encodeSQL($databaseName)."',
										table_name='".encodeSQL($tableName)."',
										primary_key_value='".encodeSQL($primaryKeyValue)."',
										page_performed_on='".encodeSQL($page)."'";
		if($id_user!='') {
			$query .= ", id_contact_performing_change='".encodeSQL($id_user)."' ";
		}
		if($arbitraryValue!='') {
			$query .= ", change_log='".encodeSQL($arbitraryValue)."'";
		} elseif(is_array($newDataArray) && count($newDataArray)>0) {
			$change_log = '';
			$change_arry = array();
			$subQuery = "SELECT * FROM ".$databaseName.".".$tableName." 
							WHERE `".$primaryKeyName."`='".$primaryKeyValue."'";
			$data = getMySQLData($subQuery, false, true);
			if($data) {
				foreach($newDataArray AS $key=>$new_value) {
					$old_value = mysql_result($data,0,$key);
					if($old_value!=$new_value) {
						$change_log .= $key.": ".$old_value." [ changed to ] ".$new_value."\n";
						$change_arry[$key] = array(
							'orig' => (string)$old_value,
							'changed_to' => (string)$new_value
							);
					}
				}
			}
			if(count($change_arry) > 0) {
				$change_log = serialize($change_arry);
			}
			if($change_log == "") {
				$performQueries = false;
			}
			$query .= ", change_log='".encodeSQL($change_log)."'";
		}
		

		if($performQueries) {
			$insertQuery = "INSERT INTO audit_log (`datetime_created`) VALUES (NOW())";
			$id = insertMySQLData($insertQuery);
			$query .= " WHERE id='".$id."'";
			updateMySQLData($query);
		}
	}
}

August 20th, 2013

Posted In: Functions

Leave a Comment

As promised earlier, here’s the save function. This is an almost magical function. It makes every write you’ll almost ever do exceedingly simple.

function save() {
	$this->dataChecks();
	$this->id = objectSave($this->tableName, $this->id, $this->data, $this->databaseName, array('writeAudit'=>true));
	$this->data['id'] = $this->id;
	$this->postSaveFunctions();
}

OK, so that magical save function just basically calls other functions. What’s the big deal? Each of those functions, particularly objectSave() make life easier.

I’ll take them in order, starting with dataChecks(). This function is defined on a per-class basis in a way that meets the needs of the class. For most of my classes, I include a datetime_created field in my MySQL table, so I like to set that value in my dataCheck function. I also take care of filling in any missing fields or special calculations that are needed. The example below shows how I’d set the datetime_created and set an id_client value if it’s missing and id_company was set.

function dataChecks() {
	if($this->data['datetime_created'] == "" && $this->id==''){
		$this->data['datetime_created'] = dateFormat("now","mysql");
	}
	
	
	if($this->data['id_client']=='' && $this->data['id_company']!='') {
		$companyObj = new company($this->data['id_company']);
		$this->setIDclient($companyObj->getIDclient());
	}
}

I’ll go over the dateFormat function at another time. It’s one of those functions that makes me love PHP and is really simple to create.

The next function is the real meat of this post: objectSave(). objectSave() has been an evolution of various needs over time and if I were to design it from scratch today, I’d write it slightly differently. (First thing, pass all data via one $parameters array rather than the listed parameters that it has now.) You’ll also see in this function a function for writeAuditLog(). That’s a function that is worth it’s own posting later as well. And, since this was written for a particular system, it has some database names and table names hard-coded. That, ideally, should be abstracted. Overall though, this function is called many thousands (perhaps hundreds of thousands?) of times a day and saves us countless time and effort.

function objectSave($tableName, $id=NULL, $dataArray, $databaseName='dtrm', $extraParams=array()) {
	
	$writeAudit = false;
	if($extraParams['writeAudit']===true) {
		$writeAudit = true;
	}
	
	if($id==NULL) {
		if($dataArray['id']!=NULL) {
			$id = $dataArray['id'];
		}
	}
	
	if($id==NULL) {
		mysql_query("SET NAMES utf8",$GLOBALS['standardDBlink']);
		$preferredID = $extraParams['preferredID'];
		$query = "INSERT INTO ".$databaseName.".".$tableName." (`id`) VALUES ('".$preferredID."')";
		$q_insert = mysql_query($query);showMySQLError(mysql_error($GLOBALS['standardDBlink']),$query);
		$sqlError = mysql_error($GLOBALS['standardDBlink']);
		if($sqlError!='') {
			$query2 = "INSERT INTO dtrm.badQueryLog (`query`, `page`, `datetime_created`, `error_text`) 
						VALUES ('".encodeSQL($q_insert)."', '".encodeSQL(curPageURL())."',NOW(), 
								'".encodeSQL($sqlError)."')";
			insertMySQLData($query2);
		}
		$id = mysql_insert_id();
		
		if($writeAudit===true) {
			$subParams = array();
			$subParams['databaseName']=$databaseName;
			$subParams['tableName']=$tableName;
			$subParams['primaryKeyName']='id';
			$subParams['primaryKeyValue']=$id;
			$subParams['arbitraryValue']="New Record";
			writeAuditLog($subParams);
		}
	}

	if($writeAudit===true) {
		$subParams = array();
		$subParams['databaseName']=$databaseName;
		$subParams['tableName']=$tableName;
		$subParams['primaryKeyName']='id';
		$subParams['primaryKeyValue']=$id;
		$subParams['newDataArray']=$dataArray;
		writeAuditLog($subParams);
	}
	
	mysql_query("SET NAMES utf8",$GLOBALS['standardDBlink']);
	
	$query = "UPDATE ".$databaseName.".".$tableName." SET ";
	foreach ($dataArray AS $key=>$val) {
		if($key!='id') {
			$query .= "`".$key."`=";
			if($val==='' || $val===NULL) {
				$query .= "NULL, ";
			} else {
				$query .= "'".mysql_real_escape_string($val)."', ";
			}
		}
	}
	$query = trim($query, ", ");
	$query .= " WHERE id=".$id;
	$update_q = mysql_query($query);showMySQLError(mysql_error($GLOBALS['standardDBlink']),$query);
	$sqlError = mysql_error($GLOBALS['standardDBlink']);
	if($sqlError!='') {
		$query2 = "INSERT INTO dtrm.badQueryLog (`query`, `page`, `datetime_created`, `error_text`) 
					VALUES ('".encodeSQL($update_q)."', '".encodeSQL(curPageURL())."',NOW(), 
							'".encodeSQL($sqlError)."')";
		insertMySQLData($query2);
	}
	
	return $id;
	
}

The above function will take the $data array from the class structure that has already been defined and write those fields, correctly, to a MySQL table. That saves infinite time trying to match up fields in code to MySQL insert and update statements. It also handles returning the $id value whether it stays the same or is created as a result of performing the save. Plus, it logs any errors that happen so you can easily see what you’re doing wrong if you make typos in your code. Oh, and it handles NULL values correctly so you don’t end up with blank or ‘0’ values in your table.

The last function in our save function is postSaveFunctions(). This function isn’t used nearly as much as the others listed here, but it can be really handy if you need to have some code execute after you have a primary key identified or otherwise need to have data written before executing the code. I often create a postSaveTriggers array within an object if I am going to need to perform postSaveFunctions so that in other places in the class I can add items to the postSaveTriggers array and then have the class execute them automatically later when it is saved.

function doPostSaveTriggers() {
	$triggersPerformed = array();
	foreach($this->postSaveTriggers AS $triggerName) {

		// skip the trigger if it has already been performed
		if(in_array($triggerName,$triggersPerformed)) {
			continue;
		}
		
		if($triggerName=="human_audit_created") {
			// do stuff here
		}

		if($triggerName=="hire_nostart_payoutEmail") {
			// do stuff here
		}

	}
	$this->postSaveTriggers = array();
}

I want to make sure to make one point clear: the save(), dataChecks(), and postSaveFunctions() functions all all class-specific. The objectSave() function is a single piece of code that should be included via a common file for use everywhere in your system.

August 20th, 2013

Posted In: Functions

Leave a Comment