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?
This question is related to
php
mysql
codeigniter
activerecord
sql-update
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'
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
$data = array(
'name' => $_POST['name'] ,
'groupname' => $_POST['groupname'],
'age' => $_POST['age']
);
$this->db->where('id', $_POST['id']);
$this->db->update('tbl_user', $data);
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().
Source: Stackoverflow.com