dongtan7418 2013-12-14 12:35
浏览 73
已采纳

PHP脚本在将数据从CSV导入MySQL时保持超时

I'm stumped on this one. I'm a little above beginner-level with PHP/MySQL and very new with posting on this site. GoDaddy switched me over to a grid server to boost performance and shed light on a problem with the way I have coded a script. It grabs data out of a CSV and attempts to insert into a normalized database.

The CSV is not normalized so there is a lot of checking to see if something exists. I originally had it opening/closing result sets, but then it was suggested to me to use prepared statements and unfortunately I have run into the same problem. I can get through about 1200 of 14k records before getting the broad "Internal Server Error". The error in the log references a security feature where it prevents hitting the FastCGI server too much in a short period of time.

What I'm trying to find out and learn is the proper method to accomplish what I'm trying to do -- check to see if something exists; if it does, get the record ID. If not, insert the data and get the new ID. My code is below. It gets the file name and a hidden attribute from a simple php file upload form and starts going from there. This will only be used by me and the data I'm inserting is public record so security isn't a major concern.

<?php

if ($_POST["upload"] == "1") {

    //Connect to the database
    $hostname = xxx;
    $username = xxx;
    $dbname = xxx;
    $password = xxx;

    $dbh = mysqli_connect($hostname,$username,$password,$dbname) or die("Problem connecting: ".mysqli_error());

    $stmt = mysqli_stmt_init($dbh);


    //check for file errors
  if ($_FILES["file"]["error"] > 0)
    { echo "Return Code: " . $_FILES["file"]["error"] . "<br>"; }
  //No file errors
  else
    {

    //If file already exists
    if (file_exists($_FILES["file"]["name"]))
    { 
            echo $_FILES["file"]["name"] . " already exists.";
            exit; 
    }

    //If it doesn't exist
    else
      {
      move_uploaded_file($_FILES["file"]["tmp_name"],
      $_FILES["file"]["name"]);
      echo "Stored in: " . $_FILES["file"]["name"] . "<br><br>";
      $strFileName = $_FILES["file"]["name"];
      }
    }

    //File reporting
    echo "Upload: " . $_FILES["file"]["name"] . "<br>";
    echo "Type: " . $_FILES["file"]["type"] . "<br>";
    echo "Size: " . ($_FILES["file"]["size"] / 1024) . " kB<br>";
    echo "Temp file: " . $_FILES["file"]["tmp_name"] . "<br>";



$row = 0;
if (($handle = fopen($strFileName, "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        $num = count($data);
        $row++;
        $strPermitNo = trim($data[0]);

        //Check to see if the permit is already in the database
        $sql = "SELECT LocID FROM tbl_TABC_Locations WHERE LocPermitNo = ?";

        if (mysqli_stmt_prepare($stmt, $sql))
        {
            mysqli_stmt_bind_param($stmt, "s", $strPermitNo);
            mysqli_stmt_bind_result($stmt, $intLocID);
            mysqli_stmt_execute($stmt);

            $strPermitResult = 0;

            while (mysqli_stmt_fetch($stmt))
            {
                $strPermitResult = $intLocID;
            }
        }

        //If no permits, insert it
        if ($strPermitResult == "0")
        {       
            //Clean Location name
            $strLocName = trim($data[1]);
            $strLocName = str_replace('"', "", $strLocName);
            $strLocName = str_replace(";","-", $strLocName);
            $strLocName = addslashes($strLocName);

            $strInsertQuery = "INSERT INTO tbl_TABC_Locations (LocName,LocAddress,LocCity,LocState,LocZip,LocCounty,LocPhone,LocPermitNo) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";

            if (mysqli_stmt_prepare($stmt, $strInsertQuery)) 
            {
              mysqli_stmt_bind_param($stmt, 'ssssiiis', $field1, $field2, $field3, $field4, $field5, $field6, $field7, $field8);

              $field1 = $strLocName;
              $field2 = trim(addslashes($data[2]));
              $field3 = trim(addslashes($data[3]));
              $field4 = trim($data[4]);
              $field5 = trim($data[5]);
              $field6 = trim($data[6]);
              $field7 = trim($data[7]);
              $field8 = $strPermitNo;
              mysqli_stmt_execute($stmt);

              $intLocID = mysqli_insert_id($dbh);
            }
        }

        else 
        {
            $intLocID = $strPermitResult;
        }


        //Report dates
        $strReportDate = trim($data[8]);
        $aryNewDate = explode("/", $strReportDate);
        $strNewYear = $aryNewDate[0];
        $strNewMonth = $aryNewDate[1];

        //Check to see if the report date is already in there
        $sql = "SELECT ReportDateID FROM tbl_TABC_ReportDates WHERE ReportYear = ? AND ReportMonth = ?";

        if (mysqli_stmt_prepare($stmt, $sql))
        {
            mysqli_stmt_bind_param($stmt, "ii", $strNewYear, $strNewMonth);
            mysqli_stmt_bind_result($stmt, $intReportDateID);
            mysqli_stmt_execute($stmt);

            $strReportDateResult = 0;

            while (mysqli_stmt_fetch($stmt)) 
            {
                $strReportDateResult = $intReportDateID;
            }
        }   

        if ($strReportDateResult == "0")
        {
            $strInsertQuery = "INSERT INTO tbl_TABC_ReportDates (ReportMonth,ReportYear) VALUES (?, ?)";

            if (mysqli_stmt_prepare($stmt, $strInsertQuery)) 
            {
              mysqli_stmt_bind_param($stmt, "ii", $field1, $field2);

              $field1 = $strNewMonth;
              $field2 = $strNewYear;

              mysqli_stmt_execute($stmt);

              $intDateID = mysqli_insert_id($dbh);
            }
        }
        else
        {
            $intReportDateID = $strReportDateResult;
        }


        //Check to see if they have reported for the month already, and if not, add the report      
        $sql = "SELECT ReportID FROM tbl_TABC_Reports WHERE ReportDateID = ? AND LocID = ?";    

        if (mysqli_stmt_prepare($stmt, $sql))
        {
            mysqli_stmt_bind_param($stmt, "ii", $intReportDateID, $intLocID);
            mysqli_stmt_bind_result($stmt, $intReportID);
            mysqli_stmt_execute($stmt);

            $strReportIDResult = 0;

            while (mysqli_stmt_fetch($stmt)) 
            {
                $strReportIDResult = $intReportID;
            }
        }   


        if ($strReportIDResult == "0")
        {

            $strInsertQuery = "INSERT INTO tbl_TABC_Reports (LocID,ReportDateID,TaxReceipts) VALUES (?, ?, ?)";

            if (mysqli_stmt_prepare($stmt, $strInsertQuery)) 
            {
              mysqli_stmt_bind_param($stmt, "iid", $field1, $field2, $field3);

              $field1 = $intLocID;
              $field2 = $intReportDateID;
              $field3 = trim($data[9]);

              mysqli_stmt_execute($stmt);

              echo "New report<br>
";
            }
        }
        else { echo "<b>Already reported</b><br>"; }

    }
    echo "Closing file now";
    fclose($handle);
}

mysqli_close($dbh);
}

The error from the log is this:

[2436594] [fcgid:warn] (104)Connection reset by peer: [client xxx] mod_fcgid: error reading data from FastCGI server, referer (my webpage address)

[fcgid:warn] (104)Connection reset by peer: [client xxx] mod_fcgid: ap_pass_brigade failed in handle_request_ipc function, referer (my webpage address)

Edit 12/15 (Pulled prepared statements outside of loop). Now I'm still getting "Number of variables don't match in prepared statement" errors:

$sql1 = "SELECT LocID FROM tbl_TABC_Locations WHERE LocPermitNo = ?";
$ps_ChkPermit = mysqli_stmt_prepare($stmt, $sql1);

if ($ps_ChkPermit)
{
mysqli_stmt_bind_param($stmt, "s", $strPermitNo);
mysqli_stmt_bind_result($stmt, $intLocID);
mysqli_stmt_execute($stmt);
...
}

展开全部

  • 写回答

1条回答 默认 最新

  • douhan5853 2013-12-14 13:54
    关注

    In general, you want to remove as much computation from within a loop to outside of the loop. Because every line of code gets executed over and over again during each iteration of the loop.

    @MikeW's suggesting that you try to slim down your script's resource requirements as much as possible. Consider the following example (untested code!):

    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
    
        $sql = "SELECT LocID FROM tbl_TABC_Locations WHERE LocPermitNo = ?";
    
        if (mysqli_stmt_prepare($stmt, $sql)) // <-- this keeps running every time.
        {
            mysqli_stmt_bind_param($stmt, "s", $strPermitNo);
            mysqli_stmt_bind_result($stmt, $intLocID);
            mysqli_stmt_execute($stmt);
            ...
        }
    }
    

    Why prepare your SQL statement over and over if its going to be the same every time?

    $sql = "SELECT LocID FROM tbl_TABC_Locations WHERE LocPermitNo = ?";
    
    // this statement gets prepped outside the loop and runs only once.
    $prepared_statement = mysqli_stmt_prepare($stmt, $sql);
    
    // loop starts...
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
    
        if ($prepared_statement)
        {
            // and then you simply bind the params and execute from within the loop.
            mysqli_stmt_bind_param($stmt, "s", $strPermitNo);
            mysqli_stmt_bind_result($stmt, $intLocID);
            mysqli_stmt_execute($stmt);
            ...
        }
    }
    

    This way, you save on resources especially when you have to process many lines in your CSV.

    Of course, it will mean that you need different variable names for different queries so you can identify each one. Do this right around where you currently declare $stmt

    The second part of his suggestion involves a little more work. To reduce the number of queries, you can create a new field in the database which includes both the YEAR and MONTH and set this as a UNIQUE index in mysql. That way if you try to insert an existing record, mysql will throw and error.

    If there is an error while inserting, you can assume you have a report for that date. If you don't have an error then the report is new.

    Then you don't have the extra step of preparing yet another query just to check for a report's existence!

    As I pointed out above, you can also reduce the CSV file sizes so it doesn't take too long to complete.

    Further to @halfer's suggestion, it might be simpler to run this script with PHP-CLI. No memory limit timeouts - but this will mean you need to save the uploaded files somewhere and use cron tasks to process them later...

    Requires some familiarity with the command line :)

    Hope this clears stuff up a little... Good luck!

    展开全部

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
编辑
预览

报告相同问题?

悬赏问题

  • ¥15 雄安新区高光谱数据集的下载网址打不开
  • ¥66 android运行时native和graphics内存详细信息获取
  • ¥100 求一个c#通过CH341读取数据的Demo,能够读取指定地址值的功能
  • ¥15 rk3566 Android11 USB摄像头 微信
  • ¥15 torch框架下的强化学习DQN训练奖励值浮动过低,希望指导如何调整
  • ¥35 西门子博图v16安装密钥提示CryptAcquireContext MS_DEF_PROV Error of containger opening
  • ¥15 mes系统扫码追溯功能
  • ¥40 selenium访问信用中国
  • ¥20 在搭建fabric网络过程中遇到“无法使用新的生命周期”的报错
  • ¥15 Python中关于代码运行报错的问题
手机看
程序员都在用的中文IT技术交流社区

程序员都在用的中文IT技术交流社区

专业的中文 IT 技术社区,与千万技术人共成长

专业的中文 IT 技术社区,与千万技术人共成长

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

客服 返回
顶部