Ten Ten Studios - Blog

When we start a new custom programming project we typically begin by copying and pasting a handful of useful functions that we have developed for previous projects. Most developers will put together a toolbox like this -- if you haven't, you can save yourself a lot of hassle by doing so. The first step is to make sure that you're wrapping any potentially reusable code in a function. For example:

// Proper formatting for print_r
// $array = array to display
function print_array($array) {
  print '<pre>';
  print_r($array);
  print '</pre>';
}

Simple and useful, this function allows for easy viewing of arrays. The default print_r function does its job but does so in a fairly hard-to-read format. This one just wraps that function in <pre> tags to preserve the formatting. Here's another:

// Trim function - cuts text to a certain length
// $string = string to trim; $max_length = longest allowed string before trimming; $append = characters to add on after the trim (typically "...")
function neat_trim($string, $max_length, $append = '')
{
  if (strlen($string) > $max_length) {
    $string = substr($string, 0, $max_length);
    $pos = strrpos($string, ' ');
    if ($pos === false) {
      return substr($string, 0, $max_length) . $append;
    }
    return substr($string, 0, $pos) . $append;
  }
  else {
    return $string;
  }
}

This one accepts a string of text and chops it to a specified length and tacks on the value of $append (usually something like "...") at the end. You can create all sorts of small functions like this to simplify formatting of dates, checking length of field or integer values, or other form validation requirements.

Database Querying

It's safe to assume that if you're working on a custom web application you're going to need to interact with a database from time to time. We needed a simple function that would connect to our database and select, insert, update, or delete as needed. We needed that function to be secure and to prevent threats such as SQL Injection so we came up with the following:

// DB Query
// $query = SQL query, $params = array of parameters (i, s, d, b), $rs = whether or not a resultset is expected, $newid = whether or not to retrieve the new ID value;
// $onedimensionkey = key required to convert array into simple one dimensional array; $admin = if true, use the admin credentials (used only for logins)
function db_query($query, $params, $rs = true, $newid = false, $onedimensionkey = false, $admin = false) {
  (!$admin) ? $link = mysqli_connect(DB_SERVER, DB_USER, DB_PASS, DB_NAME) : $link = mysqli_connect(AUTH_SERVER, AUTH_USER, AUTH_PASS, AUTH_NAME);
  if (!$link) { 
    print 'Error connecting to MySQL Server. Errorcode: ' . mysqli_connect_error(); 
    exit; 
  }
  
  // Prepare the query and split the parameters array into bound values
  if ($sql_stmt = mysqli_prepare($link, $query)) {
		if ($params) {
			$types = '';
			$new_params = array();
			$params_ref = array();
			// Split the params array into types string and parameters sub-array
			foreach ($params as $param) {
				$types .= $param['type'];
				$new_params[] = $param['value'];
			}
			// Cycle the new parameters array to make it an array by reference
			foreach ($new_params as $key => $parameter) {
				$params_ref[] = &$new_params[$key];
			}
			call_user_func_array('mysqli_stmt_bind_param', array_merge(array($sql_stmt, $types), $params_ref));
		}
  }
  else {
		print 'Error: ' . mysqli_error($link);
		exit();
  }
	
  // Execute the query
  mysqli_stmt_execute($sql_stmt);
	
	// Store results
	mysqli_stmt_store_result($sql_stmt);
	
  // If there are results to retrive, do so
  if ($rs) {
		$results = array();
		$rows = array();
		$row = array();
		stmt_bind_assoc($sql_stmt, $results);
		while (mysqli_stmt_fetch($sql_stmt)) {
			foreach ($results as $key => $value) {
				$row[$key] = $value;
			}
			$rows[] = $row;
		}
		if ($onedimensionkey) {
			$i = 0;
			foreach ($rows as $row) {
				$simplearray[$i] = $row[$onedimensionkey];
				$i++;
			}
		}
  }
  // If there are no results but we need the new ID, save it
  elseif ($newid) {
		$newid = mysqli_insert_id($link);
  }
  
  // Close objects
  mysqli_stmt_close($sql_stmt);
  mysqli_close($link);
	
	// Return necessary arrays/values
	if (isset($rows)) {
		return $rows;
	}
	if (isset($simplearray)) {
		return $simplearray;
	}
	if (isset($newid)) {
		return $newid;
	}
}

Rather lengthy, the crux of the function is as follows: it accepts a MySQL database query in the $query parameter, outputs a resultset if the $rs parameter is true, or returns a newly inserted ID value if the $newid parameter is true. The best part about the function is that it parameterizes all submitted data to prevent SQL injection. The $params variable accepts an array of parameters specifying the data to be used and what format it should expect (integer, text, binary, or numeric.) Also included there is a call to a helper function, stmt_bind_assoc which helps to format the results of SELECT queries into a PHP-friendly array:

// Bind results to an array
// $stmt = sql query, $out = array to be returned
function stmt_bind_assoc (&$stmt, &$out) {
  $data = mysqli_stmt_result_metadata($stmt);
  $fields = array();
  $out = array();

  $fields[0] = $stmt;
  $count = 1;

  while($field = mysqli_fetch_field($data)) {
		$fields[$count] = &$out[$field->name];
		$count++;
  }    
  call_user_func_array('mysqli_stmt_bind_result', $fields);
}

Once you have an acceptable database querying function working you can use it in other functions to simplify development. For example, if you want to log events in a log you can write a function to do so and save you the time of writing extra queries:

// Watchdog (log) entry
// $type = type of log entry; $message = the message to be logged; $uid = user id (NULL if anonymous)
function watchdog($type, $message, $uid = NULL, $authdb = false) {
	$todaysdate = date('Y-m-d H:i:s');
	$params = array(
		array('value' => $todaysdate, 'type' => 's'),
		array('value' => $type, 'type' => 's'),
		array('value' => substr($message, 0, 2000), 'type' => 's'),
		array('value' => $uid, 'type' => 'i')
	);
	db_query('INSERT INTO watchdog (date, type, message, uid) values (?, ?, ?, ?)', $params, false);
}

Hopefully a few of these functions have helped. Either way, it's always a good idea to work up a few useful reusable functions that can be moved from project to project to save you time. No sense reinventing the wheel each time!