Welcome Guest, Not a member yet? Register   Sign In
Insert Data to MSSQL Database
#1

(This post was last modified: 07-07-2021, 06:09 AM by waraywarayako.)

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);
    
Reply
#2

@waraywarayako ,

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

(07-07-2021, 06:36 AM)php_rocs Wrote: @waraywarayako ,

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

nothing sir just a blankpage
Reply
#4

@waraywarayako ,

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

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

(This post was last modified: 07-08-2021, 03:34 AM by waraywarayako.)

(07-07-2021, 08:13 PM)php_rocs Wrote: @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); 

(07-08-2021, 02:14 AM)qury Wrote: 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.

(07-07-2021, 08:13 PM)php_rocs Wrote: @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 
Reply




Theme © iAndrew 2016 - Forum software by © MyBB