Valor_ Valor_ - 4 months ago 9
MySQL Question

Code only updates false value in DB, but not true

I have a strange case when only false state is saved in to the database. When the "state" should be true, my query still executes false.

I have controller with this function

public function change_active_state_post()
{
$id['id_location'] = $this->input->post('id_location');
$state['active'] = $this->input->post('state');
var_dump($state['active']);
$this->locations->update($state, $id);
}


This is MY_Model update function

function update($data,$conditions,$tablename=""){
if($tablename=="")
$tablename = $this->table;
$this->db->where($conditions);
$this->db->update($tablename,$data);
var_dump($this->db->last_query());
return $this->db->affected_rows();
}


When i make a request to deactivate location i get following data in my log & database is properly updated

Location.php:196:string 'false' (length=5)
MY_model.php:46:string 'UPDATE `Locations` SET `active` = 'false'
WHERE `id_location` = '2'' (length=67)


But when i make request to activate location i get following data in my log & and database value isn't updated

Location.php:196:string 'true' (length=4)
MY_model.php:46:string 'UPDATE `Locations` SET `active` = 'true'
WHERE `id_location` = '2'' (length=66)


The problem is that active column never get's updated with value 1 or true. It's stays 0, but if i wan't to make it 0 from 1 it will always work.

active column type in database is
tinyint(1)


If you need any additional information's please let me know and i will provide: Thank you in advance

UPDATE

This additional check helped me to insert data properly

if($this->input->post('state') == 'true'){
$state['active'] = true;
}else{
$state['active'] = false;
}

Answer

Here 'true' is quoted so it is being inserted as a string:

MY_model.php:46:string 'UPDATE `Locations` SET `active` = 'true'

In MySQL both strings 'true' and 'false' are converted to 0 when placed into tinyint(1) column, since neither is proper numeric value.

Using values 0 and 1 will work. MySQL also defines constants FALSE (0) and TRUE (1), so those will also work as long as you insert them unquoted and not as strings.