MySQL query fails to execute

Question

I am trying to query large amounts of data to a server, here is my code for that:

$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;
}

The $loopLength variable is dynamic and can be in the thousands or potentially hundred thousands. I designed this function to divide that massive number into a batch of smaller queries as I couldn't upload all the data at one time on my shared hosting service through GoDaddy. The $queryAmount variable represents how big the smaller queries are.

Here is an example of one of the value sets that gets inserted into the table: It is the data from a public notice that my code retrieved in the grabPageData() function.

('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'),

To attain this data, I run it through a function that crawls the page and grabs it. Then I put the webpage html code through this function:

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

Which gives me the content without tags as you see above. Here's the problem.

The function executes and everything seems just dandy. Then the function (depending on the $queryAmount variable which I don't keep over 10 for problem's sake) outputs, as you can see it would in the function something like...

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

The weird part is that when I have large amounts of data like say the $loopLength variable is like 116. The result will output, "Failed : (error)Failed : (Error)Fai...(Error)Success. So it's only actually querying the last set of data??? Not sure.

I am not sure how to fix this and I want a fresh eye. Can somebody help me please. I have been working on this problem for... several hours trying to find solution.

Sorry for making this question a pain in the butt :(

EDIT:

I changed the code from previously to use mysql prepared statements and what not... See below:

$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);
}

Unfortunately this is what I get from the output:

1 Row inserted. 0 Error: 

No error actually prints out and the row is inserted. However when I navigate to my database, and look at the values that have been stored.. They are all empty. The echo statement outputs this:

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

So I know that all of the variables contain something except for the $notice variable which gets destroyed by my cleanData() function for some reason.

Answer 1

The main error I can see on your query, are the query itself. You're using a INSERT INTO with separated fields and values. But you forget to use the pharentesis on values.

Remember, the use of INSERT INTO are as follows:

First option:
INSERT INTO table field1 = value1, field2 = value2;

Second option:
INSERT INTO table (field1, field2) VALUES (value1, value2);

Also, remember to escape every field and value for avoid more errors: Example:

First option:
INSERT INTO `table` `field1` = 'value1', `field2` = 'value2';

Second option:
INSERT INTO `table` (`field1`, `field2`) VALUES ('value1', 'value2');

If you're using mysqli driver, for more security, you can use prepared statements, to get your values automatically escaped. In that case, the syntax of the query are as follows:

First option:
INSERT INTO `table` `field1` = ?, `field2` = ?;

Second option:
INSERT INTO `table` (`field1`, `field2`) VALUES (?, ?);

Also, instead of using mysqli_query(), you need to use mysqli_prepare(), mysqli_bind_param() and mysqli_execute(). You can check more data about they syntax here: http://php.net/manual/en/mysqli.prepare.php

At least, you can use mysqli_real_escape_string() function for getting your input properly escaped and checked. You can check documentation of this function here: http://php.net/manual/en/mysqli.real-escape-string.php

Answer 2

You code is correct. Just you need to add () arround querystring Akso you need to remove ; from query string end. SO remove following else condition

else{
    $queryString .= ";";
}

change you query like :

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

Also it advisable to use prepared statements to prevent from sql injections

Answer 3

You need to bind the data after fetching it and before executing it...

$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"] . ")";
}