Inserting records from multiple sets of checkboxes and adding a text field into the mix

First question - is your data such that you will only ever have three options? Rationalisation of the data is usually the right call but not always.

If there will always only be three options then you might consider adding the 3 checkbox fields as columns so that you would have

ID      textFieldInput    CheckboxOption1   CheckboxOption2   CheckboxOption3
1       some text 1       yes               no                yes

Select allOpen in new window

Next if you want to do it this way then you should consider how you build your form data. PHP provides some nice functionality around arrays you could utilise here

So using the following names for your controls
First batch
data[1][text]
data[1][checkbox1]
data[1][checkbox2]
data[1][checkbox3]
Second Batch
data[2][text]
data[2][checkbox1]
data[2][checkbox2]
data[2][checkbox3]

This gives you the following POST structure

Array
(
  [data] => Array
    (
      [1] => Array
      (
        [text] => abc
        [checkbox1] => 1
        [checkbox2] => 2
        [checkbox3] => 3
      )

      [2] => Array
      (
        [text] => a
        [checkbox1] => 1
      )

      [3] => Array
      (
        [text] => b
        [checkbox2] => 2
      )

      [4] => Array
      (
        [text] => c
        [checkbox3] => 3
      )

    )

)

Select allOpen in new window

NB: Only checked checkboxes are returned in the post so you have to take that into account.

Post back on the question regarding the DB and we can take it from there.

Hi Julian,

No, the number of checkboxes will change. At the moment they are populated from database records from another table. I might remove some, i might add more, they will change every now and then.

It's very basic, just an ID and description like this:

ID        Description

 1          some text
 2      some other text
 3       even more text

So you might have 4 checkboxes - but then all sets will have four?

What I am trying to get to here is a single record for all values makes the solution much simpler. We break the columns up into separate tables when we have an indeterminate number of items we want related to the parent record.

For example telephone number - we can put cell, home, work, fax - in the employee table or we can create a numbers table and relate that to the employee table on the employeeID. It is easier to get the employees data if it is all in one record - but if one employee has 1 number and another 10 then if we cater for 10 most of these fields will not be used and there is nothing to say we won't have an employee who has 11.

If the data is consistent (same for all records) and determinate (we know up front how many variations their will be) then a single record structure might work.

Having said that lets go with the way you are doing it now.

Here is a slightly more efficient option

	<form class="form" action="t2221.php" method="post">
		<div class="form-group">
			<input type="text" name="text[1]" />
			<input type="checkbox" name="checkbox[1][]" value="1" />
			<input type="checkbox" name="checkbox[1][]" value="2" />
			<input type="checkbox" name="checkbox[1][]" value="3" />
		</div>
		<div class="form-group">
			<input type="text" name="text[2]" />
			<input type="checkbox" name="checkbox[2][]" value="1" />
			<input type="checkbox" name="checkbox[2][]" value="2" />
			<input type="checkbox" name="checkbox[2][]" value="3" />
		</div>
		<div class="form-group">
			<input type="text" name="text[3]" />
			<input type="checkbox" name="checkbox[3][]" value="1" />
			<input type="checkbox" name="checkbox[3][]" value="2" />
			<input type="checkbox" name="checkbox[3][]" value="3" />
		</div>
		<div class="form-group">
			<input type="text" name="text[4]" />
			<input type="checkbox" name="checkbox[4][]" value="1" />
			<input type="checkbox" name="checkbox[4][]" value="2" />
			<input type="checkbox" name="checkbox[4][]" value="3" />
		</div>
		<button type="submit" class="btn btn-primary">Go</button>
	</form>

Select allOpen in new window

You can validate it like this
PHP

<?php
$text = isset($_POST['text']) ? $_POST['text'] : false;
$checkbox = isset($_POST['checkbox']) ? $_POST['checkbox'] : false;

if ($text && $checkbox) {
  foreach($text as $k => $v) {
    if (empty($v) || empty($checkbox[$k]) || count($checkbox[$k]) == 0) {
      echo "Batch[{$k}] is invalid<br/>";
    }
  }
}

Select allOpen in new window

Correct. Every set will be exactly the same i.e.: have one text field and however many checkbox options there are in the database. But even though that changes, every set will have 4, 5, 6 or however many options are available which is why I didn't want to go with the only one record option. Purely because it is unknown how many options there will be and how many options the user will pick. And like the ajax example you helped me with before, this will also have a lot of options, approximately 30.

I agree that one record would be easier so are you suggesting that I create more columns than I think I am going to need and if I don't use them all then i will just have empty columns?

So, I create 40 columns and if a user only selects 5 checkboxes and then only 5 columns are populated etc?

Just want to make sure I am on the same page.

For some reason my hours-ago comment may have gotten lost.

Make a Google search for the exact phrase "Should I Normalize My Database" and read the very interesting arguments on all sides of the concept.  When you're in school learning database design you will be taught to normalize because it's efficient and "right" from the perspective of a computer science teacher.  When you're running a rocket-to-the-moon startup, the venture capitalists will be shoving money at you and demanding high performance.  And when that happens, you will denormalize everything and just throw more hardware at the problem.

Thanks Julian,

I am trying to convert your query into a prepared statement but obviously not successfully as I am getting an error which says:

Warning: Invalid argument supplied for foreach()

The line is:

 foreach($checkbox[$k] as $o) {

Select allOpen in new window

And this is the code I am trying to use:

if (isset($_POST['submit']) && isset($_SESSION['times'])) {

    $text = isset($_POST['tank_no']) ? $_POST['tank_no'] : false;
    $checkbox = isset($_POST['defect']) ? $_POST['defect'] : false;

    if ($text && $checkbox) {
        foreach($text as $k => $v) {
            foreach($checkbox[$k] as $o) {
                $stmt = $link - > prepare("INSERT INTO `non_green` (`ps_id`, `tank_no`, `ng_id`) VALUES (?, ?, ?)");
                $stmt - > bind_param("iii", $_SESSION['last_id'], $v, $o);
                $stmt - > execute();
                $stmt - > close();

            }
        }
    }
}

Select allOpen in new window

@ Ray,

Haha, sadly I am still at the schooling phase and always try to normalize my database. For some reason, in my mind it does seem like the "right" way to do it. I would prefer to have venture capitalists throwing money at me though and not having to worry about it because it can be a real pain in the behind!

What does your html look like

What do you get if you add this to the top of your script - what does the output look lik.

echo "<pre>" . print_r($_POST, true) . "</pre>";

Select allOpen in new window

[tank_no] => Array
        (
            [1] => 123
            [2] => 456
        )

    [defect] => Array
        (
            [1] => 4
            [2] => 3
        )

    [submit] =>
 

the [defect] options do not match all the options I selected.

I just want to check that the problem isn't my form generation code.

I had to seperate the loops otherwise what was happening was that for every single checkbox there was a text field which is wrong. There should only be one text field and then all the checkbox options.

$row = $result->fetch_assoc();
			$non_green = $row['green_target'] - $row['actual_green'];
			$times = $non_green;
			$counter = 0;
			$item = 0;
			$_SESSION['times'] = $times;
			
			while($counter < $times) {
				$counter++;
				$item++;
				

	
	$stmt = $link->prepare("SELECT `ng_id`, `non_green_desc` FROM `non_green_reasons` ORDER BY `non_green_desc` ASC");
	$stmt->execute();
	$result = $stmt->get_result();
	$numRows = $result->num_rows;
	if($numRows > 0) {
		
		
		$textfield = <<<TF
		
					<div class="row">
						<div class="col-md-6">
							<div class="form-group">
								<label class="control-label">Unit $item</label>
								<input type="text" class="form-control" name="tank_no[$item]" placeholder="Production order number">
							</div>
						</div>
					</div>
		
	
TF;
		echo $textfield;
		
		while($row = $result->fetch_assoc()) {
			$ng_id = sanitize($row['ng_id']);
			$non_green_desc = sanitize($row['non_green_desc']);

			$checkboxes = <<<CB
			
						<div class="row">
						<div class="col-md-4">
							<div class="form-group">
								<div class="checkbox checkbox-danger checkbox-inline">
									<input type="checkbox" name="defect[$item]" value="{$ng_id}">
									<label for="checkbox6"> {$non_green_desc}</label>
								</div>
							</div>
						</div>
						</div>
CB;
			echo $checkboxes;

Select allOpen in new window

Look at this post to see how I defined the checkbox and text form fields.

If you don't put your checkboxes into a named array they are going to get mixed up together. Each collection of text / checkbox controls you have on your page must be uniquely defined by an index so that you can separate the values on the server. The checkbox controls you want in an array so you can loop over it.

Will check that out,

Just so you can see, this is how my html looks if I view the source. I thought it looked correctly but perhaps not:

<div class="row">
						<div class="col-md-6">
							<div class="form-group">
								<label class="control-label">Unit 1</label>
								<input type="text" class="form-control" name="tank_no[1]" placeholder="Production order number">
							</div>
						</div>
					</div>
<div class="row">
						<div class="col-md-4">
							<div class="form-group">
								<div class="checkbox checkbox-danger checkbox-inline">
									<input type="checkbox" name="defect[1]" value="5">
									<label for="checkbox6"> Option5</label>
								</div>
							</div>
						</div>
						</div>			
						<div class="row">
						<div class="col-md-4">
							<div class="form-group">
								<div class="checkbox checkbox-danger checkbox-inline">
									<input type="checkbox" name="defect[1]" value="9">
									<label for="checkbox6"> Option9</label>
								</div>
							</div>
						</div>
						</div>			
						<div class="row">
						<div class="col-md-4">
							<div class="form-group">
								<div class="checkbox checkbox-danger checkbox-inline">
									<input type="checkbox" name="defect[1]" value="1">
									<label for="checkbox6"> Option1</label>
								</div>
							</div>
						</div>
						</div>	

Select allOpen in new window

The next one has [2] instead of 1 and the following has [3]. So, it looks like the are displaying correctly.

Oh, wait. I see you have [] after the [1] etc.

Do you see the problem

<input type="checkbox" name="defect[1]" value="5">
<input type="checkbox" name="defect[1]" value="9">
<input type="checkbox" name="defect[1]" value="1">

Select allOpen in new window

You have the same name for all your checkboxes - so they are going to overwrite each other
Should be

<input type="checkbox" name="defect[1][]" value="5">
<input type="checkbox" name="defect[1][]" value="9">
<input type="checkbox" name="defect[1][]" value="1">

Select allOpen in new window

Now you will get your values in an array defect[1] => array(5,9,1);

yeah, I saw it just before you posted. Going to try it out.

Hmm. Okay, awesome. The checkbox values seem to be inserting but the text field value is wrong. In the database they are all "1". However, the print_r values are correct.

[tank_no] => Array
        (
            [1] => Array
                (
                    [0] => 123
                )

            [2] => Array
                (
                    [0] => 456
                )

        )

    [defect] => Array
        (
            [1] => Array
                (
                    [0] => 5
                    [1] => 9
                    [2] => 1
                )

            [2] => Array
                (
                    [0] => 5
                    [1] => 10
                    [2] => 3
                    [3] => 6
                )

        )

How are you getting this

[tank_no] => Array
        (
            [1] => Array
                (
                    [0] => 123
                )

            [2] => Array
                (
                    [0] => 456
                )

        )

Select allOpen in new window

From this?

<input type="text" class="form-control" name="tank_no[1]" placeholder="Production order number">

Select allOpen in new window

No, I changed it as per your suggestion

 tank_no[1][]

<input type="text" class="form-control" name="tank_no[$item][]" placeholder="Production order number">

Select allOpen in new window

No that was only for the checkboxes

You need multiple checkbox answers per text entry - so text entry stays as you had it - only the checkbox definition changed.

Aha! It works now that I removed the extra [] on the textfield. That's brilliant, thanks!

After you've searched for "normalize" also make a search for "cyclomatic complexity" and think about how many paths you must test to prove that you have an error-free implementation in an application designed this way.  The greatest benefit of test-driven development may be that it forces up to think in terms of logic and data structures that can be tested!

I think if I were trying to design this application, I would have a table of questions that contained a question, a textual answer, and a big pile of little columns for the checkbox values.  This table would be joined to another table that contained the interpretations of all the checkbox meanings.  You would be able to write much less logic, your flexibility would increase (add or change checkboxes just by changing the interpretation table) and the maintenance of the system would be easier.  Need more checkboxes?  Just add a few new columns to the question table, and corresponding interpretations.  A design like that might get you closer to being able to match your database column names to your form input controls.  When you can get a match on those fields (or a close enough match that a simple translation class works) your application suddenly becomes easy to maintain and verify through automated testing.

Just a thought...

Thanks Ray. That is certainly food for thought. Seems like there are many ways to skin a cat!

Argh. I have a problem with validation now. I tried to make it really simple by  just having one error message for not filling a text field or selecting a checkbox:

$text = isset($_POST['tank_no']) ? $_POST['tank_no'] : false;
$checkbox = isset($_POST['defect']) ? $_POST['defect'] : false;

if ($text && $checkbox) {
	foreach($text as $k => $v) {
		foreach($checkbox[$k] as $o) {
			$stmt = $link->prepare("INSERT INTO `non_green` (`ps_id`, `tank_no`, `ng_id`) VALUES (?, ?, ?)");
			$stmt->bind_param("iii", $_SESSION['last_id'], $v, $o);
			$stmt->execute();
			$stmt->close();
		}
	}
}
else {
	echo "Please fill in all text fields and select one reason for each field";
}

Select allOpen in new window

This only works if I don't enter any text or choose anything. Otherwise I get:

Notice: Undefined offset: 2
Warning: Invalid argument supplied for foreach()

The checkbox array contains all the checkboxes for all sections so if a section is missing a checkbox it won't show up unless no checkboxes were checked at all.

We can validate by checking that the number of arrays in the checkbox arrays matches the number of elements in the text array like so

if ($text && count($checkbox) == count($text)) {
	foreach($text as $k => $v) {
         ...
}
else {
   // Not all data was received.
}

Select allOpen in new window

That works! I can only hope (need a miracle more likely) to be as smart as you and Ray one day.

need a miracle more likely

Experience is the trump card.

A side effect in this case of using the hidden input is that it will mean you cannot use the count($checkbox) to determine if a checkbox was selected. The second drawback of this approach is that you are only adding choice records for the checkboxes that were checked which means in your insert loop you would loop through all checkboxes and have to check that they don't have a value that indicates they were not checked.

Given the direction you have taken with this implementation I don't think the hidden inputs are going to add value - if anything they will complicate the issue.

If you were going with the single record implementation I discussed in my first post and this post (and which Ray alluded to in a later post) - then the hidden field approach would make sense as it would allow you to insert a value into the correct column of the table without having to check if it is there.