php - MySQL查询执行失败

Question

我正在尝试向服务器查询大量数据，这是我的代码:

$queryString = "";

$connect = mysqli_connect("localhost", "username", "password", "database");

$loopLength = 20;
$currentGroup = 1;
$currentLoopAmount = 0;
$queryAmount = 5;

for($v = 0; $v < ceil($loopLength / $queryAmount); $v++){
    //echo "Looping Main: " . $v . "<br>";
    //echo $loopLength - (($currentGroup - 1) * 10) . "<br>";
    if($loopLength - (($currentGroup - 1) * $queryAmount) >= $queryAmount){
        $currentLoopAmount = $queryAmount;
    }
    else{
        $currentLoopAmount = $loopLength - (($currentGroup - 1) * $queryAmount);
    }

    //echo $currentLoopAmount;

    $queryString = "";

    for($q = (($currentGroup - 1) * $queryAmount); $q < $currentLoopAmount + (($currentGroup - 1) * $queryAmount); $q++){
        //echo "&nbsp;&nbsp;Looping Sub: " . $q . "<br>";
        $tempVariable = grabPageData($URLs[$q], $q);

        $queryString .= $tempVariable;
        if($q < $loopLength-1){
            $queryString .= ",";
        }
        else{
            $queryString .= ";";
        }
    }

    echo $queryString;

    $query = "INSERT INTO PublicNoticesTable (url, county, paperco, date, notice, id) VALUES " . $queryString;
    $result = mysqli_query($connect, $query);

    if($result){
        echo "Success";
    }
    else{
        echo "Failed : " . mysqli_error($connect) . "<br>";
    }

    $currentGroup += 1;
}

$loopLength 变量是动态的，可以是数千甚至数十万。我设计此功能是为了将大量数据分成一批较小的查询，因为我无法通过 GoDaddy 在我的共享托管服务上一次上传所有数据。 $queryAmount 变量表示较小的查询有多大。

这是插入到表中的值集之一的示例:这是我的代码在grabPageData()函数中获取到的公告数据。

('http://www.publicnoticeads.com/az/search/view.asp?T=PN&id=37/7292017_24266919.htm','Pima','Green Valley News and Sun','2017/07/30',' ___________________________ARIZONA SUPERIOR COURT, PIMA COUNTYIn the Matter of the Estate of:JOSEPH T, DILLARD, SR.,Deceased.DOB: 10/09/1931No. PB20170865NOTICE TO CREDITORS(FOR PUBLICATION)NOTICE IS HEREBY GIVEN that DANA ANN DILLARD CALL has been appointed Personal Representative of this Estate. All persons having claims against the Estate are required to present their claimswithin four months after the date of the firat publication of this notice or the claims will be forever barred. Claims must be presented by delivering or mailing a written statement of the claim to the Personal Representative at the Law Offices of Michael W. Murray, 257 North Stone Avenue, Tucson, Arizona 85701.DATED this 17th day of July, 2017./S/ Micahel W. MurrayAttorney for the Personal RepresentativePub: Green Valley News & SunDate: July 23, 30, August 6, 2017 Public Notice ID: 24266919',' 24266919'),

为了获得这些数据，我通过一个函数来运行它，该函数可以爬取页面并抓取它。然后我把网页html代码通过这个函数:

function cleanData($data){
    $data = strip_tags($data);
    //$data = preg_replace("/[^a-zA-Z0-9]/", "", $data);
    //$data = mysql_real_escape_string($data);
    return $data;
}

如上所示，它为我提供了没有标签的内容。这就是问题所在。

函数执行，一切看起来都很漂亮。然后函数(取决于 $queryAmount 变量，为了问题的缘故，我不会保留超过 10 个)输出，正如您在函数中看到的那样......

Failed : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

奇怪的是，当我有大量数据时，比如说 $loopLength 变量是 116。结果将输出，“Failed : (error)Failed : (Error)Fai. ..(Error)Success. 所以它实际上只是在查询最后一组数据？？？不确定。

我不确定如何解决这个问题，我想重新审视一下。有人可以帮帮我吗？我一直在研究这个问题......几个小时试图找到解决方案。

很抱歉让这个问题成为一个痛苦的问题:(

编辑:

我将之前的代码更改为使用 mysql 准备语句，什么不是......见下文:

$grabDataResults = [
        "url" => "",
        "county" => "",
        "paperco" => "",
        "date" => "",
        "notice" => "",
        "id" => "",
    ];

$connect = mysqli_connect("localhost", "bwt_admin", "Thebeast1398", "NewCoDatabase");

if($stmt = mysqli_prepare($connect, "INSERT INTO PublicNoticesTable (url, county, paperco, date, notice, id) VALUES (?, ?, ?, ?, ?, ?)")){

mysqli_stmt_bind_param($stmt, 'ssssss', $grabDataResults["url"], $grabDataResults["county"], $grabDataResults["paperco"], $grabDataResults["date"], $grabDataResults["notice"], $grabDataResults["id"]);

$loopLength = 1;

for($v = 0; $v < $loopLength; $v++){
    $grabDataResults = grabPageData($URLs[$v], $v);
    mysqli_stmt_execute($stmt);
    printf("%d Row inserted.\n", mysqli_stmt_affected_rows($stmt));
    printf("Error:\n", mysqli_stmt_error($stmt));
    echo "(" . $grabDataResults["url"] . "," . $grabDataResults["county"] . "," . $grabDataResults["paperco"] . "," . $grabDataResults["date"] . "," . $grabDataResults["notice"] . "," . $grabDataResults["id"] . ")";
}

mysqli_stmt_close($stmt);

mysqli_close($connect);
}

不幸的是，这是我从输出中得到的:

1 Row inserted. 0 Error: 

实际上没有错误打印出来，并且插入了该行。但是，当我导航到我的数据库并查看已存储的值时……它们都是空的。 echo 语句输出如下:

(http://www.publicnoticeads.com/az/search/view.asp?T=PN&id=31/7292017_24266963.htm,Yuma,Sun (Yuma), The,2017/07/30,, 24266963)

所以我知道所有变量都包含一些东西，除了 $notice 变量，它出于某种原因被我的 cleanData() 函数破坏了。

Answer 1

您需要在获取数据之后和执行之前绑定(bind)数据...

$loopLength = 1;

for($v = 0; $v < $loopLength; $v++){
    $grabDataResults = grabPageData($URLs[$v], $v);

    mysqli_stmt_bind_param($stmt, 'ssssss', $grabDataResults["url"],
           $grabDataResults["county"], $grabDataResults["paperco"], 
           $grabDataResults["date"], $grabDataResults["notice"], 
           $grabDataResults["id"]);

    mysqli_stmt_execute($stmt);
    printf("%d Row inserted.\n", mysqli_stmt_affected_rows($stmt));
    printf("Error:\n", mysqli_stmt_error($stmt));
    echo "(" . $grabDataResults["url"] . "," . $grabDataResults["county"] . "," . $grabDataResults["paperco"] . "," . $grabDataResults["date"] . "," . $grabDataResults["notice"] . "," . $grabDataResults["id"] . ")";
}