sql server语言 - SQLSRV PHP删除选中的记录

Question

关于使用sqlsrv驱动程序的Mssql的教程并不多，所以我遵循MySQL的类似教程，并使用sqlsrv API来转换我的代码。到目前为止，所有的作品，我可以添加记录与弹出式表单等

现在我正试图删除多个记录与复选框。我试了所有我能找到的教程，但它仍然不起作用。

选择复选框并按下删除后，除了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-PCSQLEXPRESS";
$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-PCSQLEXPRESS";
$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细节:

database -> test -> table: dbo.modIndexrecordID -第一-第二-第三-第四-第五

提前感谢您的帮助

Answer 1

<?php
if ( !isset($_POST["chkDel"]) || !is_array($_POST["chkDel"]) ) {
    die('missing or wrong parameter "chkDel"');
}
$serverName = "THOMAS-PCSQLEXPRESS";
$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方法和添加错误处理删除。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-PCSQLEXPRESS";
$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-PCSQLEXPRESS";
$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.
        )
)

My database details:

database -> test -> table: dbo。customer -> CustomerID - Name - Email - country - code - Budget - Used.

我是SQL PHP的新手，考虑到网上关于mssql和PHP的教程不多，这更困难。

谢谢大家的帮助和建议。