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

Question

In my db table, I have two datetime columns: Last and Current. These column allow me to keep track of when someone last used a valid login to the service I am building up.

Using CodeIgniter's active record, is it possible to update a row so that the Last value receives the Current value AND then the Current value is replace with the current datetime?

Solution 1

Try like this:

$data = array('current_login' => date('Y-m-d H:i:s'));
$this->db->set('last_login', 'current_login', false);
$this->db->where('id', 'some_id');
$this->db->update('login_table', $data);

Pay particular attention to the set() call's 3rd parameter. false prevents CodeIgniter from quoting the 2nd parameter -- this allows the value to be treated as a table column and not a string value. For any data that doesn't need to special treatment, you can lump all of those declarations into the $data array.

The query generated by above code:

UPDATE `login_table`
SET last_login = current_login, `current_login` = '2018-01-18 15:24:13'
WHERE `id` = 'some_id'

Solution 2

$data = array( 
    'name'      => $_POST['name'] , 
    'groupname' => $_POST['groupname'], 
    'age'       => $_POST['age']
);

$this->db->where('id', $_POST['id']);

$this->db->update('tbl_user', $data);

Solution 3

if you want to upgrade only a single column of a table row then you can use as following:

$this->db->set('column_header', $value); //value that used to update column  
$this->db->where('column_id', $column_id_value); //which row want to upgrade  
$this->db->update('table_name');  //table name

Comments

[Krishna Chalise]

how can we do it if we need to perform $this->db->set('some_column','some_column+$some_data');

[Loki]

@krishnachalise If it is number to add,$this->db->set('some_column','some_column+$some_data',fa‌​lse)

[Loki]

Or if string to concat, $this->db->set('some_column','CONCAT(some_column,\''.$some_d‌​ata.'\')',false);

[Loki]

irrelevant answer 👎🏻

[mickmackusa]

I agree with @Loki This answer does not attempt to solve the question asked. This is merely updating a row with user-submitted data. The question, which is suitably answered by Rajeev is how to update one column value using another column's value in the same row. This answer is simply wrong (or the positive way to spin it -- This is the correct answer to a different question. Furthermore, this is a code-only answer which means it is a low-value post on Stackoverflow.

[mickmackusa]

This answer does not attempt to solve the question asked. This is merely updating a row with the value in a variable. The question, which is suitably answered by Rajeev is how to update one column value using another column's value in the same row. This answer is simply wrong (or the positive way to spin it -- This is the correct answer to a different question.

[Danish]

what if only wants to move column and not update current_login date. what would be this statement like $this->db->update('login_table', $data); ?