PHP，PDO和SQLSRV在一条INSERT语句上执行多次 [英] PHP, PDO and SQLSRV executes multiple times on one INSERT statement

问题描述

我在MySQL和Apache服务器上使用PDO和PHP已有一段时间了.我最近的任务是将企业的旧版Web应用程序转换为新的设置.旧的安装程序是标准的Linux Web堆栈(Apache/PHP/MySQL/Filezilla)，新的安装程序是带有IIS/PHP(快速cgi安装)/SQL Server 2003/无FTP的Windows Server 2003.

I've been using PDO and PHP for a while with MySQL and Apache servers. I've recently been tasked with converting a legacy web application for a business to a new setup. The old setup is a standard Linux web stack (Apache/PHP/MySQL/Filezilla) and the new setup will be a Windows server 2003 with IIS/PHP(fast cgi install)/ SQL Server 2003/No FTP.

除了转换MySQL语句以使用文件访问信息更新表外，我几乎可以进行所有工作.将PDO与SQLSRV驱动程序配合使用并在文件下载" PHP脚本内执行插入语句，会将多个记录插入SQL表中.

I have nearly everything working except for a conversion of a MySQL statement to update a table with file access information. Using PDO with SQLSRV driver and executing an insert statement inside a 'file download' PHP script, inserts multiple records into the SQL table.

download.php确实向SQL服务器发出多个查询.一次检查一个表中文件的存在和变量，然后使用访问信息更新另一张表.

The download.php DOES issues multiple queries to the SQL server. Once to check for the file existence and variables in one table, and THEN updates another table with the access information.

请参见下面的download.php代码

SEE BELOW FOR download.php CODE

调试显示$ count的打印/回显为1.但是，检查SQL Server记录时，总是显示插入多个.有时，它仅是一个额外的行，总共有两个，但有时却高达四个被插入的EXTRA语句.在每种情况下，$ count始终显示为1.

Debugging shows the print/echo of $count as 1. Checking the SQL server records however, always shows MORE than one inserted. Sometimes it is just one extra row for a total of two, but other times it is as high as four EXTRA statements being inserted. $count remains shown as 1, in every case.

此特定的PHP脚本在调用此插入语句之前根据SQL数据库验证信息.首先，验证文件访问权限(成功)，验证文件是否存在(成功)，然后使用下载信息更新访问表(错误)，最后将PDF提供给用户(成功).

This particular PHP script verifies information against the SQL database prior to calling this insert statement. First, the verification of authentication for the file access(succeeds), verifies the file existence(succeeds), then updates the access table with information for the download (ERROR) and finally serves the PDF to the user (succeeds).

当我手动向查询分析器中发出INSERT语句时，它成功并按预期工作；每次插入一行.该错误似乎与execute()的SQLSRV或PDO实现有关.

When I issue the INSERT statement manually into Query Analyzer, it succeeds and works as expected; it inserts one row every time. The error seems to be with SQLSRV or PDO implementation of the execute().

我已经在stackoverflow，serverfault和全能的Google上进行了搜索，以获取有关此信息.在用户需要在一个语句/执行中执行多个查询/插入的情况下，返回唯一的结果类型.相反，我的问题在哪里?我只想执行一个插入语句，但是总是执行多个.

I have searched on stackoverflow, serverfault and the almighty Google for information regarding this. The only types of results returned where users needing to execute multiple queries/inserts in one statement/execute. Where's my issue is the opposite; I wish to only execute ONE insert statement however, more than one is always executed.

问题是:为什么会发生这种情况，如何防止多次插入的发生?

Question is: Why is this happening and how can I prevent the multiple insert from happening?

按请求更新

访问该文件的代码是来自另一个网页的单个链接.该页面列出了允许用户访问的当前文件，并提供了指向download.php脚本的链接，用于验证，更新和实际提供PDF.

The code that accesses this file is one singular link from another web page. The page lists current files the user is allowed to access and presents links to the download.php script for the verification, update and actual serving of the PDF.

查看页面上的链接列表(打印在for循环中)排列如下:

View page has a list of links (printed in a for loop) arranged like :

<a href='download.php?f={$item['name']}&t={$type}' target='_blank'>{$item['name']}</a>

当用户单击该链接时，除了上面用于download.php的其他代码外，下面的脚本还将运行.它成功提供了PDF文件.内容由download.php作为PHP标头/嵌入式PDF发送:

When the user clicks on that link, the script below is what runs in addition to the other code above for download.php. It successfully serves the PDF file. The content is being sent by download.php as a PHP header/inline PDF:

查看下面的代码

SEE BELOW FOR CODE

查看服务器日志会显示对download.php文件的两次 GET请求:

Looking at the server logs shows two GET request to the download.php file:

2012-02-14 17:44:37 W3SVC1785071458 172.17.31.254 GET /download.php f=06304844-1A.pdf&t=av 4090 - 172.17.31.112 Mozilla/5.0+(Windows+NT+6.1)+AppleWebKit/535.7+(KHTML,+like+Gecko)+Chrome/16.0.912.77+Safari/535.7 200 0 0
2012-02-14 17:44:37 W3SVC1785071458 172.17.31.254 GET /download.php f=06304844-1A.pdf&t=av 4090 - 172.17.31.112 Mozilla/5.0+(Windows+NT+6.1)+AppleWebKit/535.7+(KHTML,+like+Gecko)+Chrome/16.0.912.77+Safari/535.7 200 0 0

我已经在Firefox，Opera和IE(6-9b)中进行了测试，结果是相同的.

I have tested in Firefox, Opera, and IE (6-9b) and the results are the same.

更新两次

将整个download.php文件放入此处:

Putting the entire download.php file here:

<?php
session_start();
require("cgi-bin/auth.php");

// Don't timeout when downloading large files
@ignore_user_abort();  
@set_time_limit(0);  

//error_reporting(E_ALL); 
//ini_set('display_errors',1);

function getfile() {
    require('cgi-bin/connect_db_pdf.php');
    //Verify information 
    if (!isset($_GET) || !isset($_GET['f']) || !isset($_GET['t'])) {
        echo "Nothing to do!";
        exit(0);

    }

    //Update variables
    $vuname = strtolower(trim($_SESSION['uname']));
    $file = trim($_GET['f']); //Filename we're looking for
    $type = trim($_GET['t']);//Filetype

    if (!preg_match('/^[a-zA-Z0-9_\-\.]{1,60}$/', $file) || !preg_match('/^av|ds|cr|dp$/', $type)) {
        echo "Non conforming values";
        exit(0);
    }

    try {

        $sQuery = "SELECT * FROM pdf_info WHERE PDF_name=:file AND PDF_type=:type";

        $statm = $conn->prepare($sQuery);
        $statm->execute(array(':file'=>$file,':type'=>$type));
        $result = $statm->fetch();
        $count = $statm->rowCount();
        $sQuery = null;
        $statm = null;

        if ($count == 1 ){ //File was found in the database so let them download it. Update the time as well

            $sQuery = "INSERT INTO access (PDF_name,PDF_type, PDF_time, PDF_access) VALUES (:file, :type, GetDate(), :vuname)";

            $statm = $conn->prepare($sQuery);
            $statm->execute(array( ':vuname'=>$vuname, ':file'=>$file, ':type'=>$type));
            $count = $statm->rowCount();
            $sQuery = null;
            $statm = null;

            $sQuery = "UPDATE pdf_info SET last_view=GetDate(),viewed_uname=:vuname WHERE PDF_name=:file AND PDF_type=:type";

            $statm = $conn->prepare($sQuery);
            $statm->execute(array( ':vuname'=>$vuname, ':file'=>$file, ':type'=>$type));
            $sQuery = null;
            $statm = null;

            //$result is from FIRST SELECT query outside this 'if' scope.
            $file_loc = $result['floc'];
            $file_name = $result['filename'];

            $fileh = fopen($file_loc,'rb');//Send content to browser as inline PDF
            header("Content-Type: application/pdf"); 
            header("Pragma: no-cache");  
            header("Cache-Control: must-revalidate, post-check=0, pre-check=0");  
            header("Content-Length: " . filesize($file_loc));  
            header("Accept-Ranges: bytes");
            header("Content-Disposition: inline; filename={$file_name}");  

            while (!feof($fileh)) { 
                echo(@fgets($fileh, 8192)); 
            } 

            fclose ($fileh); 
            exit(0);

            } else { //We did not find a file in the database. Redirect the user to the view page.
                header("Location: view.php");
            }

            }   catch(PDOException $err) {//PDO SQL error. 
            //echo $err;
            header('Location: error.php');
            exit(0);
         }
}

getfile();

?>

推荐答案

提示:从所有PHP文件末尾删除?>.有时，您最终会在其后得到一个不可见的空格或换行符，并且当输出二进制格式(如PDF)时，它会成为PDF的一部分并使其损坏. (因此，浏览器/PDF阅读器将挂起.)

Tip: Remove the ?> from the end of ALL your PHP files. Occasionaly you end up getting an invisible space or newline after it, and when outputting a binary format like PDF it becomes a part of the PDF and makes it corrupt. (Hence browser/PDF reader hang.)

这篇关于PHP，PDO和SQLSRV在一条INSERT语句上执行多次的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！