CodeIgniter query: How to move a column value to another column in the same row and save the current time in the original column?

CodeIgniter query: How to move a column value to another column in the same row and save the current time in the original column?

  1. Blog
  2. 15 November 2026
  3. 4 min read

Stefan Bogdanescu

Founder & Senior Architect

Atomic Swaps in CodeIgniter: Migrating Timestamps Without Race Conditions

Handling datetime logic in stateful applications often requires more than just setting a field to NOW(). When you need to preserve an existing timestamp while simultaneously updating it, doing so in two separate database statements introduces a critical vulnerability. If the user performs another action between your first read/update and the second update, the data becomes inconsistent. To handle this safely in CodeIgniter's Active Record, you must treat the column migration as a single atomic unit.

The scenario involves two columns: Last, which tracks when a valid login occurred previously, and Current, which represents the timestamp of the most recent activity. The goal is to shift the value from Current into Last and then overwrite Current with the new time, all within one transaction or statement.

In standard SQL, you cannot reference an alias (like AS last_time) in the same UPDATE statement's SET clause because the database executes assignments sequentially before evaluating the final values. However, you can reference the table column directly. This approach ensures that the new Last value is derived from the exact row data existing at the moment of execution, preventing race conditions where another process might modify the row between a separate SELECT and an UPDATE.

Here is how to implement this in CodeIgniter 3.x using the Query Builder:

$this->db->where('id', $user_id); // Ensure you filter by the specific user ID

// Use SET A = B, B = NOW() syntax directly in the query builder
$this->db->update('users', [
    'last_login' => 'current_login', // This assigns the value of current_login to last_login
    'current_login' => date('Y-m-d H:i:s') // Or use DB::now() depending on your driver
], ['id' => $user_id]);

While the above snippet shows the concept, CodeIgniter's Active Record often requires a different syntax for column aliasing because it expects array keys to match column names exactly. If you attempt to pass an alias like ['current_login' => 'last_login'] directly as a value, the framework might treat it as a string literal rather than a reference to another column.

A more robust pattern in CodeIgniter involves chaining methods or using raw SQL within the Active Record context to ensure the assignment happens correctly. One common approach is to perform the swap in two steps if the database driver supports it (like MySQL), but strictly speaking, relying on two separate queries without a transaction is risky.

However, there is a cleaner way to handle this without breaking into raw SQL strings that might confuse the ORM. You can explicitly assign the current row's current_login value to last_login by referencing the column name in the SET clause if your specific database driver allows it. In MySQL, you can do:

UPDATE users 
SET last_login = current_login, current_login = NOW() 
WHERE id = $id;

To achieve this within CodeIgniter's Active Record without writing raw SQL, you might need to construct the array carefully. Since standard Active Record arrays map column names to values, passing a value that is another column name usually results in an error or treats it as a string. A workaround often used in CI3 is to update last_login with a placeholder and then handle the logic, but that defeats the atomicity requirement.

The safest implementation without raw SQL involves acknowledging a limitation of Active Record: it does not natively support "column-to-column" assignment in a single call like native SQL does. Therefore, developers often have to choose between accepting a slight performance hit for data integrity or using transactions.

If you are using CodeIgniter 4, the Query Builder is more flexible and closer to raw SQL syntax. You can execute the atomic update directly:

$db->table('users')
    ->where('id', $id)
    ->update(['last_login' => 'current_login', 'current_login' => new DateTime()]);

Note that new DateTime() is a PHP object; ensure your database driver converts it to the correct string format, or use DB::now() equivalent if available. If direct column assignment fails in your specific CI version, wrapping the logic in a database transaction ensures that even if an exception occurs during the update, the partial change does not persist.

Ultimately, the choice between using raw SQL for this specific atomic update versus chaining two separate Active Record calls depends on your tolerance for race conditions. For high-concurrency login systems, the single-statement atomic update is the only correct architectural decision to prevent lost updates.

Our network