Form validation in Codeigniter 3 using different database

Question

I got in trouble with Codeigniter 3 using the form validation library; I have to check that the email address submitted by a user into aregistration form is unique into the users database. I use two databases in my project, the users one is not the default. To perform the email check I use the following code:

$this->form_validation->set_rules('email','Email','trim|required|valid_email|is_unique[users.email]');

I got an error about the missing users table into the default database, so I've realized that CI 3 looks the email to check into a default database ... not the correct users database, even if in the construct I load the correct model/database.

Is there a way to perform the check into a different database using the form validation above?

Thanks for any feedback

UPDATE Below the code I use to load the model in the controller

function __construct()
{
    parent::__construct();
    $this->load->model("admin/user_model","user");
}

Below the code of the User_model

// Database
private $auth_db;

// Tables
private $table_users = 'users';

public function __construct()
{
    parent::__construct();
    $this->auth_db = $this->load->database('auth', true);
}

and...finally...in the config file the database configuration

$active_group = 'default';
$query_builder = TRUE;

$db['default'] = array(
    'dsn'   => '',
    'hostname' => 'localhost',
    'username' => 'xxxxxxxxx',
    'password' => 'xxxxxxxxx',
    'database' => 'vfr_main',
    'dbdriver' => 'mysqli',
    'dbprefix' => '',
    'pconnect' => FALSE,
    'db_debug' => TRUE,
    'cache_on' => FALSE,
    'cachedir' => '',
    'char_set' => 'utf8',
    'dbcollat' => 'utf8_unicode_ci',
    'swap_pre' => '',
    'encrypt' => FALSE,
    'compress' => FALSE,
    'stricton' => FALSE,
    'failover' => array(),
    'save_queries' => TRUE
);

$db['auth'] = array(
    'dsn'   => '',
    'hostname' => 'localhost',
    'username' => 'xxxxxxxxxxx',
    'password' => 'xxxxxxxxxxx',
    'database' => 'vfr_auth',
    'dbdriver' => 'mysqli',
    'dbprefix' => '',
    'pconnect' => FALSE,
    'db_debug' => TRUE,
    'cache_on' => FALSE,
    'cachedir' => '',
    'char_set' => 'utf8',
    'dbcollat' => 'utf8_unicode_ci',
    'swap_pre' => '',
    'encrypt' => FALSE,
    'compress' => FALSE,
    'stricton' => FALSE,
    'failover' => array(),
    'save_queries' => TRUE
);

user_1440932 · Answer

In CodeIgniter 3, models do not have a direct way to control which database group they connect to. In this case I do not think we can simply write a custom validation rule, because is_unique is the only rule that makes a db call, and there is no built in way to change db.

In this case I think the most direct approach would be to extend the form validation class, and then add a new is_unique_authdb method in this extended library for checking with the second db. Then you would use this new method in place of the call you have shown above.

In the 3.x repo on git hub I see this is the existing method.

public function is_unique($str, $field)
{
    sscanf($field, '%[^.].%[^.]', $table, $field);
    return isset($this->CI->db)
        ? ($this->CI->db->limit(1)->get_where($table, array($field => $str))->num_rows() === 0)
        : FALSE;
}

And your extra method could be something like:

public function is_unique_authdb($str, $field)
{
    $db = $this->CI->load->database('auth', true);
    sscanf($field, '%[^.].%[^.]', $table, $field);
    return isset($db)
        ? ($db->limit(1)->get_where($table, array($field => $str))->num_rows() === 0)
        : FALSE;
}

Extending a native library is very simple. For example, to extend the native Form_validation class you’ll create a file named application/libraries/MY_Form_validation.php, and declare your class with:

class MY_Form_validation extends CI_Form_validation {
    public function is_unique_authdb($str, $field)
    {
        $db = $this->CI->load->database('auth', true);
        sscanf($field, '%[^.].%[^.]', $table, $field);
        return isset($db)
            ? ($db->limit(1)->get_where($table, array($field => $str))->num_rows() === 0)
            : FALSE;
    }
}

Then you would load the library as normal but it will load your extended library and you can use the authdb method for this validation.

$this->form_validation->set_rules('email','Email','trim|required|valid_email|is_unique_authdb[users.email]');

CI3 docs for extending a native library.

Note: With CodeIgniter 4 I think this would be more simple because CI4 has a built in property for models that specifically allows you to manage which db a model will connect to.

Form validation in Codeigniter 3 using different database

1 Answers