how to insert a value to both tables using join codeigniter 4 sql server

Question

I'm trying to insert values to both tables at the same time. I'm using a form in my application where I use the inserted values from the form to inert them into the db. But now I'm inserting values to one table (Users).

public function registerUser($formdata){

    helper('global');// a heper for randomString().
    //Asign value to columns
    $db_data['Emailaddress'] = $formdata['emailaddress'];
    $db_data['Password'] = password_hash($formdata['password'], PASSWORD_DEFAULT);
    $db_data['Status'] = 'Free';
    $db_data['Token'] = randomString(32);
    $db_data['FirstLogin'] = 0;
    $db_data['Users.UsersKey'] = $db_data['UsersSettings.UsersKey'];
    //insert to db
    $this->db->table('Users', 'UsersSettings')->join('UsersSettings','Users.UsersKey = UsersSettings.UsersKey', 'inner')->insert($db_data);       
} 
public function updateUserSetting_proccess(){
    $formdata = $this->request->getPostGet();
    return $this->SettingsModel->update_user_settings($formdata);
}

The content of the Users table is:

    SELECT TOP (1000) [UsersKey]
      ,[UniqueID]
      ,[Token]
      ,[ResetToken]
      ,[Emailaddress]
      ,[Password]
      ,[Status]
      ,[DateTimeAdded]
      ,[DateTimeLastUpdated]
      ,[FirstLogin]
    FROM [dbo].[Users]

The UsersKey is inserted automaticly because of the auto increment. The second table I want to use is UsersSettings with content:

 SELECT TOP (1000) [UsersSettingsKey]
      ,[UsersKey]
      ,[FirstName]
      ,[LastName]
      ,[Logo]
      ,[Organization]
      ,[Address]
      ,[Number]
      ,[Addition]
      ,[Postcode]
      ,[City]
      ,[Country]
      ,[Language]
      ,[Theme]
      ,[CalcPercentage]
      ,[CalcAdminFee]
      ,[ColorPrimary]
      ,[ColorSecondary]
      ,[DateTimeLastUpdated]
  FROM [dbo].[UsersSettings]

I want the UsersKey from UsersSettings have the same value in Users UsersKey. I tried this:

join('UsersSettings','Users.UsersKey = UsersSettings.UsersKey', 'inner')

but it didn't help. Can someong give me some suggestions?

Answer 1

You'll need to perform the insert into table Users first in order to get the generated UsersKey.

Explanation of why inserting into table Users first is required, may be shown with the SQL equivalent:

declare @lv_UsersKey int

-- insert into table Users (only essential parts shown)
insert into Users(....) values (...)

-- capture UsersKey for inserted record
select @lv_UsersKey = cast(SCOPE_IDENTITY() as int)

-- then insert into UsersSettings (only essential parts shown)
insert into UsersSettings (UsersKey, ....) values (@lv_UsersKey, ...)

Transferring the above SQL to codeigniter will look like this:

$this->db->table('Users')->insert($db_data);
$inserted_users_key = $this->db->insert_id();

$db_data2['UsersKey'] = $inserted_users_key;
// some more init of $db_data2 here

$this->db->table('UsersSettings')->insert($db_data2);