Insert Data to MSSQL Database

Hello Good Day,

I`ve been working a online game front-end template which is using both MySql and MsSql. But I am having trouble to insert data to my MsSql Database. Hope someone can help me. Please check my codes below:

SQLSRV:

PHP Code:

public function insert_id()
 {
 return $this->query('SELECT SCOPE_IDENTITY() AS insert_id')->row()->insert_id;
 } 


Controller:

PHP Code:

function validation()
 {
 $this->load->helper('form');
 $this->load->helper('security');
 $this->load->library('form_validation');
 $this->form_validation->set_rules('username', 'Username', 'required|alpha_numeric|callback_username_check|min_length[4]|max_length[12]|xss_clean');
 $this->form_validation->set_rules('email', 'Email Address', 'required|callback_email_check|valid_email|xss_clean');
 $this->form_validation->set_rules('password', 'Password', 'required|alpha_numeric|min_length[6]|max_length[12]|xss_clean');
 $this->form_validation->set_rules('confirmPassword', 'Confirm Password', 'required|matches[password]|xss_clean');
 $this->form_validation->set_rules('pin', 'Pin', 'required|numeric|min_length[4]|max_length[12]|xss_clean');
 $this->form_validation->set_rules('invalidCheck', 'invalidCheck', 'required|xss_clean');
 
 
 //Recaptcha Validation
 if(get_settings('site_settings','enable_recapcha','')!=0){
 $this->form_validation->set_rules('g-recaptcha-response', 'Recaptcha Validation', 'required|callback_validate_captcha');
 $this->form_validation->set_message('validate_captcha', 'Please check the the captcha form');
 }


 if($this->form_validation->run())
  {
 $username = trim($this->input->post('username'));
 $password = trim($this->input->post('password'));
 $email = trim($this->input->post('email'));
 $pin = trim($this->input->post('pin'));
 $user_ip = $this->input->ip_address();
 
 $this->rfonline->create_new_account($data);
 $this->rfonline->create_user_account($username, $user_ip);
 $array = array(
 'success' => '<div class="alert alert-success alert-dismissible" role="alert" >
 <button type="button" class="close" data-dismiss="alert" aria-label="Close">
 <span aria-hidden="true">&times;</span>
 </button>
 You have successfully created new Account
 </div>'
   );
 }else{
 $array = array(
 'error'  => true,
 'username_error' => form_error('username'),
 'email_error' => form_error('email'),
 'password_error' => form_error('password'),
 'confirmPassword_error' => form_error('confirmPassword'),
 'pin_error' => form_error('pin'),
 'invalidCheck_error' => form_error('invalidCheck'),
 'g_recaptcha_response' => form_error('g-recaptcha-response')
 );
  }
 echo json_encode($array);
 } 


Model:

PHP Code:

function create_lu_account($username,$password,$email,$pin,$fb_id){
        $lu_sql = sprintf("INSERT INTO tbl_rfaccount (id,password,Email,accounttype,birthdate,pin,fb_id) VALUES ((CONVERT (binary,$username)),(CONVERT (binary,$password)),$email,0,'2011-11-11 00:00:00',$pin,0)");
        return $this->mssqlu->insert($lu_sql);
    }

    function create_user_account($username,$user_ip){
        $user_sql = sprintf("INSERT INTO tbl_UserAccount (id, createip) VALUES (CONVERT (binary,$username), $user_ip");
        return $this->mssqlu->insert($user_sql);
    } 


@waraywarayako ,

A little bit more info please. What error message are you seeing?

@waraywarayako ,

A little bit more info please. What error message are you seeing?

nothing sir just a blankpage

@waraywarayako ,

Can you check the error Codeigniter error log? Make sure error logging is turned on in your config.php file.

have you tried using $this->db->insert_id() instead of trying to get the id using SCOPE_IDENTITY()?

@waraywarayako ,

Can you check the error Codeigniter error log?  Make sure error logging is turned on in your config.php file.

Hello Sir,

No errors logs. I was able to fix it but the problem no data has been save into MSSQL Database but the success message has been displayed.

PHP Code:

$this->form_validation->set_rules('username', 'Username', 'required|alpha_numeric|callback_username_check|min_length[4]|max_length[12]|xss_clean');
 $this->form_validation->set_rules('email', 'Email Address', 'required|callback_email_check|valid_email|xss_clean');
 $this->form_validation->set_rules('password', 'Password', 'required|alpha_numeric|min_length[6]|max_length[12]|xss_clean');
 $this->form_validation->set_rules('confirmPassword', 'Confirm Password', 'required|matches[password]|xss_clean');
 $this->form_validation->set_rules('pin', 'Pin', 'required|numeric|min_length[4]|max_length[12]|xss_clean');
 $this->form_validation->set_rules('invalidCheck', 'invalidCheck', 'required|xss_clean');
 
 
 //Recaptcha Validation
 if(get_settings('site_settings','enable_recapcha','')!=0){
 $this->form_validation->set_rules('g-recaptcha-response', 'Recaptcha Validation', 'required|callback_validate_captcha');
 $this->form_validation->set_message('validate_captcha', 'Please check the the captcha form');
 }


 if($this->form_validation->run())
  {
 $username = trim($this->input->post('username'));
 $password = trim($this->input->post('password'));
 $email = trim($this->input->post('email'));
 $pin = trim($this->input->post('pin'));
 $user_ip = $this->input->ip_address();
 
 $this->rfonline->create_lu_account($username,$password,$email,$pin);
 $this->rfonline->create_user_account($username, $user_ip);
 $array = array(
 'success' => '<div class="alert alert-success alert-dismissible" role="alert" >
 <button type="button" class="close" data-dismiss="alert" aria-label="Close">
 <span aria-hidden="true">&times;</span>
 </button>
 You have successfully created new Account
 </div>'
   );
 }else{
 $array = array(
 'error'  => true,
 'username_error' => form_error('username'),
 'email_error' => form_error('email'),
 'password_error' => form_error('password'),
 'confirmPassword_error' => form_error('confirmPassword'),
 'pin_error' => form_error('pin'),
 'invalidCheck_error' => form_error('invalidCheck'),
 'g_recaptcha_response' => form_error('g-recaptcha-response')
 );
  }
 echo json_encode($array); 


---

have you tried using $this->db->insert_id() instead of trying to get the id using SCOPE_IDENTITY()?

that codes came for SQLSRV Drivers. I`ve already tried insert_id() but didn`t work.

---

@waraywarayako ,

Can you check the error Codeigniter error log?  Make sure error logging is turned on in your config.php file.

PHP Code:

<?php defined('BASEPATH') OR exit('No direct script access allowed'); ?>

INFO - 2021-07-08 10:30:33 --> Config Class Initialized
INFO - 2021-07-08 10:30:33 --> Hooks Class Initialized
DEBUG - 2021-07-08 10:30:33 --> UTF-8 Support Enabled
INFO - 2021-07-08 10:30:33 --> Utf8 Class Initialized
INFO - 2021-07-08 10:30:33 --> URI Class Initialized
INFO - 2021-07-08 10:30:33 --> Router Class Initialized
INFO - 2021-07-08 10:30:33 --> Output Class Initialized
INFO - 2021-07-08 10:30:33 --> Security Class Initialized
DEBUG - 2021-07-08 10:30:33 --> Global POST, GET and COOKIE data sanitized
INFO - 2021-07-08 10:30:33 --> Input Class Initialized
INFO - 2021-07-08 10:30:33 --> Language Class Initialized
INFO - 2021-07-08 10:30:33 --> Loader Class Initialized
INFO - 2021-07-08 10:30:33 --> Helper loaded: mywebsite_helper
INFO - 2021-07-08 10:30:33 --> Helper loaded: security_helper
DEBUG - 2021-07-08 10:30:33 --> Session Class Initialized
INFO - 2021-07-08 10:30:33 --> Helper loaded: string_helper
INFO - 2021-07-08 10:30:33 --> Encrypt Class Initialized
INFO - 2021-07-08 10:30:33 --> Database Driver Class Initialized
DEBUG - 2021-07-08 10:30:33 --> Session routines successfully run
INFO - 2021-07-08 10:30:33 --> Helper loaded: form_helper
INFO - 2021-07-08 10:30:33 --> Form Validation Class Initialized
INFO - 2021-07-08 10:30:33 --> Controller Class Initialized
INFO - 2021-07-08 10:30:33 --> Helper loaded: url_helper
INFO - 2021-07-08 10:30:33 --> Helper loaded: text_helper
INFO - 2021-07-08 10:30:33 --> Helper loaded: date_helper
INFO - 2021-07-08 10:30:33 --> Helper loaded: rfonline_helper
INFO - 2021-07-08 10:30:33 --> Model Class Initialized
INFO - 2021-07-08 10:30:33 --> Database Driver Class Initialized
INFO - 2021-07-08 10:30:33 --> Database Driver Class Initialized
INFO - 2021-07-08 10:30:33 --> Database Driver Class Initialized
INFO - 2021-07-08 10:30:33 --> File loaded: C:\wamp64\www\rfwargods\application\views\themes/frontend/default/layout/header.php
INFO - 2021-07-08 10:30:33 --> File loaded: C:\wamp64\www\rfwargods\application\views\themes/frontend/default/account/register.php
INFO - 2021-07-08 10:30:33 --> File loaded: C:\wamp64\www\rfwargods\application\views\themes/frontend/default/layout/footer.php
INFO - 2021-07-08 10:30:33 --> Final output sent to browser
DEBUG - 2021-07-08 10:30:33 --> Total execution time: 0.1835
INFO - 2021-07-08 10:30:39 --> Config Class Initialized
INFO - 2021-07-08 10:30:39 --> Hooks Class Initialized
DEBUG - 2021-07-08 10:30:39 --> UTF-8 Support Enabled
INFO - 2021-07-08 10:30:39 --> Utf8 Class Initialized
INFO - 2021-07-08 10:30:39 --> URI Class Initialized
INFO - 2021-07-08 10:30:39 --> Router Class Initialized
INFO - 2021-07-08 10:30:39 --> Output Class Initialized
INFO - 2021-07-08 10:30:39 --> Security Class Initialized
DEBUG - 2021-07-08 10:30:39 --> Global POST, GET and COOKIE data sanitized
INFO - 2021-07-08 10:30:39 --> Input Class Initialized
INFO - 2021-07-08 10:30:39 --> Language Class Initialized
INFO - 2021-07-08 10:30:39 --> Loader Class Initialized
INFO - 2021-07-08 10:30:39 --> Helper loaded: mywebsite_helper
INFO - 2021-07-08 10:30:39 --> Helper loaded: security_helper
DEBUG - 2021-07-08 10:30:39 --> Session Class Initialized
INFO - 2021-07-08 10:30:39 --> Helper loaded: string_helper
INFO - 2021-07-08 10:30:39 --> Encrypt Class Initialized
INFO - 2021-07-08 10:30:39 --> Database Driver Class Initialized
DEBUG - 2021-07-08 10:30:39 --> Session routines successfully run
INFO - 2021-07-08 10:30:39 --> Helper loaded: form_helper
INFO - 2021-07-08 10:30:39 --> Form Validation Class Initialized
INFO - 2021-07-08 10:30:39 --> Controller Class Initialized
INFO - 2021-07-08 10:30:39 --> Helper loaded: url_helper
INFO - 2021-07-08 10:30:39 --> Helper loaded: text_helper
INFO - 2021-07-08 10:30:39 --> Helper loaded: date_helper
INFO - 2021-07-08 10:30:39 --> Helper loaded: rfonline_helper
INFO - 2021-07-08 10:30:39 --> Model Class Initialized
INFO - 2021-07-08 10:30:39 --> Database Driver Class Initialized
INFO - 2021-07-08 10:30:39 --> Database Driver Class Initialized
INFO - 2021-07-08 10:30:39 --> Database Driver Class Initialized
INFO - 2021-07-08 10:30:39 --> Final output sent to browser
DEBUG - 2021-07-08 10:30:39 --> Total execution time: 0.0870
INFO - 2021-07-08 10:32:00 --> Config Class Initialized
INFO - 2021-07-08 10:32:00 --> Hooks Class Initialized
DEBUG - 2021-07-08 10:32:00 --> UTF-8 Support Enabled
INFO - 2021-07-08 10:32:00 --> Utf8 Class Initialized
INFO - 2021-07-08 10:32:00 --> URI Class Initialized
INFO - 2021-07-08 10:32:00 --> Router Class Initialized
INFO - 2021-07-08 10:32:00 --> Output Class Initialized
INFO - 2021-07-08 10:32:00 --> Security Class Initialized
DEBUG - 2021-07-08 10:32:00 --> Global POST, GET and COOKIE data sanitized
INFO - 2021-07-08 10:32:00 --> Input Class Initialized
INFO - 2021-07-08 10:32:00 --> Language Class Initialized
INFO - 2021-07-08 10:32:00 --> Loader Class Initialized
INFO - 2021-07-08 10:32:00 --> Helper loaded: mywebsite_helper
INFO - 2021-07-08 10:32:00 --> Helper loaded: security_helper
DEBUG - 2021-07-08 10:32:00 --> Session Class Initialized
INFO - 2021-07-08 10:32:00 --> Helper loaded: string_helper
INFO - 2021-07-08 10:32:00 --> Encrypt Class Initialized
INFO - 2021-07-08 10:32:00 --> Database Driver Class Initialized
DEBUG - 2021-07-08 10:32:00 --> Session routines successfully run
INFO - 2021-07-08 10:32:00 --> Helper loaded: form_helper
INFO - 2021-07-08 10:32:00 --> Form Validation Class Initialized
INFO - 2021-07-08 10:32:00 --> Controller Class Initialized
INFO - 2021-07-08 10:32:00 --> Helper loaded: url_helper
INFO - 2021-07-08 10:32:00 --> Helper loaded: text_helper
INFO - 2021-07-08 10:32:00 --> Helper loaded: date_helper
INFO - 2021-07-08 10:32:00 --> Helper loaded: rfonline_helper
INFO - 2021-07-08 10:32:00 --> Model Class Initialized
INFO - 2021-07-08 10:32:00 --> Database Driver Class Initialized
INFO - 2021-07-08 10:32:00 --> Database Driver Class Initialized
INFO - 2021-07-08 10:32:00 --> Database Driver Class Initialized
INFO - 2021-07-08 10:32:00 --> File loaded: C:\wamp64\www\rfwargods\application\views\themes/frontend/default/layout/header.php
INFO - 2021-07-08 10:32:00 --> File loaded: C:\wamp64\www\rfwargods\application\views\themes/frontend/default/account/register.php
INFO - 2021-07-08 10:32:00 --> File loaded: C:\wamp64\www\rfwargods\application\views\themes/frontend/default/layout/footer.php
INFO - 2021-07-08 10:32:00 --> Final output sent to browser
DEBUG - 2021-07-08 10:32:00 --> Total execution time: 0.2282
INFO - 2021-07-08 10:32:04 --> Config Class Initialized
INFO - 2021-07-08 10:32:04 --> Hooks Class Initialized
DEBUG - 2021-07-08 10:32:04 --> UTF-8 Support Enabled
INFO - 2021-07-08 10:32:04 --> Utf8 Class Initialized
INFO - 2021-07-08 10:32:04 --> URI Class Initialized
INFO - 2021-07-08 10:32:04 --> Router Class Initialized
INFO - 2021-07-08 10:32:04 --> Output Class Initialized
INFO - 2021-07-08 10:32:04 --> Security Class Initialized
DEBUG - 2021-07-08 10:32:04 --> Global POST, GET and COOKIE data sanitized
INFO - 2021-07-08 10:32:04 --> Input Class Initialized
INFO - 2021-07-08 10:32:04 --> Language Class Initialized
INFO - 2021-07-08 10:32:04 --> Loader Class Initialized
INFO - 2021-07-08 10:32:04 --> Helper loaded: mywebsite_helper
INFO - 2021-07-08 10:32:04 --> Helper loaded: security_helper
DEBUG - 2021-07-08 10:32:04 --> Session Class Initialized
INFO - 2021-07-08 10:32:04 --> Helper loaded: string_helper
INFO - 2021-07-08 10:32:04 --> Encrypt Class Initialized
INFO - 2021-07-08 10:32:04 --> Database Driver Class Initialized
DEBUG - 2021-07-08 10:32:04 --> Session routines successfully run
INFO - 2021-07-08 10:32:04 --> Helper loaded: form_helper
INFO - 2021-07-08 10:32:04 --> Form Validation Class Initialized
INFO - 2021-07-08 10:32:04 --> Controller Class Initialized
INFO - 2021-07-08 10:32:04 --> Helper loaded: url_helper
INFO - 2021-07-08 10:32:04 --> Helper loaded: text_helper
INFO - 2021-07-08 10:32:04 --> Helper loaded: date_helper
INFO - 2021-07-08 10:32:04 --> Helper loaded: rfonline_helper
INFO - 2021-07-08 10:32:04 --> Model Class Initialized
INFO - 2021-07-08 10:32:04 --> Database Driver Class Initialized
INFO - 2021-07-08 10:32:04 --> Database Driver Class Initialized
INFO - 2021-07-08 10:32:04 --> Database Driver Class Initialized
INFO - 2021-07-08 10:32:04 --> Final output sent to browser
DEBUG - 2021-07-08 10:32:04 --> Total execution time: 0.0754
INFO - 2021-07-08 10:32:08 --> Config Class Initialized
INFO - 2021-07-08 10:32:08 --> Hooks Class Initialized
DEBUG - 2021-07-08 10:32:08 --> UTF-8 Support Enabled
INFO - 2021-07-08 10:32:08 --> Utf8 Class Initialized
INFO - 2021-07-08 10:32:08 --> URI Class Initialized
INFO - 2021-07-08 10:32:08 --> Router Class Initialized
INFO - 2021-07-08 10:32:08 --> Output Class Initialized
INFO - 2021-07-08 10:32:08 --> Security Class Initialized
DEBUG - 2021-07-08 10:32:08 --> Global POST, GET and COOKIE data sanitized
INFO - 2021-07-08 10:32:08 --> Input Class Initialized
INFO - 2021-07-08 10:32:08 --> Language Class Initialized
INFO - 2021-07-08 10:32:08 --> Loader Class Initialized
INFO - 2021-07-08 10:32:08 --> Helper loaded: mywebsite_helper
INFO - 2021-07-08 10:32:08 --> Helper loaded: security_helper
DEBUG - 2021-07-08 10:32:08 --> Session Class Initialized
INFO - 2021-07-08 10:32:08 --> Helper loaded: string_helper
INFO - 2021-07-08 10:32:08 --> Encrypt Class Initialized
INFO - 2021-07-08 10:32:08 --> Database Driver Class Initialized
DEBUG - 2021-07-08 10:32:08 --> Session routines successfully run
INFO - 2021-07-08 10:32:08 --> Helper loaded: form_helper
INFO - 2021-07-08 10:32:08 --> Form Validation Class Initialized
INFO - 2021-07-08 10:32:08 --> Controller Class Initialized
INFO - 2021-07-08 10:32:08 --> Helper loaded: url_helper
INFO - 2021-07-08 10:32:08 --> Helper loaded: text_helper
INFO - 2021-07-08 10:32:08 --> Helper loaded: date_helper
INFO - 2021-07-08 10:32:08 --> Helper loaded: rfonline_helper
INFO - 2021-07-08 10:32:08 --> Model Class Initialized
INFO - 2021-07-08 10:32:08 --> Database Driver Class Initialized
INFO - 2021-07-08 10:32:08 --> Database Driver Class Initialized
INFO - 2021-07-08 10:32:08 --> Database Driver Class Initialized
INFO - 2021-07-08 10:32:08 --> Final output sent to browser
DEBUG - 2021-07-08 10:32:08 --> Total execution time: 0.0994
INFO - 2021-07-08 10:32:16 --> Config Class Initialized
INFO - 2021-07-08 10:32:16 --> Hooks Class Initialized
DEBUG - 2021-07-08 10:32:16 --> UTF-8 Support Enabled
INFO - 2021-07-08 10:32:16 --> Utf8 Class Initialized
INFO - 2021-07-08 10:32:16 --> URI Class Initialized
INFO - 2021-07-08 10:32:16 --> Router Class Initialized
INFO - 2021-07-08 10:32:16 --> Output Class Initialized
INFO - 2021-07-08 10:32:16 --> Security Class Initialized
DEBUG - 2021-07-08 10:32:16 --> Global POST, GET and COOKIE data sanitized
INFO - 2021-07-08 10:32:16 --> Input Class Initialized
INFO - 2021-07-08 10:32:16 --> Language Class Initialized
INFO - 2021-07-08 10:32:16 --> Loader Class Initialized
INFO - 2021-07-08 10:32:16 --> Helper loaded: mywebsite_helper
INFO - 2021-07-08 10:32:16 --> Helper loaded: security_helper
DEBUG - 2021-07-08 10:32:16 --> Session Class Initialized
INFO - 2021-07-08 10:32:16 --> Helper loaded: string_helper
INFO - 2021-07-08 10:32:16 --> Encrypt Class Initialized
INFO - 2021-07-08 10:32:16 --> Database Driver Class Initialized
DEBUG - 2021-07-08 10:32:16 --> Session routines successfully run
INFO - 2021-07-08 10:32:16 --> Helper loaded: form_helper
INFO - 2021-07-08 10:32:16 --> Form Validation Class Initialized
INFO - 2021-07-08 10:32:16 --> Controller Class Initialized
INFO - 2021-07-08 10:32:16 --> Helper loaded: url_helper
INFO - 2021-07-08 10:32:16 --> Helper loaded: text_helper
INFO - 2021-07-08 10:32:16 --> Helper loaded: date_helper
INFO - 2021-07-08 10:32:16 --> Helper loaded: rfonline_helper
INFO - 2021-07-08 10:32:16 --> Model Class Initialized
INFO - 2021-07-08 10:32:16 --> Database Driver Class Initialized
INFO - 2021-07-08 10:32:16 --> Database Driver Class Initialized
INFO - 2021-07-08 10:32:16 --> Database Driver Class Initialized
DEBUG - 2021-07-08 10:32:16 --> Form_validation class already loaded. Second attempt ignored.
INFO - 2021-07-08 10:32:16 --> Language file loaded: language/english/form_validation_lang.php
INFO - 2021-07-08 10:32:16 --> Final output sent to browser
DEBUG - 2021-07-08 10:32:16 --> Total execution time: 0.1043