I'm in a situation where I want to build a code which gets $bindParam
variable in this format:
$bindParams = [$type1 => $param1, $type2 => $param2, ... ]
I wanna build some code that dynamically adds that parameters to the prepared statement.
This is the code which I built so far :
$mysql = new mysqli("localhost", "root", "", "db1");
$stmt = $mysql->prepare($sql);
foreach($bindParams as $type => $data) {
$stmt->bind_param($type, $data);
}
$stmt->execute();
$result = $stmt->get_result();
// and after perhaps twiddling with the result set, but this is not the case .....
For your instance
$sql = "INSERT INTO table1 (name, age) VALUES (?,?);"
and
$bindParams = ["s" => "hello", "i" => 15]
This does not always have this structure and it can change to for example $bindParams = ["s" => "hello", "i" => 15, "d" => 22.5]
and so the $sql
changes respectively.
After the first time the compiler heads to $stmt->bind_param($type, $data);
firefox flushes this error:
Warning: mysqli_stmt::bind_param(): Number of variables doesn't match number of parameters in prepared statement in D:\PHP\tr.php on line 23
I know PDO support that as stated here at the end of the page. but perhaps as you might expect Im not a fan of PDO so ;)
My other option is to use the eval()
workarounds available in php but thats out of what I might think of.
Is there another way to do this?
Then, have a look at the bind_param() function: $stmt->bind_param("sss", $firstname, $lastname, $email); This function binds the parameters to the SQL query and tells the database what the parameters are. The "sss" argument lists the types of data that the parameters are.
bindParam is a PHP inbuilt function used to bind a parameter to the specified variable name in a sql statement for access the database record. bindValue, on the other hand, is again a PHP inbuilt function used to bind the value of parameter to the specified variable name in sql statement. 2. Execution.
You must always use prepared statements for any SQL query that would contain a PHP variable. To do so, always follow the below steps: Create a correct SQL SELECT statement. Test it in mysql console/phpmyadmin if needed.
I had the same problem, and found an answer much simplier:
$array_of_values = array( "Brasil", "Argentina" );
$types = "ss";
$mysqli_stmt->bind_param( $types, ...$array_of_values );
This is called "argument unpacking", and is available since PHP 5.6
Sadly mysqli
doesn't support this. Calling the function over and over again overwrites the values, so you're only binding one param when you clearly have more.
There's a couple of ways to get around this
Bind the params as one aggregate using call_user_func_array
$sqltype = '';
$sqldata = [];
foreach($bindParams as $type => $data) {
$sqltype .= $type;
$sqldata[] = &$data; // MUST be a reference
}
array_unshift($sqldata, $sqltype); // prepend the types
call_user_func_array([$stmt, 'bind_param'], $sqldata);
I use something like this to do dynamic procedure calls.
Example Call:
$mapi = new MySQLIAPI($con);
$mapi->BeginProc();
$mapi->AddParameter("user", $usern, "s");
$mapi->AddParameter("email", $email, "s");
$mapi->AddParameter("passwd", $pwd, "s");
$id = $mapi->CallProc("ij_create_user");
$id = $id[0];
if(isset($id['mysql_error']) || isset($id["error"])){
return "error";
}
return $id["id"];
Example Class:
class MySQLIAPI
{
private $con = null;
private $Variables = null;
private $values = null;
private $types = null;
private $vQu = null;
private $stmt = null;
function __construct($dbc)
{
$this->con = $dbc;
$this->Variables = [];
$this->values = [];
$this->types = [];
$this->vQu = [];
}
function BeginProc()
{
$this->stmt = $this->con->stmt_init(); // initialize statement
}
function AddParameter($key, $val, $type)
{
$this->Variables[] = "@" . $key;
$this->values[] = $val;
$this->types[] = $type;
$this->vQu[] = "?";
}
//KeyPair is v = the value, t = the type s or d
function CallProc($Proc) {
$out_var = null;
$call = "";
if(sizeof($this->values) > 0)
$call = "CALL ".$Proc."(".implode(",", (array)$this->vQu).")";
else
$call = "CALL ".$Proc."()";
if($this->stmt->prepare($call));//call stored procedure with database server session variable
{
if(sizeof($this->values) > 0) {
$params = array_merge(array(implode("", $this->types)), $this->values);
call_user_func_array(array($this->stmt, 'bind_param'), $this->refValues($params));
}
$this->stmt->execute();
$result = $this->stmt->get_result();
/* Error Checking */
$mySQLiError = mysqli_stmt_error($this->stmt);
if ($mySQLiError != "") {
$this->resetStmt();
$this->stmt->close();
$this->stmt = null;
return array('mysql_error' => $mySQLiError);
}
while ($row = $result->fetch_array(MYSQLI_ASSOC))
{
$out_var[] = $row;
}
$result->free();
while($this->stmt->more_results())
{
$this->stmt->next_result();
}
$this->resetStmt();
$this->stmt->close();
$this->stmt = null;
}
return $out_var;
}
private function refValues($arr)
{
if (strnatcmp(phpversion(), '5.3') >= 0) //Reference is required for PHP 5.3+
{
$refs = array();
foreach ($arr as $key => $value)
$refs[$key] =& $arr[$key];
return $refs;
}
return $arr;
}
private function resetStmt()
{
//Reset Params
$this->Variables = array();
$this->values = array();
$this->types = array();
$this->vQu = array();
}
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With