note 80118 added to function.mysqli-stmt-bind-param

I've found that you can't pass NULL values in using mysql_stmt_bind_param. Recently I ran into this problem because I wrote some MySQL routines that would update existing data, but only when the value wasn't NULL.

My solution to work around this is simple:

<?php

function preparse_prepared($sQuery, &$saParams)
{
	$nPos = 0;

	$sRetval = $sQuery;
	foreach ($saParams as $Param)
	{
		//if we find no more ?'s we're done then
		if (($nPos = strpos($sQuery, '?', $nPos + 1)) === false)
		{
			break;
		}

		//this test must be done second, because we need to increment offsets of $nPos for each ?.
		//we have no need to parse anything that isn't NULL.
		if (!is_null($Param))
		{
			continue;
		}

				
		//null value, replace this ? with NULL.
		$sRetval = substr_replace($sRetval, 'NULL', $nPos, 1);
	}
			
			
	return $sRetval;
}

?>

This will iterate the given list of parameters and replace any null values in the query with an actual null value. You'll want to use the resulting $sQuery to pass to mysqli_prepare(). For that, I use another routine that generates a list of the values (s, i, etc).

For example:

<?php

					array_unshift($saParams, $this->getPreparedTypeString($saParams));
					array_unshift($saParams, $stmt);
					
					call_user_func_array('mysqli_stmt_bind_param', $saParams);

?>

Where getPreparedTypeString is defined as:

<?php

		public static function getPreparedTypeString(&$saParams)
		{
			$sRetval = '';

			//if not an array, or empty.. return empty string
			if (!is_array($saParams) || !count($saParams))
			{
				return $sRetval;
			}

			//iterate the elements and figure out what they are, and append to result
			foreach ($saParams as $Param)
			{
				if (is_int($Param))
				{
					$sRetval .= 'i';
				}
				else if (is_double($Param))
				{
					$sRetval .= 'd';
				}
				else if (is_string($Param))
				{
					$sRetval .= 's';
				}
			}

			return $sRetval;
		}

?>

To clarify why I pass array values by reference: They aren't being modified, so I don't want copies of them begin made in memory as they may be large. In other languages, this is much more efficient. Not sure if PHP handles passing values on a "copy on edit" basis.. but I'm guessing not.
----
Server IP: 208.69.120.35
Probable Submitter: 76.28.182.137
----
Manual Page -- http://www.php.net/manual/en/function.mysqli-stmt-bind-param.php
Edit        -- https://master.php.net/note/edit/80118
Del: integrated  -- https://master.php.net/note/delete/80118/integrated
Del: useless     -- https://master.php.net/note/delete/80118/useless
Del: bad code    -- https://master.php.net/note/delete/80118/bad+code
Del: spam        -- https://master.php.net/note/delete/80118/spam
Del: non-english -- https://master.php.net/note/delete/80118/non-english
Del: in docs     -- https://master.php.net/note/delete/80118/in+docs
Del: other reasons-- https://master.php.net/note/delete/80118
Reject      -- https://master.php.net/note/reject/80118
Search      -- https://master.php.net/manage/user-notes.php