How can I optimize the code below?

$xml = simplexml_load_file("xmlfiles/".$row['idzk'].".xml"); foreach ($xml->offer as $offer) { $id= 'internal-id'; $import_obj = mysql_query("SELECT `internal-id` FROM object WHERE `internal-id`='$offer[$id]' "); if(mysql_fetch_array($import_obj)){ echo '1'; } else { echo '2'; } }
  • If the essence of the code, check whether there are records from XML in the database, then write in your question. If they can be withdrawn in a pack, then write about it too. How much data do you have in this XML 100 lines, or 100 thousand? How much data is in the database? Are there indices on the sample field. And go from using mysql_* to something more modern. And what’s the point of phpmyadmin - teran
  • check if there is an id in the database where there are about 45000 lines, we take id from XML? 2) - Victor Vasilyev
  • "And go from the use of mysql_ * to something more modern" - this is what?) - Victor Vasiliev
  • @VictorVasilyev to PDO, for example. - D-side
  • 2
    в результате вместо 1 мы обновляем данные в БД, 2 добавляем новые - This should be in question! - vp_arth

1 answer 1

The problem with your code is that you make N requests to the database where you can do with one.

 $xml = simplexml_load_file("xmlfiles/".$row['idzk'].".xml"); $offers = iterator_to_array($xml->offer); if (count($offers) == 0) die('No ids'); $ids = array_map(function($offer){ //return (int)$offer['internal-id']; return "'".mysql_real_escape_string($offer['internal-id'])."'"; }, $offers); $in = implode(', ', $ids); $sql = "select `internal-id` from `object` where `internal-id` IN ($in)"; $r = mysql_query($sql); if (!$r) die(mysql_error()); $exists = []; while ($row = mysql_fetch_array($r, MYSQL_ASSOC)) { $exists[$row['internal-id']] = true; } foreach ($offers as $offer) { if (isset($exists[$offer['internal-id']])) { echo "Найден {$offer['internal-id']}\n"; } else { echo "Не найден {$offer['internal-id']}\n"; } }

If there are too many lines, you can send in batches, say 1000

For a task from comments, you can create a unique index across the field internal-id and use the on duplicate key update construction:

 $offers = iterator_to_array($xml->offer); $values = array_map(function($offer) { // нужно экранировать значения здесь по необходимости //return '('.implode(', ', [(int)$offer['internal-id'], (int)$offer['a']]).')'; return '('.implode(',', [ "'".mysql_real_escape_string($offer['internal-id'])."'", int($offer['a']), ]).')'; }, $offers); $values = implode(",\n", $values); $sql = <<<SQL INSERT INTO `object` (`internal-key`, `a`) VALUES $values ON DUPLICATE KEY UPDATE a = VALUES(a) SQL;

mysql_

Warning: This extension is deprecated since PHP 5.5.0 and removed in PHP 7.0.0. Use MySQLi or PDO_MySQL instead . See also the MySQL instruction : API selection and the corresponding FAQ for more details.

  • On the line if (! $ R) die (mysql_error ()); displays the error You have an error in your SQL syntax; the right line, you’ll be able to check it out. ”at line 1 php 5.4, MySQL 5.5.52-1.el7 - Victor Vasiliev
  • For SELECT, SHOW, DESCRIBE, EXPLAIN, and other queries that return a result from multiple rows, mysql_query () returns a handle to the result of the query (resource), or FALSE if an error occurs. - vp_arth
  • I understand that there is a syntax error, but I do not understand what it can be in the if (! $ R) line die (mysql_error ()); - Victor Vasiliev
  • Everything as you wrote did) - Victor Vasiliev
  • If you do not post a request, I can not help you - vp_arth