note 80118 modified in mysqli.stmt-bind-param by danbrown

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. --was-- 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. http://php.net/manual/en/mysqli.stmt-bind-param.php