PHP / SQL：多次删除不成功
目前我已经创建了一个系统，该系统可以对从 SQL 数据库中获取并显示在dashboard_engineer.php. 每个数据行都包含一个删除按钮。每行还有一个复选框，使用户能够删除选定的数据行。在数据行的底部，有一个用于删除所有选定数据行的按钮。
我的问题是多次删除，比如说，如果我选择两个数据行（id 4 和 5），它只会删除一个 ID。
下面是我的代码
仪表板_engineer2.php
<?php
if(isset($_REQUEST['from'], $_REQUEST['to'], $_REQUEST['team'])){
$from = $_REQUEST['from'];
$to = $_REQUEST['to'];
$team = $_REQUEST['team'];
$result = '';
$query = "SELECT * FROM ot_report LEFT JOIN ot_users ON ot_report.badgeid = ot_users.badgeid
WHERE ot_report.status = 'yes' AND ot_users.team_id = :team AND report_date BETWEEN :from
AND :to ORDER BY ot_report.report_id DESC";
$sql = $conn->prepare($query, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
$sql->bindParam(':team', $team);
$sql->bindParam(':from', $from);
$sql->bindParam(':to', $to,);
$sql -> execute();
if($sql->rowCount() > 0){
echo'
<table class = "table-bordered" width = "100%">
<thead>
<tr>
<th width = "10%"><input type="checkbox" id="checkAl"> All</th>
<th width = "3%">id</th>
<th width = "15%">Date</th>
<th width = "25%">Supervisor</th>
<th width = "30%">Task Name</th>
<th width = "10%">Status</th>
<th colspan = "2" width = "7%">Action</th>
</tr>
</thead>
<tbody>';
$i=0;
while($row = $sql->fetch(PDO::FETCH_ASSOC)){
$status=$row['report_status'];
if($status=="Pending"){
$color="color:blue";
}
else {
$color="color:green";
}
}
谁能帮我解决这个问题？谢谢
手掌心浏览 108回答 1
1回答
-
狐的传说
好的 - 以下是基于问题的代码，但为了构建一个工作演示，它已经被概括了。这里重要的是使用 Javascript 来维护选定的 ID 列表（通过单击select-all复选框或单击单个复选框。请忽略表单中生成的内容 - 日期对于本示例而言是虚假且无意义的。您可以复制它并创建一个应该可以正常运行的测试页面 - 生成伪 SQL 的 PHP 只是显示将要执行的语句，而不是它应该如何执行（应该按照问题，prepared statement）<?php if( $_SERVER['REQUEST_METHOD']=='POST' ){ if( isset( $_POST['ids'] ) ){ $items=explode( ',', $_POST['ids'][0] ); foreach($items as $id){ $sql=sprintf('UPDATE ot_report SET status="no" WHERE report_id=%d',$id); echo $sql . '<br />'; } } }?><!DOCTYPE html><html> <head> <meta charset='utf-8' /> <title></title> </head> <body> <table> <?php for( $i=1; $i <= 10; $i++ ){ /* example data for dates... */ $hrs=mt_rand(1,24); $mins=mt_rand(0,60); $secs=mt_rand(0,60); $month=mt_rand(1,12); $day=mt_rand(1,28); $year=mt_rand(2000,2020); $from=date('y-m-d',mktime($hrs,$mins,$secs,$month,$day,$year)); $to=date('y-m-d',mktime($hrs+3,$mins+20,$secs,$month,$day,$year)); printf(' <tr> <td><input type="checkbox" name="check[]" value="%1$d" /></td> <td>Some data %1$d</td> <td> <form method="post"> <input type="hidden" name="from" value="%2$s" /> <input type="hidden" name="to" value="%3$s" /> <input type="hidden" name="id" value="%1$d" /> <input type="submit" /> </form> </td> </tr>', $i, $from, $to ); } ?> </table> <form method='post'> <input type='checkbox' name='selectall' value='Select-All' /> <input type='hidden' name='ids[]' /> <input type='button' name='sub-delete' value='Update selected' /> </form> <script> let ids=document.querySelector('input[type="hidden"][name="ids[]"]'); let tmp=[]; document.querySelector('[name="selectall"]').addEventListener('change',function(e){ let col=document.querySelectorAll('[name="check[]"]'); col.forEach(chk=>{ chk.checked=this.checked; if( this.checked )tmp.push(chk.value) else tmp.splice(tmp.indexOf(chk.value),1) }); }); document.querySelectorAll('[name="check[]"]').forEach( chk=>{ chk.addEventListener('change',function(e){ if( this.checked )tmp.push(this.value) else tmp.splice( tmp.indexOf(this.value),1); }); }); document.querySelector('[name="sub-delete"]').addEventListener('click',function(e){ ids.value=tmp.join(',') this.parentNode.submit() }); </script> </body></html>将产生类似于：UPDATE ot_report SET status="no" WHERE report_id=10UPDATE ot_report SET status="no" WHERE report_id=8UPDATE ot_report SET status="no" WHERE report_id=6UPDATE ot_report SET status="no" WHERE report_id=4快速尝试使用您的代码调整此示例：<?php if(isset($_REQUEST['from'], $_REQUEST['to'], $_REQUEST['team'])){ $from=$_REQUEST['from']; $to =$_REQUEST['to']; $team=$_REQUEST['team']; $result=''; $query="SELECT * FROM ot_report LEFT JOIN ot_users ON ot_report.badgeid=ot_users.badgeid WHERE ot_report.status='yes' AND ot_users.team_id=:team AND report_date BETWEEN :from AND :to ORDER BY ot_report.report_id DESC"; $sql=$conn->prepare($query, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL)); $sql->bindParam(':team', $team); $sql->bindParam(':from', $from); $sql->bindParam(':to', $to); $sql->execute(); if($sql->rowCount() > 0){ echo' <table class="table-bordered" width="100%"> <thead> <tr> <th width="10%"><input type="hidden" name="selectall" value="Select-All">All</th> <th width="3%">id</th> <th width="15%">Date</th> <th width="25%">Supervisor</th> <th width="30%">Task Name</th> <th width="10%">Status</th> <th colspan="2" width="7%">Action</th> </tr> </thead> <tbody>'; $i=0; while($row=$sql->fetch(PDO::FETCH_ASSOC)){ $status=$row['report_status']; if($status=="Pending"){ $color="color:blue"; } else { $color="color:green"; } $report_id=$row["report_id"]; echo' <tr> <td><input type="checkbox" name="check[]" value="'.$report_id.'"></td> <td>'.$report_id.'</td> <td>'.$row['report_date'].'</td> <td>'.$row["fullname"].'</td> <td>'.$row["task_name"].'</td> <td style='.$color.'><strong>'.$status.'</strong></td> <td> <form action="view_task/view_task.php" method="post" target="_blank"> <input type="hidden" name="report_id" value="'.$report_id.'"> <button type="submit" class="btn-primary">View</button> </form> </td> <td> <form action="remove2.php" method="post" onsubmit="return confirm(\'Do you want to remove this reports?\')"> <input type="hidden" name="from" value="'.$from.'"> <input type="hidden" name="to" value="'.$to.'"> <input type="hidden" name="team" value="'.$team.'"> <input type="hidden" name="report_id" value="'.$report_id.'"> <button type="submit" class="btn-danger">Remove</button> </form> </td> </tr>'; $i++; } echo ' <tr> <td> <form action="delete_selected.php" method="post"> <input type='hidden' name='ids[]' /> <input type='button' name='sub-delete' value='DELETE' /> </form> </td> </tr>'; } }?><script> let ids=document.querySelector('input[type="hidden"][name="ids[]"]'); let tmp=[]; document.querySelector('[name="selectall"]').addEventListener('change',function(e){ let col=document.querySelectorAll('[name="check[]"]'); col.forEach(chk=>{ chk.checked=this.checked; if( this.checked )tmp.push(chk.value) else tmp.splice(tmp.indexOf(chk.value),1) }); }); document.querySelectorAll('[name="check[]"]').forEach( chk=>{ chk.addEventListener('change',function(e){ if( this.checked )tmp.push(this.value) else tmp.splice( tmp.indexOf(this.value),1); }); }); document.querySelector('[name="sub-delete"]').addEventListener('click',function(e){ ids.value=tmp.join(',') this.parentNode.submit() });</script>和delete_selected.php<?php include("../../../config/configPDO.php"); include("../../../config/check.php"); $checkbox=explode( ',', $_POST['ids'][0] ); for($i=0;$i < count($checkbox); $i++){ $report_id=$checkbox[$i]; $sql="UPDATE ot_report SET status='no' WHERE report_id=:report_id"; $query=$conn->prepare($sql); $query->execute(array(':report_id' => $report_id)); header("Location: dashboard_engineer.php"); }?>
随时随地看视频慕课网APP
PHP