Page 1 of 1

Multiple record update testing checkbox

Posted: Mon Dec 13, 2010 11:04 am
by marnieg
I have a page where I'm listing some records. On each record I have added a checkbox. Then I have button for "Approve" where if they click it, then an update only on those records that have the checkbox set I want to update another field in my database. What is happening is when I click the Approve button it updates ALL of the records displayed. I have tried several ways of displaying the field and checking the checkbox field but no such luck. Here is my code.

Code: Select all

        <form name="timecardapproval" method="post" action="">
        <table border='1' style='border-collapse' width='100%' cellpadding='2'>
        <tr>
        <td><b>Date<b></td>
        <td><b>WO Num<b></td>
        <td><b>Job Name</b></td>
        <td><b>Job Num<b></td>
        <td><b>Inspector</b></td>
        <td><b>ST<b></td>
        <td><b>OT</b></td>
        <td><b>DT</b></td>
        <td><b>HP</b></td>
        <td><b>Sub</b></td>
        <td><b>Auto</b></td>
        <td><b>Miles</b></td>
        <td><b>Stat<b></td>
        <td><b>Appv</b></td>
        <td><b>Det</b></td>
        </tr>
        <?php
        while($row = mysql_fetch_array($query))
        {
        $tid = $row['ts_id'];
        ?>
        <tr>
         <td><?php echo $row['ts_date']; ?></td>
       <td><?php echo $row['ts_wo_num']; ?></td>
        <td><?php echo $row['job_nm']; ?></td>
        <td><?php echo $row['job_num']; ?></td>
        <td><?php echo $row['insp_nm']; ?></td>
        <td><?php echo $row['ts_st_hours']; ?></td>
        <td><?php echo $row['ts_ot_hours']; ?></td>
        <td><?php echo $row['ts_dt_hours']; ?></td>
        <td><?php echo $row['ts_hp_hours']; ?></td>
        <td><?php echo $row['ts_sub_cost']; ?></td>
        <td><?php echo $row['ts_auto_cost']; ?></td>
        <td><?php echo $row['ts_mileage']; ?></td>
        <td><?php echo $row['ts_status']; ?></td>
        <td><input name="checkbox" type='checkbox' id="checkbox" <?php if($row['ts_status']=='A'): ?>checked='checked'<?php endif; ?>/></td>
        <td><?php echo "<a target=\'_blank\' href='timesheet_det.php?ts_id=$tid'>Det</a>" ?></td>
        <td><input type=hidden name="tsid[]" value="<?php echo $row['ts_id']; ?>" /><?php echo $row['ts_id']; ?></td>
       </tr>
        <?php
        }
        ?>
        <tr>
        <td colspan="3"><input type="submit" name="Approve" value="Approve">
        <input type="button" onClick="history.go(0)" value="Refresh">
</td></tr>
        </table></form>
        <br />
        <?php
        $count = mysql_num_rows($query);
        if ($count == 0)
        {
        echo "Sorry, but we can not find an entry to match your query<br>"; exit;    
        }
        //CHECK FOR APPROVAL CHECKBOXES
        if($Approve){
        foreach($_POST['tsid'] as $tsid) {
        if (isset($_POST['checkbox'])) {
        echo $tsid;
        // need logic for approval
        $sql1="Update timesheets set ts_status = 'A' where ts_id = '".$tsid."'";
        $result1=mysql_query($sql1);
        }
        }
        }
        exit;
        // if successful redirect 
        if($result1){
        echo ('<meta http-equiv="refresh" content="0;url=/admin_timesheets.php">');
        }
Thanks for any help. I know I'm just missing something.

Re: Multiple record update testing checkbox

Posted: Mon Dec 13, 2010 11:23 am
by AbraCadaver
Lot's of problems here, but here is an untested example that might help. Get rid of the hidden input and just use the checkbox array and give each the value of $tid:

[text]<input name="checkbox[]" value="<?php echo $tid; ?>" type="checkbox" id="checkbox" <?php if($row['ts_status']=='A'): ?>checked="checked"<?php endif; ?>/>[/text]

Then, get rid of the loop, one update will do it:

Code: Select all

if($_POST['Approve']) {
     $values = "'".implode("','", $_POST['checkbox'])."'";
     $sql1 = "UPDATE timesheets SET ts_status = 'A' WHERE ts_id IN ($values)'";
     $result1 = mysql_query($sql1);
}

Re: Multiple record update testing checkbox

Posted: Mon Dec 13, 2010 12:56 pm
by marnieg
The code you provided was almost perfect. Just

changed if($_POST['Approve']) to if($Approve)

and on the "where" statement you had an extract single quote at the end.

Thanks so much for your quick response. I can always count on this forum to get me past my little hurdles. :D

Re: Multiple record update testing checkbox

Posted: Mon Dec 13, 2010 1:01 pm
by AbraCadaver
marnieg wrote:The code you provided was almost perfect. Just

changed if($_POST['Approve']) to if($Approve)

and on the "where" statement you had an extract single quote at the end.

Thanks so much for your quick response. I can always count on this forum to get me past my little hurdles. :D
Glad it works, however you should use $_POST['Approve'] as just using $approve is relying on register_globals which is deprecated and insecure. Better would be:

Code: Select all

if(isset($_POST['Approve'])) {