php - Why XMLReader repeat mysql insert function inside while and can't stop? -
i using xmlreader import huge xml file elements mysql database. xml contains 1 547 772 tags (element) named 'record'.
xml example
<?xml version="1.0" encoding="utf-8"?> <record><name>ДОШКІЛЬНИЙ НАВЧАЛЬНИЙ ЗАКЛАД №1 ЗАГАЛЬНОГО РОЗВИТКУ УЖГОРОДСЬКОЇ МІСЬКОЇ РАДИ ЗАКАРПАТСЬКОЇ ОБЛАСТІ</name><short_name>ДНЗ №1</short_name><edrpou>34888585</edrpou><address>88000, Закарпатська обл., місто Ужгород, ВУЛИЦЯ М.ВОВЧКА, будинок 47, "А"</address><boss>НАКОНЕЧНА ОЛЕНА АНАТОЛІЇВНА</boss><kved>85.10 Дошкільна освіта</kved><stan>зареєстровано</stan><founders><founder>УПРАВЛІННЯ ОСВІТИ УЖГОРОДСЬКОЇ МІСЬКОЇ РАДИ, розмір внеску до статутного фонду - 0.00 грн.</founder>...</founders></record>...
for mysql connection use
function connectbase(){ include __dir__ . '/../../settings/sql.set.php'; $mysql = mysqli_connect($_sqlhost, $_sqluser, $_sqlpass, $_sqldb); mysqli_query($mysql, "set charset utf8"); mysqli_query($mysql, "set character_set_client = utf8"); mysqli_query($mysql, "set character_set_connection = utf8"); mysqli_query($mysql, "set character_set_results = utf8"); mysqli_query($mysql, "set collation_connection = utf8_general_ci"); return $mysql; }
main function parsing
function xmlreadertodb($setting = false) { $mysql = connectbase(); $dir = __dir__ . '/../../tmp/'; $xmlurl = $dir . $setting['file']; $xml = new xmlreader(); $xml->open($xmlurl); $start_time = time(); $start = $setting['start']; $limit = $setting['limit']; $stop = $start + $limit; $i = 0; $count = 0; $result = 1; while($xml->read()) { if ($xml->nodetype == xmlreader::element && $xml->name == $setting['tag']) { $item[] = "('items', '" . mysqli_real_escape_string($mysql, $xml->readouterxml()) . "')"; } if ($xml->nodetype == xmlreader::end_element && $xml->name == $setting['tag']) { $i++; $count++; if ($count >= 500) { insertxmltodb($mysql, $item); $item = array(); $count = 0; } } if($i == $stop){ break; } } $xml->close(); insertxmltodb($mysql, $item); $mysql->close(); $end_time = time(); $time_elapsed_secs = $end_time - $start_time; echo '<br/>items: ' . $i . '<br/>'; echo 'start: ' . date('h:i:s', $start_time) . '<br/>'; echo 'end: ' . date('h:i:s', $end_time) . '<br/>'; echo $time_elapsed_secs . ' sec. (' . ($time_elapsed_secs /60) . ' min.)'; die; }
and mysql insert
function insertxmltodb($mysql, $data = false){ mysqli_query($mysql,"insert _parse_tmp (parse_key, parse_value) values " . implode(", ", $data)); $data = array(); // echo 'success'; }
but, mysql requests don't stop after 1 547 772 inserts , "while" continues run. notice if uncommenting echo 'success'; in insertxmltodb function, "while" stopping @ 1 547 772 inserts , finishing correctly. wrong in functions?
i've never used xmlreader however, i'd assume try add condition statement such while($xml->read() && $i != $stop)
opposed having within loop directly. ah noticed after usage of function insertxmltodb
re-declare $data
although you'll notice set null every time call function, therefore setting array pointless.
Comments
Post a Comment