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

I had this question after viewing form validation - make sure at least 1 checkbox is selected.

I also require a text field as well as the already discussed checkbox inputs so the form would look something like:


text field input

checkbox 1 checkbox 2 checkbox 3

--------------------------------------------------------------

text field input

checkbox 1 checkbox 2 checkbox 3

--------------------------------------------------------------

text field input

checkbox 1 checkbox 2 checkbox 3


The fun part is trying to insert all of this into the database.

The way I have the table now (open to suggestions) is (fictitious names to illustrate what each column is)

ID      textFieldInput    CheckboxOption

 1         some text 1          checked1
 2         some text 1          checked2
 3         some text 2          checked1
 4         some text 2          checked2
 5         some text 2          checked3

So, this means that the user checked 2 checkboxes for text field one, and checked 3 options for text field 2. I hope that makes sense.

However, this is just the design. I can't actually get the data to insert into the database like that. I am using a foreach loop for the text field validation and a while loop for the checkbox validation. I don't know how to combine the two and perform the insert.

if(isset($_POST['submit']) && isset($_SESSION['times'])) {
		
		$message = "";
		
		foreach($_POST['unit_no'] as $unitNo) {
			if(empty($unitNo)) {
				
				$message .= "Unit number cannot be empty<br/>";
				break;
			}
			
		}
		
			$counter = 0;
		
		while($counter < $_SESSION['times']) {
			
			$counter++;
				 if (empty($_POST['defect'][$counter])) {
					 
					$message .= "Please select at least one option for unit " . $counter . "<br/>";
					 
			}
		}
		
		if($message) {
			
			echo error_message($message);
			
		} else {
			
			// loops here to insert records
			}
		}

Open in new window

LVL 1
Black SulfurAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Julian HansenCommented:
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

Open 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
      )

    )

)

Open 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.
0
Black SulfurAuthor Commented:
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
0
Julian HansenCommented:
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>

Open 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/>";
    }
  }
}

Open in new window

0
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Black SulfurAuthor Commented:
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.
0
Ray PaseurCommented:
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.
0
Julian HansenCommented:
For some reason my hours-ago comment may have gotten lost.
Ditto.

The following is a slightly modified version of my earlier post in terms of how to use the form arrays

Here is the PHP code for adding the data to the database - you just need to add the DB connection and execution parts
	<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>

Open in new window

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

if ($text && $checkbox) {
  $query = "INSERT INTO theTable (textFieldInput, CheckboxOption) VALUES";
  foreach($text as $k => $v) {
     foreach($checkbox[$k] as $o) {
         $query .= "('{$v}', '{$o}'),";
     }
  }
}
$query = trim($query, ',');
echo $query;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Black SulfurAuthor Commented:
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) {

Open 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();

            }
        }
    }
}

Open in new window

0
Black SulfurAuthor Commented:
@ 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!
0
Julian HansenCommented:
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>";

Open in new window

0
Black SulfurAuthor Commented:
[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.
0
Black SulfurAuthor Commented:
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;

Open in new window

0
Julian HansenCommented:
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.
0
Black SulfurAuthor Commented:
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>	

Open in new window


The next one has [2] instead of 1 and the following has [3]. So, it looks like the are displaying correctly.
0
Black SulfurAuthor Commented:
Oh, wait. I see you have [] after the [1] etc.
0
Julian HansenCommented:
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">

Open 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">

Open in new window


Now you will get your values in an array defect[1] => array(5,9,1);
0
Black SulfurAuthor Commented:
yeah, I saw it just before you posted. Going to try it out.
0
Black SulfurAuthor Commented:
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
                )

        )
0
Julian HansenCommented:
How are you getting this
[tank_no] => Array
        (
            [1] => Array
                (
                    [0] => 123
                )

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

        )

Open in new window

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

Open in new window

0
Black SulfurAuthor Commented:
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">

Open in new window

0
Julian HansenCommented:
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.
0
Black SulfurAuthor Commented:
Aha! It works now that I removed the extra [] on the textfield. That's brilliant, thanks!
0
Ray PaseurCommented:
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...
0
Black SulfurAuthor Commented:
Thanks Ray. That is certainly food for thought. Seems like there are many ways to skin a cat!
0
Black SulfurAuthor Commented:
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";
}

Open 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()
0
Julian HansenCommented:
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.
}

Open in new window

0
Black SulfurAuthor Commented:
That works! I can only hope (need a miracle more likely) to be as smart as you and Ray one day.
0
Ray PaseurCommented:
One thing that can make checkboxes a little easier to deal with is a hidden input control, immediately before the checkbox, that has the same name= attribute as the checkbox, and a value= attribute that gives a "default" for the input control.  If the checkbox is not checked, you'll get the default value.  If the checkbox is checked, it will override the default value with the checkbox value.  This is PHP-unique, in that PHP presents the request variables in associative arrays, and in PHP associative arrays, a second identical key will cause the associated value to be overwritten (handy feature).  Examples here:
https://www.experts-exchange.com/articles/5450/Common-Sense-Examples-Using-Checkboxes-with-HTML-JavaScript-and-PHP.html
0
Julian HansenCommented:
need a miracle more likely
Experience is the trump card.
0
Julian HansenCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.