SQLSRV删除选定记录

Question

在web中，关于使用sqlsrv驱动程序的Mssql的教程不多，所以我使用MySQL学习了类似的教程，并使用sqlsrv来转换我的代码。到目前为止，所有的工作，我可以添加与弹出形式的记录等。

现在我尝试用复选框删除多个记录。我尝试了我能够找到的所有教程，但仍然无法工作。

在选择复选框并按下onDelete脚本中的删除问题后，没有任何效果，这会问我是否喜欢删除记录。

我有两个php文件- SQLTest.php和delete.php。

代码来自SQLTest.php

<h1>SQL Testing</h1>
<h1>Component Group Modifications</h1>

<table class="table_general">
<tbody>

<tr>
  <th>ID</th>
  <th colspan="2">MODIFICATIONS</th>
  <th colspan="3">APPLICABLE WORKSHEET</th> 
  <th>Delete</th>
</tr>

<?php
$serverName = "THOMAS-PC\SQLEXPRESS";
$connectionInfo = array( "Database"=>"test");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn ) 
{
     echo "Connected to Database !";
}
else
{
     echo "Opps - something went wrong !";
     die( print_r( sqlsrv_errors(), true));
}

$tsql = "SELECT * FROM dbo.modIndex";
$query = sqlsrv_query($conn, $tsql);
if ($query === false){  exit("<pre>".print_r(sqlsrv_errors(), true));

}
while ($row = sqlsrv_fetch_array($query))
{  
?>
  <tr>
    <td><?php echo $row["recordID"];?></td>
    <td><?php echo $row["first"];?></td>
    <td><?php echo $row["second"];?></td>
    <td><?php echo $row["third"];?></td>
    <td><?php echo $row["fourth"];?></td>
    <td><?php echo $row["fifth"];?></td>
    <td><input type="checkbox" name="chkDel[]" value="<?php echo $row["recordID"];?>"></td>
  </tr>
<?php
}
sqlsrv_free_stmt($query);
?>

</tbody>
<input type="button" value="Delete Selected Records" action="delete.php" method="post" onclick="return onDelete();"/>
</table>

</section>
</section>

delete.php代码

<?php

$serverName = "THOMAS-PC\SQLEXPRESS";
$connectionInfo = array( "Database"=>"test");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn === false )
{
     echo "<script>alert('Opps - something went wrong !');</script>";
     die( print_r( sqlsrv_errors(), true));
}
for($i = 0; $i < count($_POST["chkDel"]);$i++)
{
    if($_POST["chkDel"][$i] != "")

        {
            $tsql = "DELETE FROM dbo.modIndex ";
            $tsql = "WHERE recordID = '".$_POST["chkDel"][$i]."'  "; 
            $query = sqlsrv_query($conn, $tsql);
        }
}
    echo "Record Deleted.";

sqlsrv_free_stmt( $tsql);
sqlsrv_close( $conn);

?>

短onDelate函数：

<script language="JavaScript">
    function onDelete()
    {
        if(confirm('Do you want to delete ?')==true)
        {
            return true;
        }
        else
        {
            return false;
        }
    }
</script>

以及我的SQL详细信息：

数据库->测试->表: dbo.modIndex recordID -第一、第二、第三、第四、第五

提前谢谢你的帮助。

Answer 1

<?php
if ( !isset($_POST["chkDel"]) || !is_array($_POST["chkDel"]) ) {
    die('missing or wrong parameter "chkDel"');
}

$serverName = "THOMAS-PC\SQLEXPRESS";
$connectionInfo = array( "Database"=>"test");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false ) {
    echo "<script>alert('Opps - something went wrong !');</script>";
    die( print_r( sqlsrv_errors(), true) );
}

$query = 'DELETE FROM dbo.modIndex WHERE ID=?';
$stmt = sqlsrv_prepare($conn, $query, array(&$id));
if( !$stmt ) {
    die( print_r( sqlsrv_errors(), true) );
}

foreach( $_POST["chkDel"] as $id ) {
    if( !sqlsrv_execute( $stmt ) ) {
        die( print_r( sqlsrv_errors(), true) );
    }
}

Answer 2

我按照VolkerK的建议修改代码，使用POST方法，并将错误处理添加到delete.php中，这给了我更多的信息。

变动如下：

    <form name="frmMain" action="delete.php" method="post" OnSubmit="return onDelete();">

<table class="table_general">
<tbody>
<tr>
   <th>CustomerID</th>
    <th>Name</th>
    <th>Email</th>
    <th>CountryCode</th>
    <th>Budget</th>
    <th>Used</th>
    <th>Delete</th>
</tr>

<?php
$serverName = "THOMAS-PC\SQLEXPRESS";
$connectionInfo = array( "UID"=>"", "PWD"=>"", "Database"=>"test");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn ) 

{
     echo "Connected to Database !";
}
else
{
     echo "Opps - something went wrong !";
     die( print_r( sqlsrv_errors(), true));
}

$tsql = "SELECT * FROM dbo.customer";
$query = sqlsrv_query($conn, $tsql);
if ($query === false){  exit("<pre>".print_r(sqlsrv_errors(), true));

}
while ($objResult = sqlsrv_fetch_array($query))

{  

?>
  <tr>
    <td><?=$objResult["CustomerID"];?></td>
    <td><?=$objResult["Name"];?></td>
    <td><?=$objResult["Email"];?></td>
    <td><?=$objResult["CountryCode"];?></td>
    <td><?=$objResult["Budget"];?></td>
    <td><?=$objResult["Used"];?></td>
    <td><input type="checkbox" name="chkDel[]" value="<?=$objResult["CustomerID"];?>"></td>
  </tr>
<?php
}

sqlsrv_free_stmt($query);

?>
</tbody>

<input type="submit" name="btnDelete" value="Delete">
</table>
</form>

在选择一个复选框并按下“删除”按钮后，delete.php给了我现在的错误：

delete.php代码：

    <?php

$serverName = "THOMAS-PC\SQLEXPRESS";
$connectionInfo = array( "Database"=>"test");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn === false )
{
     echo "<script>alert('Opps - something went wrong !');</script>";
     die( print_r( sqlsrv_errors(), true));
}
for($i = 0; $i < count($_POST["chkDel"]);$i++)
{
    if($_POST["chkDel"][$i] != "")

        {
            $tsql = "DELETE FROM dbo.customer";
            $strSQL .="WHERE CustomerID = '".$_POST["chkDel"][$i]."' ";
            $query = sqlsrv_query($conn, $tsql, $strSQL);
            var_export($_POST);

        }

        print_r(sqlsrv_errors());
        }


sqlsrv_free_stmt( $tsql);
sqlsrv_close( $conn);

?>

以及检查三个可用复选框后出现的错误。

       Array
(
    [chkDel] => Array
        (
            [0] => 1
        )

    [btnDelete] => Delete
)
chkDel=Array<br />btnDelete=Delete<br />

Array
(
    [0] => Array
        (
            [0] => IMSSP
            [SQLSTATE] => IMSSP
            [1] => -14
            [code] => -14
            [2] => An invalid parameter was passed to sqlsrv_query.
            [message] => An invalid parameter was passed to sqlsrv_query.
        )

)

我的数据库详细信息：

数据库->测试->表: dbo.customer -> CustomerID -名称-电子邮件- CountryCode -预算使用。

我是SQL的新手，考虑到网络中关于mssql和php的教程不多，这就更难了。

谢谢大家的帮助和建议。