Trying to save checkbox array to database

I have a half written module that needs finishing:

Database has structure

mem_id
class_id
Attend :(varchar where typical value would be “1,1,0,0,0,0,0,1”)
The “1” value indicates checkbox should be checked, 0 unchecked

The code takes the values and puts them in a form table which includes

    while ($g_fetch = $a_query->fetch_array()) {
          $memid = $g_fetch['mem_id'];
          $at_query = $conn->query("SELECT Attend FROM `attendance` WHERE `mem_id`= '$memid' AND `class_id`= '$classid'");
          $at_fetch = $at_query->fetch_array();
          echo '<td> <input type="hidden" name="memberid[]" value="' . $memid . '" /></td>';

          $att = explode(",", $at_fetch['at']);

          for ($x = 0; $x < $NoWeeks; $x++) {
                  if ($att[$x] != "0") {
                     $checked = 'checked = "checked"';
                     $chkval = 1;
                    } else {
                      $checked = '';
                      $chkval = 0;
          }
           echo '<td align="center"><input type="checkbox" " name="chk[' . $memid . '][]" value = "' . $chkval . '" ' ></td>';
      }

($noWeeks defined elsewhere)
This works fine and displays 8 checkboxes for each id with those that have a value of 1 checked. .

What is not working is then saving the data back to the database e.g. if checkbox 1 was unchecked it should be saved back as “0,1,0,0,0,0,0,1”

Basically I assume it goes something like

  foreach ($_POST['memberid'] as $key=>$memberid){
      if (isset($_POST['chk'][$memberid])){
            foreach ($_POST['chk'][$memberid] as $checkedValue){
                $attend=implode(','$_POST['chk'][$memberid]);
                 $a_query= $conn->query("UPDATE attendance SET Attend = '$attend' WHERE id = '$memberid' AND class_id= '$classid'") ;
            }  
       } 
   }

I have tried several variations but assume it is something to do with the way I am interpreting the POST variables.
Any help (with explanation if possible) gratefully appreciated.

Thanks

First of all i would suggest to make one column in the database for each checkbox (or add a new table for the checkboxes). Making arrays always give a pain in the ass afterwards. For example you cannot use one of them in a WHERE statement.

That said i could give you maybe one important hint. If a checkbox is unchecked then you will not find it in the $_POST array. So if you want to check if a checkbox is checked or not you will have to do like so:

if(isset($_POST['chk'][$memberid])) {
    // checkbox is checked
} else {
    // checkbox is not checked
}

Thanks for the reply.

Would rather not change the DB structure as it is used by several other modules that are working ok. Also it could be that, the Attend column could contain anything from 2 to 24 elements.

Cheers

The attendance table should have a separate row for each class_id, mem_id, and week number combination. Also, only data that exists should be stored, and the design should take care of creating new data when it doesn’t exist. This will allow you to directly find and manipulate each piece or group of data and to produce data for attendance reports directly in queries.

Next, since only checked checkboxes (and radio buttons) will be set in the form data, you should forget about the value=’…’ attribute and just detect if a checkbox isset() or not. Just use the week number as the second chk form field array index, instead of it being used as the value. To produce the form fields and process the form data, you need to loop over the member ids and the NoWeeks value. Also, you should not execute SELECT queries inside of loops. The appropriate attendance data should be queried for and fetched, storing it in an array using the mem_id as the array index, before the start of your html document. The member data should also be fetched into an appropriately named array variable before the start of the html document (per the reply in your previous thread, you should not have database specific code inside the html document.)

See the following example code for your current attendance data definition -

<?php

// make up some test data - this only has values for three weeks
// you would use a single query to get this data all at once, rather than executing a query inside of a loop
// the array index is the memid
$data = [];
$data[1] = '0,0,0';
$data[2] = '1,0,0';
$data[3] = '0,1,0';
$data[4] = '0,0,1';
$data[5] = '1,1,0';

// make up some member ids
$members = range(1,5);

$NoWeeks = 3;
// note: if the number of weeks is increased, this code works as expected and will add extra zeros/un-checked boxes on the end of the data


// form processing
if($_SERVER['REQUEST_METHOD'] == 'POST')
{
	// examine the submitted form data
	// echo '<pre>'; print_r($_POST); echo '</pre>';

	// loop over the class members
	foreach($members as $memid)
	{
		// produce attendance data for this member
		$att_array = [];
		// loop over the week numbers
		foreach(range(1,$NoWeeks) as $week_no)
		{
			// determine the checked/un-checked state
			$attend = isset($_POST['chk'][$memid][$week_no]) ? 1 : 0;
			$att_array[] = $attend; // add to current attendance data
		}
		// replace any existing data with the newly submitted data
		$data[$memid] = implode(',',$att_array);
		// at this point, you have the memid and the attendance string. you would execute a prepared (prepare it once before the start of the looping)
		// INSERT ... ON DUPLICATE KEY UPDATE ... query, for the composite class_id/mem_id value, since the record won't initially exist in the attendance table.
	}
}


// output the form in the html document
echo "<form method='post'><input type='submit'>";
echo "<table>";
// this is probably looping over members in a class
// while ($g_fetch = $a_query->fetch_array())
// loop over the class members
foreach($members as $memid)
{
	// don't run SELECT queries inside of loops. query for and fetch all data before the start of the html document
	//$memid = $g_fetch['mem_id'];
	//$at_query = $conn->query("SELECT Attend FROM `attendance` WHERE `mem_id`= '$memid' AND `class_id`= '$classid'");
	//$at_fetch = $at_query->fetch_array();
	// echo '<td> <input type="hidden" name="memberid[]" value="' . $memid . '" /></td>';

	//$att = explode(",", $at_fetch['at']); // this doesn't match the column name being SELECTed
	$att = []; // define an empty array
	// if there is existing data, use it
	if(isset($data[$memid]))
	{
		$att = explode(',',$data[$memid]);
	}

	echo "<tr>";
	echo "<td>$memid</td>";
	// loop over the week numbers
	foreach(range(1,$NoWeeks) as $week_no)
	{
		$checked = isset($att[$week_no - 1]) && $att[$week_no - 1] == 1 ? ' checked' : '';
		// echo '<td align="center"><input type="checkbox" " name="chk[' . $memid . '][]" value = "' . $chkval . '" ' ></td>';
		echo "<td align='center'><input type='checkbox' name='chk[$memid][$week_no]'$checked></td>";
	}
	echo "</tr>\n";
}
echo "</table>";
echo "</form>";
Sponsor our Newsletter | Privacy Policy | Terms of Service