php - inserting multiple dynamic checkboxes into mySQL
one text
My people table has rows which need a value of 1 if checked and 0 if not. I used checkboxes to collect that in my form. I also wanted to allow the user to add more fields so if they can multiple entries can be made at a time.
I scraped out how to collect the unchecked boxes by creating a hidden input for each checkbox by creating a input type=hidden before the checkboxes, so I just copied the whole table row for the jQuery to append.
Unfortunately, the checkbox values mess up on every other row:
(the lName column specifies the input on the following boolean columns: Y should be a check and N is blank)
The first row is correct, but I don't know why the appended rows are being screwed.
<?php
include_once("connections/db.inc.php");
if(isset($_POST['submit'])){
//insert people
$lName=$_POST['lName'];
$fName= $_POST['fName'];
$mName= $_POST['mName'];
$suffixName= $_POST['suffixName'];
$gender= $_POST['gender'];
$birthday= $_POST['birthday'];
$phoneNumber= $_POST['phoneNumber'];
$civilStatus= $_POST['civilStatus'];
$isHeadofFamily=$_POST['isHeadOfFamily'];
$isEmployed=$_POST['isEmployed'];
$isSelfEmployedInBusiness=$_POST['isSelfEmployedInBusiness'];
$isSelfEmployedInInformalSector=$_POST['isSelfEmployedInInformalSector'];
$isSoloParent=$_POST['isSoloParent'];
$isSeniorCitizen=$_POST['isSeniorCitizen'];
$isPWD = $_POST['isPWD'];
$relationToHeadOfFamily= $_POST['relationToHeadOfFamily'];
foreach ($lName as $key => $value) {
$sql = "INSERT INTO `people` (`addressId`, `lName`, `fName`, `mName`, `suffixName`, `gender`, `birthday`, `phoneNumber`, `civilStatus`, `isHeadOfFamily`, `isEmployed`, `isSelfEmployedInBusiness`, `isSelfEmployedInInformalSector`, `isSoloParent`, `isSeniorCitizen`, `isPWD`, `relationToHeadOfFamily`)
VALUES (:lastId,:a2,:b2,:c2,:d2,:e2,:f2,:g2,:h2,:i2,:j2,:k2,:l2,:m2,:n2,:o2,:p2)";
$stmt = $db->prepare($sql);
$stmt->bindParam(":lastId",$lastId);
$stmt->bindParam(":a2",$lName[$key]);
$stmt->bindParam(":b2",$fName[$key]);
$stmt->bindParam(":c2",$mName[$key]);
$stmt->bindParam(":d2",$suffixName[$key]);
$stmt->bindParam(":e2",$gender[$key]);
$stmt->bindParam(":f2",$birthday[$key]);
$stmt->bindParam(":g2",$phoneNumber[$key]);
$stmt->bindParam(":h2",$civilStatus[$key]);
$stmt->bindParam(":i2",$isHeadofFamily[$key]);
$stmt->bindParam(":j2",$isEmployed[$key]);
$stmt->bindParam(":k2",$isSelfEmployedInBusiness[$key]);
$stmt->bindParam(":l2",$isSelfEmployedInInformalSector[$key]);
$stmt->bindParam(":m2",$isSoloParent[$key]);
$stmt->bindParam(":n2",$isSeniorCitizen[$key]);
$stmt->bindParam(":o2",$isPWD[$key]);
$stmt->bindParam(":p2",$relationToHeadOfFamily[$key]);
$stmt->execute();
}
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>BRGY</title>
<script>
$(function() {
var html = '<tr> <td><input type="text" name="lName[]" id="lName" ></td> <td><input type="text" name="fName[]" id="fName" ></td> <td><input type="text" name="mName[]" id="mName"></td> <td><input type="text" name="suffixName[]" id="suffixName" ></td> <td> <select name="gender[]" id="gender"> <option value="Male">Male</option> <option value="Female">Female</option> </select> </td> <td><input type="date" name="birthday[]" id="birthday" ></td> <td><input type="text" name="phoneNumber[]" id="phoneNumber" ></td> <td> <select name="civilStatus[]" id="civilStatus"> <option value="Single">Single</option> <option value="Married">Married</option> <option value="Widowed">Widowed</option> </select> </td> <td><input type="checkbox" name="isHeadOfFamily[]" id="isHeadOfFamily" value="1"> </td> <input type="hidden" name="isHeadOfFamily[]" id="isHeadOfFamilyHIDDEN" value="0"> <td><input type="checkbox" name="isEmployed[]" id="isEmployed" value="1"> </td> <input type="hidden" name="isEmployed[]" id="isEmployedHIDDEN" value="0"> <td><input type="checkbox" name="isSelfEmployedInBusiness[]" id="isSelfEmployedInBusiness" value="1"> </td> <input type="hidden" name="isSelfEmployedInBusiness[]" id="isSelfEmployedInBusinessHIDDEN" value="0"> <td><input type="checkbox" name="isSelfEmployedInInformalSector[]" id="isSelfEmployedInInformalSector" value="1"> </td> <input type="hidden" name="isSelfEmployedInInformalSector[]" id="isSelfEmployedInInformalSectorHIDDEN" value="0"> <td><input type="checkbox" name="isSoloParent[]" id="isSoloParent" value="1"> </td> <input type="hidden" name="isSoloParent[]" id="isSoloParentHIDDEN" value="0"> <td><input type="checkbox" name="isSeniorCitizen[]" id="isSeniorCitizen" value="1"> </td> <input type="hidden" name="isSeniorCitizen[]" id="isSeniorCitizenHIDDEN" value="0"> <td><input type="checkbox" name="isPWD[]" id="isPWD" value="1"> </td> <input type="hidden" name="isPWD[]" id="isPWDHIDDEN" value="0"> <td> <select name="relationToHeadOfFamily[]" id="relationToHeadOfFamily"> <option value="Spouse">Spouse</option> <option value="Child">Child</option> <option value="Sibling">Sibling</option> <option value="Parent">Parent</option> <option value="None">None</option> </select> </td> <td><button type="button" name="addmore" id="addmore">Add More</button></td> </tr>';
var x = 1;
$("#addmore").click(function(){
$("#table_input-people").append(html);
});
$("#table_input-people").on('click','#remove',function(){
$(this).closest('tr').remove();
});
});
</script>
</head>
<body>
<?php include_once("nav.php") ?>
<div>
<form action="" method="post">
<table class="table table-bordered" id="table_input-people">
<tr>
<th>Last Name</th>
<th>First Name</th>
<th>Middle Name</th>
<th>Suffix</th>
<th>Gender</th>
<th>Birthday</th>
<th>Phone Number</th>
<th>Civil Status</th>
<th>Are you the head of family?</th>
<th>Employed</th>
<th>Self Employed in Business</th>
<th>Self Employed in Informal Sector</th>
<th>Solo Parent</th>
<th>Senior Citizen</th>
<th>PWD</th>
<th>Relation to Head of Family</th>
</tr>
<tr>
<td><input type="text" name="lName[]" id="lName" ></td>
<td><input type="text" name="fName[]" id="fName" ></td>
<td><input type="text" name="mName[]" id="mName"></td>
<td><input type="text" name="suffixName[]" id="suffixName" ></td>
<td>
<select name="gender[]" id="gender">
<option value="Male">Male</option>
<option value="Female">Female</option>
</select>
</td>
<td><input type="date" name="birthday[]" id="birthday" ></td>
<td><input type="text" name="phoneNumber[]" id="phoneNumber" ></td>
<td>
<select name="civilStatus[]" id="civilStatus">
<option value="Single">Single</option>
<option value="Married">Married</option>
<option value="Widowed">Widowed</option>
</select>
</td>
<td><input type="checkbox" name="isHeadOfFamily[]" id="isHeadOfFamily" value="1"> </td>
<input type="hidden" name="isHeadOfFamily[]" id="isHeadOfFamilyHIDDEN" value="0">
<td><input type="checkbox" name="isEmployed[]" id="isEmployed" value="1"> </td>
<input type="hidden" name="isEmployed[]" id="isEmployedHIDDEN" value="0">
<td><input type="checkbox" name="isSelfEmployedInBusiness[]" id="isSelfEmployedInBusiness" value="1"> </td>
<input type="hidden" name="isSelfEmployedInBusiness[]" id="isSelfEmployedInBusinessHIDDEN" value="0">
<td><input type="checkbox" name="isSelfEmployedInInformalSector[]" id="isSelfEmployedInInformalSector" value="1"> </td>
<input type="hidden" name="isSelfEmployedInInformalSector[]" id="isSelfEmployedInInformalSectorHIDDEN" value="0">
<td><input type="checkbox" name="isSoloParent[]" id="isSoloParent" value="1"> </td>
<input type="hidden" name="isSoloParent[]" id="isSoloParentHIDDEN" value="0">
<td><input type="checkbox" name="isSeniorCitizen[]" id="isSeniorCitizen" value="1"> </td>
<input type="hidden" name="isSeniorCitizen[]" id="isSeniorCitizenHIDDEN" value="0">
<td><input type="checkbox" name="isPWD[]" id="isPWD" value="1"> </td>
<input type="hidden" name="isPWD[]" id="isPWDHIDDEN" value="0">
<td>
<select name="relationToHeadOfFamily[]" id="relationToHeadOfFamily">
<option value="Spouse">Spouse</option>
<option value="Child">Child</option>
<option value="Sibling">Sibling</option>
<option value="Parent">Parent</option>
<option value="None">None</option>
</select>
</td>
<td><button type="button" name="addmore" id="addmore">Add More</button></td>
</tr>
</table>
<button type ="submit" name="submit">Submit</button>
</form>
</div>
</body>
</html>