Updating Checked Checkboxes in Database

[eluser]Timothy_[/eluser]
Hello fellow Codeigniter coders! :cheese:

Background Information
I have about 8 checkboxes that need to be updated in my mysql database.

I have a table with 4 fields (ID, project_id, checkbox_id, time_checked)

If an entry exists with a checkbox_id then that check box is 'checked'.

This works great, however the problem comes when I want to edit my checkboxes.

I did a quick and dirty which was basically to just delete all checkboxes with a certain project_id and then reinserting the values from the form.

This is now just not acceptable as I am keeping time references when each checkbox was checked and obviously this wont work if I am recreating them all from scratch.

So the question is...

How can I update only the fields that have been changed?

My friend helped me come up with the algorithm below.

Code:

if (empty($_POST['progresscheck'])) {
        $linkproc = '';    
    }
    else
    {
        $linkproc = $_POST['progresscheck'];
        $N = count($linkproc);

for(int i=0; i<n; i++)
    {
        if($linkproc[$i] = checkedindatabase())
        {
            if($linkproc[$i] = notcheckedonpage)
            {
                removevalue(i)fromdatabase
            }
        }
        else
        {
    
        //this means its not in database
        if($linkproc[1] = checkedonpage)
        {
            insertINTOposition(i)indatabase
        }
    }
}

Unfortunately it's not quite there; and I really need some help to put the theory into real code.

I hope someone can help me with this.

Thanks,

Tim

[eluser]mddd[/eluser]
I would not use this kind of cross table in this case. A set of checkboxes is always linked to a certain project. So I would just have a table 'project properties' that has 1 record per project, keeping track of all the checkboxes. Then you can read all the checkboxes in one go, and set the new values in one go too. Now, you are making things more complicated than necessary, I think.

If the checkboxes keep changing all the time, than your solution is a good one, but it does come at a cost as you see..
If you want to do it this way, I would definitely read all checkboxes at once and keep them in a variable. Then you can more easily check which have changed. Going to the database to check for every checkbox whether it has been set or not, is too much work!

[eluser]Timothy_[/eluser]
Yes I believe I have to do it this way as all the checkboxes are generated dynamically from another table.

Could you please post some example code of how I would read all the checkboxes in at once and store them as a variable???

Thanks

[eluser]mddd[/eluser]
To get the list of checkboxes checked for a certain $yourprojectid:

Code:

$this->db->select('checkbox_id');
$this->db->where('project_id', $yourprojectid);
$boxes = $this->db->get('checkbox_table')->result_array();

// $boxes is now a list of arrays, every array contains one field 'checkbox_id'.
// make it easier by simplifying this to a simple array of checkbox id's
foreach($boxes as &$box) $box = $box['checkbox_id'];

// $boxes is now a simple list like array(1,2,3) -> meaning checkboxes 1,2 and 3 were checked.

If you want to update, you also need to have a list of ALL possible checkbox id's. Then you loop through that list, and see if you need to add or remove a certain checkbox. For speed, I would 'save them up' and do all saving and deleting in one go. I assume your checkboxes are named $_POST['checkbox_{id}'] and their value is '1'.

Code:

$remove = array();
$set = array();

foreach ($checkbox_ids as $id)
{
  // if this box was set and is not in the list of already set boxes, add it
  if ($this->input->post('checkbox_'.$id)=='1' && !in_array($id, $boxes)) $set[] = $id;
  // if is was not set but was in the list of set boxes, remove it
  if ($this->input->post('checkbox_'.$id)!='1' && in_array($id, $boxes)) $remove[] = $id;
}

// now you have a list of boxes to set and remove
if (sizeof($remove))
{
  $this->db->where_in('checkbox_id', $remove);
  $this->db->where('project-id', $yourprojectid);
  $this->db->delete('checkbox_table');
}
if (sizeof($set))
{
  foreach($set as $id)
  {
    $this->db->set('project_id', $yourprojectid);
    $this->db->set('checkbox_id', $id);
    $this->db->set('time_checked', 'NOW()', false);
    $this->db->insert('checkbox_table');
  }
}

Setting boxes can be made quicker by combining the queries into one, entering multiple records at once. But that's up to you..

[eluser]Timothy_[/eluser]
Hey,

Just wanted to say thanks your solution, it works perfectly and has helped learn how to tackle similar problems down the road.

Cheers,

Tim

[eluser]mddd[/eluser]
Thanks! That is great to hear! I love thinking about design patterns like this.