Prepared Queries

Hi,

Ci4 is getting rather frustrating now.



I've been trying for over 1 day now to complete a fairly simple insert and return any errors.



After submitting my POST via AJAX and doing all the necessary validation from my controller I am calling:

PHP Code:

$register = new \App\Models\User\Register;
$register_user=$register->registerUser($this->$data);
if($register_user!=true) {
    $this->$data['error']=$register_user;
} 


My Model

PHP Code:

<?php namespace App\Models\User;

use CodeIgniter\Model;
use CodeIgniter\Database\Query;

class Register extends Model {
    
    public function __construct() {
        $db = db_connect();
    }
    
    
    public function registerUser ($data) {
        
        $sql="INSERT INTO user_register (salutation, first_name, last_name, telephone, email, password, question, answer, status, level, confirm, ip) VALUES ('" . (int)$data['salutation'] . "',  '" . $db->escapeString($data['first_name']) . "',  '" . $db->escapeString($data['last_name']) . "',  '" . (int)$data['telephone'] . "',  '" . $db->escapeString($data['email']) . "',  '" . $db->escapeString($data['password_hash']) . "',  '" . (int)$data['question'] . "',  '" . $db->escapeString($data['answer_hash']) . "',  '" . $db->escapeString($data['status']) . "',  '" . $db->escapeString($data['level']) . "',  '" . $db->escapeString($data['confirm']) . "',  '" . $db->escapeString($data['ip']) . "' )";
        
        if(!$db->query($sql)) {
            return $db->error();
        }
    }
} 


All seems great as the row is inserted in the database, but Here is the twist,



if(!$db->query($sql)) only seems to tell me if the query is "formatted" correctly.



If for example insert fails because email is not unique, no errors are collected by $db->error because the query is formatted correctly.






So, I try to use a prepared statement because I can use hasError(), [b]getErrorCode() and getErrorMessage(), [/b]and I suppose this is the better (more secure) way to do things.






Now my model (as per the documentation), but this just flat out completely fails on all levels.

PHP Code:

class Register extends Model {
    
    public function __construct() {
        $db = db_connect();
    }
    
    
    public function registerUser ($data) {
        $options=([
            $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);
        }, $options);
        
        $results = $pQuery->execute();
        
        if($results->hasError()) {
             return $results->getErrorMessage();
        }
        
    }
} 


sorry PLEASE HELP this is so frustrating for a simple task.