16

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?

mickmackusa
  • 43,625
  • 12
  • 83
  • 136
chris
  • 36,115
  • 52
  • 143
  • 252

4 Answers4

53

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'
mickmackusa
  • 43,625
  • 12
  • 83
  • 136
Rajeev Ranjan
  • 4,152
  • 3
  • 28
  • 41
  • how can we do it if we need to perform `$this->db->set('some_column','some_column+$some_data');` – Krishna Chalise Jul 05 '18 at 17:15
  • @krishnachalise If it is number to add,`$this->db->set('some_column','some_column+$some_data',false)` – Loki Sep 15 '18 at 04:26
  • Or if string to concat, `$this->db->set('some_column','CONCAT(some_column,\''.$some_data.'\')',false);` – Loki Sep 15 '18 at 04:33
  • 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); ? – Danish Dec 21 '20 at 00:29
2
$data = array( 
    'name'      => $_POST['name'] , 
    'groupname' => $_POST['groupname'], 
    'age'       => $_POST['age']
);

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

$this->db->update('tbl_user', $data);
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
Praveen PL
  • 41
  • 1
  • 2
  • 1
    irrelevant answer – Loki Sep 15 '18 at 04:36
  • 1
    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 Apr 05 '20 at 06:33
1

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
Bizley
  • 17,392
  • 5
  • 49
  • 59
Mustafiz
  • 35
  • 1
  • 1
    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. – mickmackusa Apr 05 '20 at 07:43
0

Yes, this is possible and I would like to provide a slight alternative to Rajeev's answer that does not pass a php-generated datetime formatted string to the query.

The important distinction about how to declare the values to be SET in the UPDATE query is that they must not be quoted as literal strings.

To prevent CodeIgniter from doing this "favor" automatically, use the set() method with a third parameter of false.

$userId = 444;
$this->db->set('Last', 'Current', false);
$this->db->set('Current', 'NOW()', false);
$this->db->where('Id', $userId);
// return $this->db->get_compiled_update('Login');  // uncomment to see the rendered query
$this->db->update('Login');
return $this->db->affected_rows();  // this is expected to return the integer: 1

The generated query (depending on your database adapter) would be like this:

UPDATE `Login` SET Last = Current, Current = NOW() WHERE `Id` = 444

Demonstrated proof that the query works: https://www.db-fiddle.com/f/vcc6PfMcYhDD87wZE5gBtw/0

In this case, Last and Current ARE MySQL Keywords, but they are not Reserved Keywords, so they don't need to be backtick-wrapped.

If your precise query needs to have properly quoted identifiers (table/column names), then there is always protectIdentifiers().

mickmackusa
  • 43,625
  • 12
  • 83
  • 136