I executed a PHP script that (Part 1) pulled out a set of GET parameters and API authentication keys from MySQL. (Part 2) built multiple HTTP GET queries for an API and sent it using cURL (the maximum is 100 per authentication key). (Part 3) parsed the XML response from the API. (Part 4) built a MySQL INSERT query (using foreach loops and concatenation) which was then to be inserted into MySQL using mysqli functions.
The code for Part 4 is as follow:
$dbQuery1 = 'INSERT INTO `MARC Records`(`Query`,`Record`, `OCLC Number`, `ISBN`, `Number of Fields`) VALUES (';
d($dbQuery1);
foreach ($XMLtexts as $query => $querygr) {
echo "in loop first level";
d($query);
foreach ($querygr as $xmltext) {
$xmltext = addslashes($xmltext);
$oclcn = addslashes(parseXML($xmltext, "oclcn", "number", 1));
$isbn = addslashes(parseXML($xmltext, "isbn", "number", 1));
$fieldn = addslashes(parseXML($xmltext, "fieldn", "number", 1));
$dbQuery1 .= "'".$query."', '".$xmltext."','".$oclcn."', '".$isbn."', '".$fieldn."'), (";
d($oclcn, $isbn, $fieldn, $dbQuery1);
}
}
$dbQuery1 = substr($dbQuery1, 0, -3);
$mysqli = new mysqli('localhost','root','root','OCLC');
//Output any connection error
if ($mysqli->connect_error) {
die('Error : ('. $mysqli->connect_errno .') '. $mysqli->connect_error);
}
//MySqli Insert Query
$insert = $mysqli->query($dbQuery1);
**NOTE: d() is a custom function I use that is similar to var_dump().
In a test run: I pulled out 1 authentication key. I built 10 API HTTP GET queries for this key. The parsing of the XML responses that was returned for each query broke down each response into 100 child nodes, each of which was to be inserted as one record in MySQL. So it's about 10X1000 = 1000 rows to add to MySQL. My code was to build a single INSERT query for these 1000 rows of data. There are 5 fields.
My script went past all Parts till it reached Part 4, when it stopped after concatenating the 46th row of data to my SQL query. I did not see any error output. The SQL query was 231 066 characters long. It didn't reach the point of insertion into MySQL database.
Why could this have happened and what can I do to solve it? Actually, the script has to handle as many as 10keysX100requestsX100rows = 100 000 rows and maybe more, and I was only doing a test for 1000 rows and even so it stopped. What must I do so it can handle that many rows? (Although I am hoping for answers that assumed I won't change me using MySQL and MAMP, I welcome any recommendations that also require me to fundamentally change the software(or hardware) involved.)
NOTE: I have checked the XML that was supposed to be parsed when the script stopped. It is well-formed as per a validator, so it is unlikely to cause the problem:
<record xmlns="http://www.loc.gov/MARC21/slim">
<leader>00000cam a2200000Mi 4500</leader>
<controlfield tag="001">920683102</controlfield>
<controlfield tag="008">150908t20152015si a f b 000 0 eng d</controlfield>
<datafield ind1=" " ind2=" " tag="020">
<subfield code="a">9789814641463 (rel)</subfield>
</datafield>
<datafield ind1=" " ind2=" " tag="020">
<subfield code="a">9814641464 (rel)</subfield>
</datafield>
<datafield ind1="0" ind2="0" tag="245">
<subfield code="a">Computer arithmetic.</subfield>
<subfield code="n">Vol. II /</subfield>
<subfield code="c">editor Earl E. Swartzlander, University of Texas at Austin, USA.</subfield>
</datafield>
<datafield ind1=" " ind2=" " tag="260">
<subfield code="a">Hackensack, NJ ;</subfield>
<subfield code="a">London ;</subfield>
<subfield code="a">Singapore :</subfield>
<subfield code="b">World Scientific,</subfield>
<subfield code="c">cop. 2015.</subfield>
</datafield>
<datafield ind1=" " ind2=" " tag="300">
<subfield code="a">1 vol. (XXXVIII-446 p.) ;</subfield>
<subfield code="c">29 cm.</subfield>
</datafield>
<datafield ind1=" " ind2="0" tag="650">
<subfield code="a">Computer programming.</subfield>
</datafield>
<datafield ind1=" " ind2="0" tag="650">
<subfield code="a">Floating-point arithmetic.</subfield>
</datafield>
<datafield ind1=" " ind2="0" tag="650">
<subfield code="a">Computer arithmetic.</subfield>
</datafield>
<datafield ind1=" " ind2="7" tag="650">
<subfield code="a">Arithmétique interne des ordinateurs.</subfield>
<subfield code="2">ram</subfield>
</datafield>
<datafield ind1=" " ind2="7" tag="650">
<subfield code="a">Arithmétique en virgule flottante.</subfield>
<subfield code="2">ram</subfield>
</datafield>
<datafield ind1=" " ind2="7" tag="650">
<subfield code="a">Computer arithmetic.</subfield>
<subfield code="2">fast</subfield>
<subfield code="0">(OCoLC)fst00872029</subfield>
</datafield>
<datafield ind1=" " ind2="7" tag="650">
<subfield code="a">Computer programming.</subfield>
<subfield code="2">fast</subfield>
<subfield code="0">(OCoLC)fst00872390</subfield>
</datafield>
<datafield ind1=" " ind2="7" tag="650">
<subfield code="a">Floating-point arithmetic.</subfield>
<subfield code="2">fast</subfield>
<subfield code="0">(OCoLC)fst00927429</subfield>
</datafield>
<datafield ind1="1" ind2=" " tag="700">
<subfield code="a">Swartzlander, Earl E..</subfield>
<subfield code="4">edt</subfield>
</datafield>
</record>
I have also, as per advice that my question might be a duplicate of How to increase apache timeout directive in .htaccess?, perused the replies there and made adjustments to my php.ini file:
max_execution_time 21600
