+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: CodeIgniter 4 (https://forum.codeigniter.com/forumdisplay.php?fid=28)
+--- Forum: CodeIgniter 4 Support (https://forum.codeigniter.com/forumdisplay.php?fid=30)
+--- Thread: Pass Array to Prepared Statements (/showthread.php?tid=76680)
Pass Array to Prepared Statements - 68thorby68 - 06-09-2020
Hi ,
I'm tearing my hair out trying to fathom Prepared statements in Ci4 as per the documentation.
Could someone please help me understand why the follwing statement fails with error "mysqli_stmt::bind_param(): Number of variables doesn't match number of parameters in prepared statement" undefined, when an array is passed to the execute statement, as per the documentation. I have tried enclosing the SQL ? parameter with parentheses (?) but get the same error?????
This throws the error
PHP Code:
use CodeIgniter\Model;
use CodeIgniter\Database\Query;
class Register extends Model {
public function registerUser ($data) {
$db = db_connect();
$dataArray=array(
$data['salutation'],
$data['first_name'],
$data['last_name'],
$data['telephone'],
$data['email'],
$data['password_hash'],
$data['question'],
$data['answer_hash'],
$data['status'],
$data['level'],
$data['confirm'],
$data['ip']
);
$pQuery = $db->prepare(function($db) {
$sql = "INSERT INTO user_register (salutation, first_name, last_name, telephone, email, password, question, answer, status, level, confirm, ip) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
return (new Query($db))->setQuery($sql);
}, $dataArray);
try {
$pQuery->execute($dataArray);
}catch (\Exception $e) {
return ($e->getMessage());
}
}
}
PHP Code:
use CodeIgniter\Model;
use CodeIgniter\Database\Query;
class Register extends Model {
public function registerUser ($data) {
$db = db_connect();
$dataArray=array(
$data['salutation'],
$data['first_name'],
$data['last_name'],
$data['telephone'],
$data['email'],
$data['password_hash'],
$data['question'],
$data['answer_hash'],
$data['status'],
$data['level'],
$data['confirm'],
$data['ip']
);
$pQuery = $db->prepare(function($db) {
$sql = "INSERT INTO user_register (salutation, first_name, last_name, telephone, email, password, question, answer, status, level, confirm, ip) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
return (new Query($db))->setQuery($sql);
}, $dataArray);
try {
$pQuery->execute($data['salutation'],
$data['first_name'],
$data['last_name'],
$data['telephone'],
$data['email'],
$data['password_hash'],
$data['question'],
$data['answer_hash'],
$data['status'],
$data['level'],
$data['confirm'],
$data['ip']);
}catch (\Exception $e) {
return ($e->getMessage());
}
}
}
I just dont understand????
PLEASE HELP !!!!!
RE: Pass Array to Prepared Statements - 68thorby68 - 06-16-2020
I DO NOT believe Ci4 execute accepts arrays of data, but instead insists on each data element being defined indiviually. That said I believe a single data field can be an array.
Unfortunately this thread is a result of my mis-interpretation of the documentation.
RE: Pass Array to Prepared Statements - israes - 01-28-2022
I'm dealing with it now, and thanks to your post my code now works!!!
In addition, I found a quick fix related to this on php docs, take a look at example #5 https://www.php.net/manual/en/pdostatement.execute.php#example-1045