小贝子编程

PHP/SQL:成功更新数据行，但出现PHP致命错误



我的代码中有一个非常奇怪的问题。简而言之，我创建了一个系统，它有一个名为dashboard_engineer.php的仪表板。这个仪表板将只显示SQL数据库的前30个数据行。该仪表板还包含3个过滤输入，它们是：

团队

日期

日期

用户可以使用此筛选器来查找其确切的数据行。用户只需填写输入并按下按钮Search，这将把用户重定向到dashboard_engineer2.php。

在此仪表板中，将显示所有筛选的数据行。每个数据行都包含一个"查看"和一个"删除"按钮。删除按钮用于通过更新数据行来删除数据行的详细信息。

我的问题是，在过滤后的数据上，当我单击删除按钮时，数据在SQL数据库中更新，但它会显示以下错误：

PHP致命错误：未捕获的PDOException:SQLSTATE[42000]：[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]"10"附近的语法不正确。在C:\inetpub\wwwroot\tgotworker_testing\pages\dashboard\engineer\dashboard_engineer2.php中：157

当我从一个未被过滤的数据行(dashboard_engineer.php(中单击删除按钮时，数据被删除，之后不会显示任何错误。

任何关于如何解决这一问题的建议都将不胜感激。以下是我的代码：

dashboard_engineer.php

<form method = 'post' action = 'dashboard_engineer2.php' target="_blank">
<td width="40%">
<select class="form-control"  name="team" id="team" required>
<option value="">Please select...</option>
<?php foreach ($data as $row2): ?>
<option value="<?php echo $row2["team_id"]; ?>"><?php echo $row2["fullname"]; ?></option>
<?php endforeach ?>
</select>
</td>
<td width="1%"></td>
<td width="20%"><input type="text" name="from" id="from" class="form-control" placeholder="From" required></td>
<td width="1%"></td>
<td width="20%"><input type="text" name="to" id="to" class="form-control" placeholder="To" required></td>
<td width="1%"></td>
<td width="10%"><button type="submit" class="btn-primary" >Search</button><td>
</form>
</tr>
</table><br>
<div class="row" style='height: 300px; overflow-y: scroll;'>
<div class="col-lg-12 grid-margin stretch-card">
<?php
$query = $conn->query("SELECT TOP 30 * FROM ot_report LEFT JOIN ot_users ON ot_report.badgeid = ot_users.badgeid WHERE ot_users.team_id <> 1 AND ot_report.status = 'yes'  ORDER BY ot_report.report_id DESC");
$query -> execute();
$results = $query -> fetchAll(PDO::FETCH_OBJ);
if(empty($results)){
echo "<table class = 'table-bordered' width ='100%'>";
echo "<thead>";
echo "<tr>";
echo "<th width = '5%'>id</th>
<th width = '17%'>Date</th>
<th width = '27%'>Officer/ Asst. Engineer</th>
<th width = '32%'>Task Name</th>
<th width = '12%'>Status</th>
<th width = '7%'>Action</th>
</tr>
</thead>
<tbody >
<tr>
<td colspan='8'>No report at this moment</td>
</tr>
</tbody>
</table>";
}else{
echo "<table class = 'table-bordered' width ='100%'>";
echo "<thead>";
echo "<tr>";
echo "<th width = '5%'>id</th>
<th width = '17%'>Date</th>
<th width = '27%'>Officer/ Asst. Engineer</th>
<th width = '32%'>Task Name</th>
<th width = '12%'>Status</th>
<th colspan = '2' width = '7%'>Action</th>
</tr>
</thead>
<tbody >";
$query = $conn->query("SELECT TOP 30 * FROM ot_report LEFT JOIN ot_users ON ot_report.badgeid = ot_users.badgeid WHERE ot_users.team_id <> 1 AND ot_report.status = 'yes' ORDER BY ot_report.report_id DESC");
while($row = $query->fetch(PDO::FETCH_ASSOC)){
$status=$row['report_status'];
if($status=="Pending")
{
$color="color:blue";
}
else 
{
$color="color:green";
}
$report_id = $row['report_id'];
$datereport = $row['report_date'];
$datereport2 = strtotime($datereport);
$report_date = date('d M Y', $datereport2);
$fullname = $row['fullname'];
$task_name = $row['task_name'];

echo "<tr>";
echo "<td>". $report_id. "</td>";
echo "<td>". $report_date . "</td>";
echo "<td>". $fullname . "</td>";
echo "<td>". $task_name . "</td>";
echo "<td align='center' style='$color'><strong>". $status . "</strong></td>";
echo "<td align='center'>";
echo "<form method = 'post' action = 'view_task/view_task.php' target='_blank'>";
echo "<input type = 'hidden' name = 'report_id' value = '".$report_id."'>";
echo "<button type = 'submit' class='btn-primary'>View</button>";
echo "</form>";
echo "</td>";
echo "<td align='center'>";
echo "<form method = 'post' action = 'remove.php' onClick="return confirm('Do you want to remove this report?')">";
echo "<input type = 'hidden' name = 'report_id' value = '".$report_id."'>";
echo "<button type = 'submit' class='btn-danger'>Remove</button>";
echo "</form>";
echo "</td>";
echo "</tr>";
}
echo "</tbody>";
echo "</table><br>";  
}
?>

dashboard_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 -> execute(); //line 157
if($sql->rowCount() > 0){
echo'
<div><a href="../pdf.php?from='.$from.'&to='.$to.' &team='.$team.'" target="_blank"><u>PDF View</u></a></div><br>
<div class="row">
<div class="col-lg-12 grid-margin stretch-card">   
<div class="row" style="height: 300px; overflow-y: scroll;">
<div class="col-lg-12 grid-margin stretch-card">    
<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)){
$datereport = $row['report_date'];
$datereport2 = strtotime($datereport);
$report_date = date('d M Y', $datereport2);
$status=$row['report_status'];
if($status=="Pending"){
$color="color:blue";
}
else {
$color="color:green";
}
$report_id = $row["report_id"];
echo'<tr>';
echo '<td><input type="checkbox" id="checkItem" name="check[]" value='.$row['report_id'].'></td>';
echo '<td>'.$report_id.'</td>';
echo '<td>'.$report_date.'</td>';
echo '<td>'.$row["fullname"].'</td>';
echo '<td>'.$row["task_name"].'</td>';
echo '<td align="center" style='.$color.'><strong>'.$status.'</strong></td>';
echo '<td align="center">';
echo '<form action = "view_task/view_task.php" method = "post" target="_blank">';
echo '<input type = "hidden" name = "report_id" value = "'.$report_id.'">';
echo '<button type = "submit" class="btn-primary">View</button>';
echo '</form>';
echo '</td>';
// echo '<a class="btn-view btn-primary btn-sm" href="view_task/view_task.php?report_id='. $report_id .'" data-toggle="tooltip" >View</a></td>';
echo '<td align="center">';
echo "<form action = 'remove2.php' method = 'post' onClick="return confirm('Do you want to remove this reports?')">";
echo '<input type = "hidden" name = "from" value = "'.$from.'">';
echo '<input type = "hidden" name = "to" value = "'.$to.'">';
echo '<input type = "hidden" name = "team" value = "'.$team.'">';
echo '<input type = "hidden" name = "report_id" value = "'.$report_id.'">';
echo '<button type = "submit" class="btn-danger">Remove</button>';
echo '</form>';
echo '</td>';
echo '</tr>';
$i++;
}
echo '<tr>';
echo '<td><p align="center"><button type="submit" class="btn-danger btn-sm" name="save">DELETE</button></p></td>';
echo '</tr>';
}
else
{
echo '
<table class = "table-bordered" width = "100%">
<thead>
<tr>
<th width = "5%">id</th>
<th width = "12%">Date</th>
<th width = "29%">Supervisor</th>
<th width = "23%">Task Name</th>
<th width = "10%">Status</th>
<th width = "7%">Action</th>
</tr>
<tr>
<td colspan="6">No report found</td>
</tr>';
}
echo '</body></table></div></div>';
} 
?>

remove2.php

<?php
include('../../../config/configPDO.php');
$report_id = $_POST['report_id'];
$from = $_POST['from'];
$to = $_POST['to'];
$team = $_POST['team'];
$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_engineer2.php?from='".$_POST["from"]."'&to='".$_POST["to"]."' &team='".$_POST["team"]."'");
?>

使用参数化查询，而不是串联变量。

$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, PDO::PARAM_INT);
$sql->bindParam(':from', $from, PDO::PARAM_INT);
$sql->bindParam(':to', $to, PDO::PARAM_INT);
$sql -> execute();

我在remove2.php找到了答案，问题。下面是代码：

<?php
include('../../../config/configPDO.php');
$report_id = $_POST['report_id'];
$from = $_POST['from'];
$to = $_POST['to'];
$team = $_POST['team'];
$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_engineer2.php?from=".$from."&to=".$to."&team=".$team."");
?>

相关内容

最新更新


热门标签：

javascript python java c# php android html jquery c++ css ios sql mysql arrays asp.net json python-3.x ruby-on-rails .net sql-server django objective-c excel regex ruby linux ajax iphone xml vba spring asp.net-mvc database wordpress string postgresql wpf windows xcode bash git oracle list vb.net multithreading eclipse algorithm macos powershell visual-studio image forms numpy scala function api selenium