Valor_ Valor_ - 1 year ago 48
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');
$this->locations->update($state, $id);

This is MY_Model update function

function update($data,$conditions,$tablename=""){
$tablename = $this->table;
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

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


This additional check helped me to insert data properly

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

Answer Source

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.