Multiple record update testing checkbox

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
marnieg
Forum Commoner
Posts: 65
Joined: Wed Mar 12, 2003 4:35 pm

Multiple record update testing checkbox

Post 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.
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: Multiple record update testing checkbox

Post 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);
}
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
marnieg
Forum Commoner
Posts: 65
Joined: Wed Mar 12, 2003 4:35 pm

Re: Multiple record update testing checkbox

Post 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
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: Multiple record update testing checkbox

Post 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'])) {
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
Post Reply