Restoring Checkbox Selections from a Database

In a project I’m working on right now I’m making a dynamic reporting application. Basically I’m trying to take what I can do easily with a few lines of SQL and turn it into a usable interface for people who don’t know what they’re doing. It has summary reporting (group by), filters (wheres), etc. As you can imagine, this system has a plethora of fields that can be selected to be included in the report. No big deal, they’re all dynamically generated anyway. The catch is when I want to save a report, effectively restoring selections from a database.

It’s a straightforward approach really. What we do is store the names of the selected fields in the database in a text blob, and parse through it to restore the checks. It starts with storing them. The database structure is easy really, a report id, name and a text blob for the selected fields. Setting up the form to save a report is easy, just concatenate the field names with the delimiter of your choosing. I like to use ^ for separators because they don’t come up often in real-world usage. I know delimited text is ugly, but this is the best solution for this problem I think.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

<?php
	foreach ($_POST['fields'] as $key=>$field) {
		$cs_fields .= "$field^";
	}
 
	// remove the last ^
	$cs_fields = substr($cs_fields, 0, -1);
?>
 
<form action="?a=svreport" method="post">
<input type="hidden" name="fields" value="<?=$cs_fields?>">
 
<h2 style="margin: 10px;">Report Name: <input type="text" name="name" size="30"></h2>
<input type="submit" value="Save Report"> <input type="button" onClick="hideform()" value="Cancel">
 
</form>

I’ll spare you the simple form processing to add this to the database so we can get to the meat of restoring these checked fields with javascript. My fields are printed in the document in a loop like so:

1
2
3
4
5

// $fields should be populated from your database
$fields = array('field1', 'field2', 'field3');
foreach ($fields as $field) {
     echo '<input type="checkbox" name="fields[]" value="' . $field . '">';
}

So the value of the field is what we need to look at when updating. The function to run through the checkboxes and set them all up is as follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

function setReport() {
 
	// We generate a separate 'fields' variable for each report at runtime
        // this is kind of ugly, I know
<?
$q = mysql_query("SELECT * from reports");
while ($r = mysql_fetch_assoc($q)) {
	echo "var fields" . $r['report_id'] . " = '" . $r['report_fields'] . "';\n";
}
 
?>
 
	var selbox = document.getElementById('savedreports');
	reportId = selbox[selbox.selectedIndex].value;
 
        // eval concatenates the text inside before calling it as a variable, how convenient!
	var fields = eval("fields"+reportId);
 
	// thankfully we're only setting checkboxes, so we can cycle through all the checkboxes in the document and check them against the names in our fields
	var checks = document.getElementsByTagName('input');
 
	for (var i=0; i<checks.length; i++) {
		// reset everything
		checks[i].checked = false;
 
		// if the name of the checkbox is in our field lists, check it
		if (fields.indexOf(checks[i].value) >= 0) {
			checks[i].checked = true;
		}	
	}
}

To fire this event, I’ve set up a select box with all the saved reports in it like so:

1
2
3
4
5
6
7
8
9

echo '<select id="savedreports" onChange="setReport()">
	  <option value="0">Select Report</option>';
 
$q = mysql_query("SELECT * from reports");
while ($r = mysql_fetch_assoc($q)) {
	echo '<option value="' . $r['report_id'] . '">' . $r['report_name'] . '</option>';
}
 
echo '</select>';

Note that my implementation actually has several separate groups of fields and text values as well. This approach is nicely scalable because it doesn’t involve adding columns to the reports table for every field whose state you want to recall. I’d hesitate to use it for storing the values of text boxes, but for situations where the values and names of the fields are closely controlled by me and not exposed to user error, I think this will do quite nicely