Flauschig Dev. Blog

I worked with PHP and MySQLi for the last few days and still don’t like it that much but got used to it.

Something that was annoying me pretty much was the Binding of Parameters in an SQL-Statement.
So I decided to write a little Helper Function for this.

A normal Binding looks like this:

$stmt = $db->prepare('UPDATE SET Name=?, SomeInt=? FROM SomeTable WHERE ID = ?');
$stmt->bind_param('sii', $newName, $newInt, $id);

It’s a shame that it’s not possible to pass an Array of Objects to the bind_param

After some search I found a Function for that problem:

// $stmt = The SQL Statement Object
// $param = Array of the Parameters
function DynamicBindVariables($stmt, $params)
{
	if ($params != null)
	{
		// Generate the Type String (eg: 'issisd')
		$types = '';
		foreach($params as $param)
		{
			if(is_int($param)) {
				// Integer
				$types .= 'i';
			} elseif (is_float($param)) {
				// Double
				$types .= 'd';
			} elseif (is_string($param)) {
				// String
				$types .= 's';
			} else {
				// Blob and Unknown
				$types .= 'b';
			}
		}
 
		// Add the Type String as the first Parameter
		$bind_names[] = $types;
 
		// Loop thru the given Parameters
		for ($i=0; $i<count($params);$i++)
		{
			// Create a variable Name
			$bind_name = 'bind' . $i;
			// Add the Parameter to the variable Variable
			$$bind_name = $params[$i];
			// Associate the Variable as an Element in the Array
			$bind_names[] = &$$bind_name;
		}
		
		// Call the Function bind_param with dynamic Parameters
		call_user_func_array(array($stmt,'bind_param'), $bind_names);
	}
	return $stmt;
}

Now you can Bind the Parameters like this:

$paramList = array();
$paramList[] = $newName;
$paramList[] = $newInt;
$paramList[] = $id;
$stmt = $db->prepare('UPDATE SET Name=?, SomeInt=? FROM SomeTable WHERE ID = ?');
DynamicBindVariables($stmt, $paramList);

The Code is a bit longer but we’re now able to easily generate Statements with a dynamic amount of Parameters and bind them correctly.