update setting all fields to zero

[eluser]mflammia[/eluser]
Hi,

When updating a record using the code below it seems to replace all items with a 0?

Code:

function update_recordP($updateID,$person)
{
  $this->db->where ('id_person',$updateID);
                $this->db->update('person', $person);
                $str = $this->db->last_query();
                echo $str;
  return;
}

The echo $str looks like the following:

Code:

UPDATE `person` SET `first_name` = 'Joe', `second_name` = 'Bloggs', `dob` = '1972-08-11', `phone` = '', `email` = '' WHERE `id_person` = '5'

Code:

+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| id_person   | int(11)     | NO   | PRI | NULL    | auto_increment |
| first_name  | varchar(50) | YES  |     | NULL    |                |
| second_name | varchar(50) | YES  |     | NULL    |                |
| dob         | date        | NO   |     | NULL    |                |
| email       | varchar(80) | YES  |     | NULL    |                |
| phone       | varchar(20) | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+

Regardless of whats in the table, whether some fields have entries or are blank the update sets them all to zero's?

Any ideas?

[eluser]LifeSteala[/eluser]
Hey,

What does the variable or parameter, $person, contain exactly? The second parameter of $this->db->update() should be an array with all the keys your database table has with values of '0'.

See Active Record - User Guide (Updating Data) for more information.

[eluser]mflammia[/eluser]
Thanks for repling

The $person array looks like the below for this particular update:

Code:

Array
(
    [first_name] => Joe
    [second_name] => Bloggs
    [dob] => 1972-08-11
    [phone] =>
    [email] =>
)

Although if I set phone and email to 0, the same problem exits?

[eluser]Samus[/eluser]
[quote author="mflammia" date="1334220512"]Thanks for repling

The $person array looks like the below for this particular update:

Code:

Array
(
    [first_name] => Joe
    [second_name] => Bloggs
    [dob] => 1972-08-11
    [phone] =>
    [email] =>
)

Although if I set phone and email to 0, the same problem exits?[/quote]
Check their data types.

For example if your first_name data type is set to "int" (integer) it will only expect an integer any other string inserted will be defaulted to 0.

If this is the case just change their data type to varchar and specify a length.

EDIT: nvm, i see you have appropriate data types

[eluser]CroNiX[/eluser]
try:
$updateID = (int)$updateID;

Then run your query.

[eluser]mflammia[/eluser]
Thanks for all that have posted so fare....

Have tried using the $updateID = (int)$updateID; but that did not fix it.

Thought I would post some of my controller code as apposed to the model already provided to give some more insight.

I actually have one to many tables with the person table being the one and the address table below being an example of another. The public table always has some data in it where as the others may not.

When editing a record I test to see which part of the form, associated to its corresponding table (person, address etc) has been submitted / changed and then do an update or an insert. This is done by testing the result of an initial row query on each table which I store as a true or false in the flashdata session - tried previously using public set variables for this in the controller class but I could not get the variable to remain through eaching calling of the functions - bit of a newbie so just experimenting at the moment and fine tuning as I go so please be kind to what might be messy or unnecessary code :-)

Have created quite a few functions as it has helped save on repeated code that I use, for example when creating a new record.

The code seems to work perfectly for calling correctly either an update or insert - although an update would always be called for the person table as it always contains data.


Edit form submission calls this function in the controller:

Code:

function edit($updateID)
{
            $this->load->model('site_model');
            $this->load->helper('form');
            $this->load->helper('url');
            $data = $this->get_records_edit();
            $data['updateID'] = $updateID;
            $this->load->view('edit', $data);
            
}

Initial row query from model:

Code:

function get_person(){
            $query = $this->db->query('SELECT * FROM person WHERE id_person='.$this->uri->segment(3));
            if ($query->num_rows() ==1){
                return $query->row();
            }
            return false;
        }
        
        function get_address(){
            $query = $this->db->query('SELECT * FROM address WHERE id_person='.$this->uri->segment(3));
            if ($query->num_rows() == 1){
                return $query->row();
            }
            return false;
        }

Function in controller that calls the queries and sets the variables / session for true of false:

Code:

function get_records_edit(){
                $this->load->library('session');
                if ($this->site_model->get_person()){
                    $data['person'] = $this->site_model->get_person();
                    $data['personSet']= 1;
                    $this->session->set_flashdata('personSet', '1');
                    //$this->personSet = 1;
                }else{
                    $data['personSet']= 0;
                    $this->session->set_flashdata('personSet', '0');
                    //$this->personSet = 0;
                    //echo 'personSet'.$data['personSet'];
                }
                if ($this->site_model->get_address()){
                    $data['address'] = $this->site_model->get_address();
                    $data['addressSet']= 1;
                    $this->session->set_flashdata('addressSet', '1');
                    //$this->addressSet = 1;
                }else{
                    $data['addressSet']= 0;
                    $this->session->set_flashdata('addressSet', '0');
                    //$this->addressSet = 0;
                    //echo 'address'.$data['addressSet'];
                }
}

Controller function to test field's to amend from edit:

Code:

function personPostSet(){
            
        }
        
        function addressPostSet(){
            if ($this->input->post('postcode')||$this->input->post('number')||$this->input->post('address1')||$this->input->post('address2')||$this->input->post('address3')){
                return TRUE;
            }else {
                return FALSE;
            }
        }

Controller function that stores the post inputs as an array that are related to a table in the DB:

Code:

function personArray(){
            $person = array(
   'first_name' => $this->input->post('firstName'),
   'second_name' => $this->input->post('secondName'),
                        //'dob' => $this->correctDob,
                        'dob' => $this->input->post('dob'),
                        'phone' => $this->input->post('phone'),
                        'email' => $this->input->post('email'),
                );
            return $person;
        }
        
        function addressArray(){
            $address = array(
                        'postcode' => $this->input->post('postcode'),
                        'number' => $this->input->post('number'),
                        'address1' => $this->input->post('address1'),
                        'address2' => $this->input->post('address2'),
                        'address3' => $this->input->post('address3'),
                );
            return $address;
        }

Controller function that tests whether to update or insert a record:

Code:

function update(){
            $this->load->model('site_model');
            $this->load->library('session');
            $updateID = $this->uri->segment(3);
            if ($this->session->flashdata('personSet') == 1){
                $person = $this->personArray();
                $this->site_model->update_recordP($updateID,$person);
            }
            if ($this->session->flashdata('addressSet') == 1){
                $address = $this->addressArray();
                $this->site_model->update_recordA($updateID,$address);
            }elseif ($this->addressPostSet()) {
                $address = $this->addressArray();
                $this->site_model->add_recordA($updateID,$address);
            }
        }

[eluser]CroNiX[/eluser]
You do this in several places:

Code:

$this->load->model(');

and this:

Code:

if ($this->session->flashdata(') == 1){

and this:

Code:

$this->session->set_flashdata(', '1');

See how your code is messed up as far as the colors (all red)? It's because you have a LOT of unmatched quotes. You should be seeing a LOT of errors.

[eluser]mflammia[/eluser]
Sorry about that, don't think the copy and paste worked well - have now amended.

The plan is also to later tidy up the code a bit and put some of those things in the construct.

Thanks.

[eluser]mflammia[/eluser]
Have taken the update query and written directly into the model:

The query:

Code:

UPDATE `person` SET `first_name` = 'Joe', `second_name` = 'Bloggs', `dob` = '1972-08-11', `phone` = '', `email` = '' WHERE `id_person` = '5'

Re-written it

Code:

UPDATE person SET first_name = 'Joe', second_name = 'Bloggs', dob = '1972-08-11', phone = '', email = '' WHERE id_person = '5'

So changed to model fucntion:

Code:

function update_recordP($updateID,$person)
{
                $updateID = (int)$updateID;
                $this->db->query ("UPDATE person SET first_name = 'joe', second_name = 'bloggs', dob = '1972-08-11', phone = '', email = '' WHERE `id_person` = 5");
                //$this->db->where ('id_person',$updateID);
                //$this->db->update('person', $person);
                $str = $this->db->last_query();
                echo $str;
}

When the function is called the updated works perectly!

So I am thinking that although it looks clean the $person array or the $updateID is introducing something that should not be there - is there a codigniter function that could be used to clean it?

[eluser]Aken[/eluser]
What database driver are you using? It's possible your database is not set up to handle backticks, so they're just confusing the query and causing unexpected results. The query with the backticks looks perfectly fine from what I can gather.