Valor_ Valor_ - 1 year ago 58
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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download