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.
#
Hello, I am a newb with php and working with your code to dynamically create the parameters
I have tried this function this is copied & pasted from my project the code i have is this
function set_params($stmt,$params){ if ($params != null){ $types = ''; foreach ($params as $param) { if (is_int($param)) { $types .= 'i'; } elseif (is_float($param)) { $types .='d'; } elseif (is_string($param)){ $types .='s'; } else { $types .='b'; } } $bind_names[] = $types; for ($i =0; $i < count($params); $i++){ $bind_name = 'bind' . $i; $$bind_name = $params[$i]; $bind_names[] = &$$bind_name; } call_user_func(array($stmt,'bind_param'),$bind_names); } return $stmt; and I have the parameter setup like you do in the example the only difference is that i have my query set up in a function like thisfunction db_query($sql, $params){ //connect to the database $connection = db_connect(); //query the database $stmt = $connection->prepare($sql); set_params($stmt,$params); $stmt->execute(); $stmt->bind_result($result); $stmt->fetch(); $stmt->close(); return $result; } when i run the application and fill in my fields then hit my submit button i get the following errors Warning: Wrong parameter count for mysqli_stmt::bind_param() in E:\xampp\htdocs\dprojects\guildapplication\includes\functions.php on line 85 line 85 in my code iscall_user_func(array($stmt,'bind_param'),$bind_names); Warning: mysqli_stmt::bind_result(): Number of bind variables doesn't match number of fields in prepared statement in E:\xampp\htdocs\dprojects\guildapplication\includes\functions.php on line 54 line 54 in my code is inside my db_query function#
This was perfect. I can’t tell you how much you helped with the script.
Thank you!!!
If I may add one tiny detail:
$stmt = DynamicBindVariables($stmt,$paramList);Now $stmt is a ‘mysqli_result Object’