Update database field with array in CodeIgniter

Question

Again I find myself at the mercy of the stackoverflow community!

I've gone over to use CodeIgniter for my PHP projects and it's been a breeze so far, hoever I've gotten stuck trying to update a database field with some post data.

My array is the usual: array(name => value, name => value, name => value); which again is populated from the submitted $_POST data.

Similarly to the array, I have a database table with 2 fields: setting and value, where the names under setting corresponds to the array keys and value to the array keys' value.

(Did I explain that right?)

Nonetheless, I've been trying for a little while now to get this to work as it should, but, I'm really just waving my hands around in the dark.

I hope some of you bright minds out there can help me with this annoying issue!

Edit:

Thanks to everyone who replied! I managed to produce the result that I wanted with the following code:

    foreach ($form_data as $key => $val)
    {
        $this->db->where ('setting', $key);
        $this->db->set ('value', $val);
        $this->db->update ('recruitment');
    }

Now, I tried following up with this by adding:

    if ($this->db->affected_rows() >= '1') { return true; }
    return false;

To my model, and

        if ($this->RecruitmentSettings->Update($form_data) == TRUE)
        {
            redirect('recruitment/success');
        }

to my controller, but it's not working as expected at all. Any ideas what I'm doing wrong?

Answer 1

There are a lot of questions here. Do you already have values in the database and you want to update them? Or do you want to put in new data every time? The answer depends on that.

What you want is the insert_batch() or update_batch() methods of the active record class (if that's what you're using for the db).

foreach($post_array as $key => $value)
{
    $settings[] = array(
        'setting' => $key,
        'value' => $value
    );
}

$this->db->insert_batch('your_db_table', $settings);

OR, for updates:

$this->db->update_batch('your_db_table', $settings, 'setting');

You could do a query to check for settings and do insert_batch or update_batch depending on if there are results. If you wanted to insert every time, you could delete the rows in the table before you do the insert. I wouldn't do that without a transaction, however.