Insert multiple records into database from checkboxed items in form

General PHP Help
#1

I have created this page where users can check off boxes in a form for all the items they want to add. Unfortunately, it isn’t working correctly and it is adding some of the items checked but, also ones that were not checked.

Here is the Insert Record Code:

<?php
if (isset($_POST["submit"]) || isset($_POST["submit_x"])) {
$agreement_typesID = $_POST['agreement_typesID'];
$softwareID = $_POST['softwareID'];
$participatingID = $_POST['participatingID'];
$grade_level = $_POST['grade_level'];
$content_area = $_POST['content_area'];
$holding = 'N';
$statusID = $_POST['statusID'];
$districtID = $_GET['districtID'];
$originating_dataID = $_POST['dataID'];
$date_approved = $_POST['date_approved'];
$originating_addendumID = $_POST['originating_addendumID'];
$supplemental_info = $_POST['supplemental_info'];
$ric_service = $_POST['ric_service'];
$boces_service = $_POST['boces_service'];
$msa_start_date = $_POST['msa_start_date'];
$msa_end_date = $_POST['msa_end_date'];
$msa_autorenewal = $_POST['msa_autorenewal'];
$boces_instructional_program = $_POST['boces_instructional_program'];
$originator = 'N';
$date_expired = $_POST['date_expired'];
$restrictions = $_POST['restrictions'];
$dpa_restrictions = $_POST['dpa_restrictions'];
$cost = $_POST['cost'];
$paid_by = $_POST['paid_by'];
$media_release = $_POST['media_release'];
$consortiumID = $_POST['consortiumID'];
$rules_of_engagement = $_POST['rules_of_engagement'];
$signed_agreement_file = $_POST['signed_agreement_file'];
foreach(array_keys($_POST['dataID']) as $key) {
$agreement_typesID_value = $agreement_typesID[$key];
$softwareID_value = $softwareID[$key];
$grade_level_value = $grade_level[$key];
$date_now_value = $date_now[$key];
$content_area_value = $content_area[$key];
$statusID_value = $statusID[$key];
$originating_dataID_value = $originating_dataID[$key];
$date_approved_value = $date_approved[$key];
$originating_addendumID_value = $originating_addendumID[$key];
$supplemental_info_value = $supplemental_info[$key];
$ric_service_value = $ric_service[$key];
$boces_service_value = $boces_service[$key];
$msa_start_date_value = $msa_start_date[$key];
$msa_end_date_value = $msa_end_date[$key];
$msa_autorenewal_value = $msa_autorenewal[$key];
$boces_instructional_program_value = $boces_instructional_program[$key];
$msa_autorenewal_value = $msa_autorenewal[$key];
$date_expired_value = $date_expired[$key];
$restrictions_value = $restrictions[$key];
$dpa_restrictions_value = $dpa_restrictions[$key];
$cost_value = $cost[$key];
$paid_by_value = $paid_by[$key];
$media_release_value = $media_release[$key];
$participatingID_value = $participatingID[$key];
$consortiumID_value = $consortiumID[$key];
$rules_of_engagement_value = $rules_of_engagement[$key];
$signed_agreement_file_value = $signed_agreement_file[$key];
  $InsertQuery = new WA_MySQLi_Query($sdpc_i);
  $InsertQuery->Action = "insert";
  $InsertQuery->Table = "data";
  $InsertQuery->bindColumn("boces_service", "s", "".((isset($boces_service_value))?$boces_service_value:"")  ."", "WA_DEFAULT");
  $InsertQuery->bindColumn("ric_service", "s", "".((isset($ric_service_value))?$ric_service_value:"")  ."", "WA_DEFAULT");
   $InsertQuery->bindColumn("consortiumID", "i", "".((isset($consortiumID_value))?$consortiumID_value:"")  ."", "WA_DEFAULT");
  $InsertQuery->bindColumn("boces_instructional_program", "s", "".((isset($boces_instructional_program_value))?$boces_instructional_program_value:"")  ."", "WA_DEFAULT");
  $InsertQuery->bindColumn("districtID", "i", "".((isset($districtID))?$districtID:"")  ."", "WA_DEFAULT");
   $InsertQuery->bindColumn("date_added", "t", "".((isset($date_now_value))?$date_now_value:"")  ."", "WA_DEFAULT");
  $InsertQuery->bindColumn("supplemental_info", "s", "".((isset($supplemental_info_value))?$supplemental_info_value:"")  ."", "WA_DEFAULT");
  $InsertQuery->bindColumn("softwareID", "i", "".((isset($softwareID_value))?$softwareID_value:"")  ."", "WA_DEFAULT");
  $InsertQuery->bindColumn("holding", "s", "".((isset($holding))?$holding:"")  ."", "WA_NULL");
   $InsertQuery->bindColumn("originating_dataID", "i", "".((isset($originating_dataID_value))?$originating_dataID_value:"")  ."", "WA_DEFAULT");
   $InsertQuery->bindColumn("originating_addendumID", "i", "".((isset($originating_addendumID_value))?$originating_addendumID_value:"")  ."", "WA_DEFAULT");
   $InsertQuery->bindColumn("agreement_typesID", "i", "".((isset($agreement_typesID_value))?$agreement_typesID_value:"")  ."", "WA_DEFAULT");
 $InsertQuery->bindColumn("signed_agreement_file", "s", "".((isset($signed_agreement_file_value))?$signed_agreement_file_value:"")  ."", "WA_NULL");
   $InsertQuery->bindColumn("grade_level", "s", "".((isset($grade_level_value))?$grade_level_value:"")  ."", "WA_DEFAULT");
    $InsertQuery->bindColumn("content_area", "is", "".((isset($content_area_value))?$content_area_value:"")  ."", "WA_DEFAULT");
   $InsertQuery->bindColumn("statusID", "i", "".((isset($statusID_value))?$statusID_value:"")  ."", "WA_DEFAULT");
  $InsertQuery->bindColumn("date_approved", "t", "".((isset($date_approved_value))?$date_approved_value:"")  ."", "WA_DEFAULT");
   $InsertQuery->bindColumn("msa_start_date", "t", "".((isset($msa_start_date_value))?$msa_start_date_value:"")  ."", "WA_DEFAULT");
    $InsertQuery->bindColumn("msa_end_date", "t", "".((isset($msa_end_date_value))?$msa_end_date_value:"")  ."", "WA_DEFAULT");
     $InsertQuery->bindColumn("msa_autorenewal", "s", "".((isset($msa_autorenewal_value))?$msa_autorenewal_value:"")  ."", "WA_DEFAULT");
  $InsertQuery->bindColumn("date_expired", "t", "".((isset($date_expired_value))?$date_expired_value:"")  ."", "WA_DEFAULT");
   $InsertQuery->bindColumn("restrictions", "s", "".((isset($restrictions_value))?$restrictions_value:"")  ."", "WA_NULL");
   $InsertQuery->bindColumn("dpa_restrictions", "s", "".((isset($dpa_restrictions_value))?$dpa_restrictions_value:"")  ."", "WA_NULL");
  $InsertQuery->bindColumn("cost", "s", "".((isset($cost_value))?$cost_value:"")  ."", "WA_NULL");
  $InsertQuery->bindColumn("paid_by", "s", "".((isset($paid_by_value))?$paid_by_value:"")  ."", "WA_NULL");
  $InsertQuery->bindColumn("media_release", "s", "".((isset($media_release_value))?$media_release_value:"")  ."", "WA_NULL");
  $InsertQuery->bindColumn("rules_of_engagement", "s", "".((isset($rules_of_engagement_value))?$rules_of_engagement_value:"")  ."", "WA_NULL");
  $InsertQuery->bindColumn("originator", "s", "".((isset($originator))?$originator:"")  ."", "WA_DEFAULT");
  $InsertQuery->saveInSession("");
  $InsertQuery->execute();
  $InsertGoTo = "";
  if (function_exists("rel2abs")) $InsertGoTo = $InsertGoTo?rel2abs($InsertGoTo,dirname(__FILE__)):"";
  $InsertQuery->redirect($InsertGoTo);
  $status='Added';
  $UpdateQuery = new WA_MySQLi_Query($sdpc_i);
  $UpdateQuery->Action = "update";
  $UpdateQuery->Table = "ny_participating_data";
  $UpdateQuery->bindColumn("status", "s", "".((isset($status))?$status:"")  ."", "WA_IGNORE");
   $UpdateQuery->bindColumn("date_added", "t", "".((isset($date_now))?$date_now:"")  ."", "WA_DEFAULT");
   $UpdateQuery->addFilter("participatingID", "=", "i", "".($participatingID_value)  ."");
  $UpdateQuery->execute();
  $UpdateGoTo = "";
  if (function_exists("rel2abs")) $UpdateGoTo = $UpdateGoTo?rel2abs($UpdateGoTo,dirname(__FILE__)):"";
  $UpdateQuery->redirect($UpdateGoTo);
}
header("Location: ny_district_selector.php?state=$state&districtID=$districtID");
}
?>

Here is the form:

<form method="post" enctype="multipart/form-data">
     <button type="submit" class="btn btn-danger btn-lg mb-3" name="submit" id="submit">Add All Selected DPAs</button>
     <input type="button" class="btn-sm btn-outline-primary" onclick='selectAll()' value="SELECT All"/>
		<input type="button" class="btn-sm btn-outline-primary" onclick='UnSelectAll()' value="UnSELECT All"/>
     <table id="example" class="table table-responsive table-striped table-bordered" width="100%" border="0" cellspacing="0">
      <thead>
        <tr>
        <th></th>
          <th align="left" width="20%" valign="top"><strong><font size="-1">Date Added</font></strong></th>
          <th align="left" width="20%" valign="top"><strong><font size="-1">Resource Name</font></strong></th>
            <th align="left" width="20%" valign="top"><strong><font size="-1">Originating BOCES/District</font></strong></th>
            <th align="left" width="20%" valign="top"><strong><font size="-1">Consortium Tag</font></strong></th>
            <th align="left" width="20%" valign="top"><strong><font size="-1">Agreement Type</font></strong></th>
             <th align="left" width="20%" valign="top"><strong><font size="-1">Agreement</font></strong></th>
              <th align="left" width="20%" valign="top"><strong><font size="-1">Dates</font></strong></th>
        </tr>
      </thead>
      <tbody>
        <?php
$wa_startindex = 0;
while(!$participating_dpas->atEnd()) {
  $wa_startindex = $participating_dpas->Index;
?>
        <tr>
        <td>
        <input type="checkbox" value="<?php echo($participating_dpas->getColumnVal("theID")); ?>" id="dataID" name="dataID[]" class="form-control">
        <input type="hidden" name="softwareID[]" id="softwareID" value="<?php echo($participating_dpas->getColumnVal("thesoftwareID")); ?>">
<input type="hidden" name="participatingID[]" id="participatingID" value="<?php echo($participating_dpas->getColumnVal("participatingID")); ?>">
<input type="hidden" name="statusID[]" id="statusID" value="1">
<input type="hidden" name="agreement_typesID[]" id="agreement_typesID" value="<?php echo($participating_dpas->getColumnVal("agreement_typesID")); ?>">
<input type="hidden" name="grade_level[]" id="grade_level" value="<?php echo($participating_dpas->getColumnVal("grade_level")); ?>">
<input type="hidden" name="content_area[]" id="content_area" value="<?php echo($participating_dpas->getColumnVal("content_area")); ?>">
<input type="hidden" name="date_approved[]" id="date_approved" value="<?php echo($participating_dpas->getColumnVal("date_approved")); ?>">
<input type="hidden" name="date_expired[]" id="date_expired" value="<?php echo($participating_dpas->getColumnVal("date_expired")); ?>">
<input type="hidden" name="originating_addendumID[]" id="originating_addendumID" value="<?php echo($participating_dpas->getColumnVal("addendumID")); ?>">
<input type="hidden" name="supplemental_info[]" id="supplemental_info" value="<?php echo($participating_dpas->getColumnVal("supplemental_info")); ?>">
<input type="hidden" name="ric_service[]" id="ric_service" value="<?php echo($participating_dpas->getColumnVal("ric_service")); ?>">
<input type="hidden" name="boces_service[]" id="boces_service" value="<?php echo($participating_dpas->getColumnVal("boces_service")); ?>">
<input type="hidden" name="msa_start_date[]" id="msa_start_date" value="<?php echo($participating_dpas->getColumnVal("msa_start_date")); ?>">
<input type="hidden" name="msa_end_date[]" id="msa_end_date" value="<?php echo($participating_dpas->getColumnVal("msa_end_date")); ?>">
<input type="hidden" name="msa_autorenewal[]" id="msa_autorenewal" value="<?php echo($participating_dpas->getColumnVal("msa_autorenewal")); ?>">
<input type="hidden" name="boces_instructional_program[]" id="boces_instructional_program" value="<?php echo($participating_dpas->getColumnVal("boces_instructional_program")); ?>">
<input type="hidden" name="restrictions[]" id="restrictions" value="<?php echo($participating_dpas->getColumnVal("restrictions")); ?>">
<input type="hidden" name="dpa_restrictions[]" id="dpa_restrictions" value="<?php echo($participating_dpas->getColumnVal("dpa_restrictions")); ?>">
<input type="hidden" name="cost[]" id="cost" value="<?php echo($participating_dpas->getColumnVal("cost")); ?>">
<input type="hidden" name="paid_by[]" id="paid_by" value="<?php echo($participating_dpas->getColumnVal("paid_by")); ?>">
<input type="hidden" name="media_release[]" id="media_release" value="<?php echo($participating_dpas->getColumnVal("media_release")); ?>">
<input type="hidden" name="consortiumID[]" id="consortiumID" value="<?php echo($participating_dpas->getColumnVal("consortiumID")); ?>">
<input type="hidden" name="rules_of_engagement[]" id="rules_of_engagement" value="<?php echo($participating_dpas->getColumnVal("rules_of_engagement")); ?>">
<input type="hidden" name="signed_agreement_file[]" id="signed_agreement_file" value="<?php echo($participating_dpas->getColumnVal("signed_agreement_file")); ?>">
</td>
</td>
        <td><?php echo($participating_dpas->getColumnVal("date_suggested")); ?></td>
        <td><?php echo($participating_dpas->getColumnVal("software_name")); ?></td>
        <td><?php echo($participating_dpas->getColumnVal("district_name")); ?></td>
         <td><?php echo($participating_dpas->getColumnVal("name")); ?></td>
        <td><?php echo($participating_dpas->getColumnVal("agreement_name")); ?></td>
        <td><?php if ($participating_dpas->getColumnVal("signed_agreement_file") != NULL) {?><a href="agreements/<?php echo($participating_dpas->getColumnVal("signed_agreement_file")); ?>" target=_blank">Download File</a><br><?php }?><?php if ($participating_dpas->getColumnVal("supplemental_info") != NULL) {?><a href="supplemental_info/<?php echo($participating_dpas->getColumnVal("supplemental_info")); ?>" target=_blank">Supplemental Info</a><?php }?></td>
         <td>Date Approved: <?php if ("".($participating_dpas->getColumnVal("date_approved"))  ."" != "1969-12-31" and "".($participating_dpas->getColumnVal("date_approved"))  ."" != NULL) {  ?>
         <?php echo date("m/d/Y", strtotime($participating_dpas->getColumnVal("date_approved"))); ?>
<?php } ?><br>Date Expired: <?php if ("".($participating_dpas->getColumnVal("date_expired"))  ."" != "1969-12-31" and "".($participating_dpas->getColumnVal("date_expired"))  ."" != NULL) {  ?>
<?php echo date("m/d/Y", strtotime($participating_dpas->getColumnVal("date_expired"))); ?>
<?php } ?></td>
        </tr>
        <?php
  $participating_dpas->moveNext();
}
$participating_dpas->moveFirst(); //return RS to first record
unset($wa_startindex);
unset($wa_repeatcount);
?>
        </tbody>
      </table></form>

Any help would be greatly appreciated. Thank you in advance.

#2

The most immediate problem is that only checked checkboxes are submitted and the zero-referenced dataID array keys have no relationship to the hidden fields. To make this work, you would need to use the dataID value as the hidden field’s array keys, then loop over the submitted dataID values to access the correct hidden field values.

However, don’t waste your time correcting this wall of code, there are other things that need to be fixed, so you would be better off simplifying everything, then fixing the remaining problems all at once.

You don’t need to and should not be passing all this data through hidden fields. All form data needs to be trimmed, then validated before using it. You know what all this data is based on the submitted dataID values. This is the only thing you should submit from the form. This will eliminate most of the code. When the checked dataID values are submitted, if you in fact needed all this data to be inserted, instead of just inserting the ids, you would query in the post method form processing code to get the data related to the submitted set of ids. Actually you would just use a single INSERT … SELECT query, getting the records matching the submitted set of ids, but I don’t know if the database wrapper you are using will let you do this.

Next, if you have more than 2-3 form fields, you should be dynamically validating and processing the data and dynamically building the form, by defining an array with the expected fields and the attributes used throughout the logic, then loop over this defining array to control what general purpose code does, instead of writing out line after line of repetitive code for every field.

Here’s a list of other points I noticed while figuring out what the code is doing -

  1. Don’t copy variables to other variables for nothing. This is a waste of typing. Instead, keep the form data as a set, in a php array variable, then operate on elements in this array variable throughout the rest of the code.
  2. The hidden fields will be set. there’s no need for any isset() logic.
  3. Every redirect needs an exit/die statement to stop php code execution. The current code runs all the rest of the code on the page after the header() statement.
  4. All the id=’…’ attributes inside the while(){} loop code must be unique. Since it appears you are not using these in the browser, simply leave them out of the code.
  5. All dynamic values being output in the browser need to have htmlentities() applied to them to prevent any html entity, even a ', from being able to break the html markup.
  6. If you use php’s short-open-print tag <?= it will save a lot of typing.
  7. You can leave out the closing ; right before a closing php tag ?>, saving some typing.
  8. The redirect you perform at the completion of the post method form processing code needs to be to the exact same URL of the current page to cause a get request for that page. This will prevent the browser from trying to resubmit the form data should that page get browsed back to or reloaded.
Sponsor our Newsletter | Privacy Policy | Terms of Service