vendredi 23 septembre 2016

Multiple Checkboxes Columns Updating Multiple SQL Rows

I've never had issues programming PHP SQL updates when updating multiple rows of data with multiple columns, but for some reason this section of coding is killing me as it is nothing more than checkboxes for the end user to check and/or uncheck. I've include portions of the code below as the full code is over 50+ columns wide of checkboxes and can range from 1-200 rows.

Everything displays/runs the way its supposed to but the updating isnt functioning properly ... example: if there is 5 rows (each with a unique trackingid) and I check the checkbox for the 4th row on update it says that the 1st checkbox was checked and NOT the 4th row.

<?php if (!isset($_POST['trackingid'])) { ?>
        
        <form action="<?php echo $_SERVER['REQUEST_URI']; ?>" method="post" enctype="multipart/form-data" class="form-horizontal" id="form"><input type="hidden" name="id" value="<?php echo $id; ?>">

        <table class="table table-striped">
            <thead>
                <tr>
                    <td class="rotate-alt"></td>
                    <td class="rotate-alt2"><div><span></span></div></td>
                    <td class="rotate-alt2"><div><span></span></div></td>
                    <td class="rotate-alt2"><div><span></span></div></td>
                    <td class="rotate-alt2"><div><span></span></div></td>
                    <td class="rotate-alt2"><div><span></span></div></td>
                    <td class="rotate-alt"><div><span>Glass</span></div></td>
                    <td class="rotate-alt"><div><span>Glass - SDL</span></div></td>
                    <td class="rotate-alt"><div><span>Mill - S&amp;R</span></div></td>
                </tr>
             </thead>
          <tbody>
            
            <?php $result = sqlsrv_query($conn, "SELECT id, [Order Number], [Door Number], [Qty Display], [Interior or Exterior], [Style], Species_DD_Desc, [Mill Batch Nbr],
        
            [Glass Completed], [Glass Completed By], [Glass STD Hrs], [SDL Completed], [SDL Completed By], [SDL STD Hrs],
            
            [Mill Completed], [Mill Completed By], [Mill STD Hrs]
            
            FROM PD_Tracking 
            
            WHERE [Order Number] = '$id'
            
            ORDER BY [Door Number], [Qty Display], [Mill Batch Nbr]");
            $i=0;
            while ($list = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)){ ?>
            
            <tr>
                    <td class="center">
                        <input type="checkbox" onclick="$('input[id=\'selid<?php echo $i; ?>\']').attr('checked', this.checked);">
                        <input type="hidden" name="trackingid[]" value="<?php echo $list['id']; ?>">
                    </td>
                    <td class="center"><?php echo $list['Door Number']; ?></td>
                    <td class="center"><?php echo $list['Qty Display']; ?></td>
                    <td class="center"><?php echo $list['Interior or Exterior']; ?></td>
                    <td class="center"><?php echo $list['Style']; ?></td>
                    <td class="center"><?php echo $list['Species_DD_Desc']; ?></td>
                    <td class="center">
                        <?php if (($list['Glass STD Hrs']>0) && (!isset($list['Glass Completed']))){ ?> 
                            <input type="checkbox" name="glass[]" id="selid<?php echo $i; ?>">
                        <?php } else if (($list['Glass STD Hrs']>0) && ($list['Glass Completed'] > ' ')){ ?> 
                            <input type="checkbox" name="glass[]" checked="checked" id="selid<?php echo $i; ?>">
                        <?php } else { ?>
                            NR
                        <?php } ?>   
                    </td>
                    <td class="center">
                        <?php if (($list['SDL STD Hrs']>0) && (!isset($list['SDL Completed']))){ ?> 
                            <input type="checkbox" name="sdl[]" id="selid<?php echo $i; ?>">
                        <?php } else if (($list['SDL STD Hrs']>0) && ($list['SDL Completed'] > ' ')){ ?> 
                            <input type="checkbox" name="sdl[]" checked="checked" id="selid<?php echo $i; ?>">
                        <?php } else { ?>
                            NR
                        <?php } ?>   
                    </td>
                    <td class="center">
                        <?php if (($list['Mill STD Hrs']>0) && (!isset($list['Mill Completed']))){ ?> 
                            <input type="checkbox" name="mill[]" id="selid<?php echo $i; ?>">
                        <?php } else if (($list['Mill STD Hrs']>0) && ($list['Mill Completed'] > ' ')){ ?> 
                            <input type="checkbox" name="mill[]" checked="checked" id="selid<?php echo $i; ?>">
                        <?php } else { ?>
                            NR
                        <?php } ?>   
                    </td>
              </tr>
            <?php ++$i;
            } ?>
          </tbody>
       </table>
      <?php } // END if (!isset($_POST['trackingid'])) {

if (isset($_POST['trackingid'])) {
        
        $query = sqlsrv_query($conn, "SELECT * FROM users WHERE id=".$_SESSION['auid']."");
        $row = sqlsrv_fetch_array($query, SQLSRV_FETCH_ASSOC);
        $employee_num = $row['Employee Number'];
        
        $size = count($_POST['trackingid']);
        for($i=0; $i < $size; $i++){
                
                $trackingid = $_POST['trackingid'][$i];
        
        $query = "UPDATE PD_Tracking SET ";
                
                if(isset($_POST['glass'][$i])){
                        $query .= "[Glass Completed]='$date_time_entered', [Glass Completed By]='$employee_num', ";
                } else {
                        $query .= "[Glass Completed]= NULL, [Glass Completed By]= NULL, ";
                }
                
                if(isset($_POST['sdl'][$i])){
                        $query .= "[SDL Completed]='$date_time_entered', [SDL Completed By]='$employee_num', ";
                } else {
                        $query .= "[SDL Completed]= NULL, [SDL Completed By]= NULL, ";
                }
                
                if(isset($_POST['mill'][$i])){
                        $query .= "[Mill Completed]='$date_time_entered', [Mill Completed By]='$employee_num' ";
                } else {
                        $query .= "[Mill Completed]= NULL, [Mill Completed By]= NULL ";
                }

        $query .= "WHERE id='$trackingid'";

                $query = sqlsrv_query($conn, $query);

        } // END for($i=0; $i < $size; $i++){?>
    
    <SCRIPT LANGUAGE='JavaScript'>
        <!-- Begin 
                window.location = '<?php echo $_SERVER['REQUEST_URI']; ?>&success=edit'; 
        //  End -->
        </script>
                
        
<?php } // END if (isset($_POST['trackingid'])) { ?>



Aucun commentaire:

Enregistrer un commentaire