Help guys to optimize the code! Loaded for a long time and the goods are not so much! Here is the code itself, maybe I made some primitive errors - I will be very grateful!

<?php $start = microtime(true); date_default_timezone_set('Europe/Kiev'); include('db_bace.php'); $connect_to_db = mysql_connect($db_host, $db_username, $db_password) or die("Could not connect: " . mysql_error()); mysql_query("SET NAMES utf8"); mysql_select_db($db_name, $connect_to_db) or die("Could not select DB: " . mysql_error()); $qrs_result = mysql_query("select * from oc_category ") or die(mysql_error()); while ($data_s = mysql_fetch_array($qrs_result)) { $category_id = $data_s['category_id']; $parent_id = $data_s['parent_id']; $advx_result = mysql_query("select * from oc_category_description where (category_id = $category_id)") or die(mysql_error()); $oc_category_description = mysql_fetch_array($advx_result); if ($parent_id == 0) { $cat_text .= ("<category id=\"$category_id\">" . $oc_category_description['name'] . "</category>"); } else { $cat_text .= ("<category id=\"$category_id\" parentID=\"$parent_id\">" . $oc_category_description['name'] . "</category>"); } } $qr_result = mysql_query("select * from oc_product ") or die(mysql_error()); while ($data = mysql_fetch_array($qr_result)) { $product_id = $data['product_id']; $manufacturer_id = $data['manufacturer_id']; $ad_result = mysql_query("select * from oc_product_description where (product_id = $product_id)") or die(mysql_error()); $oc_product_description = mysql_fetch_array($ad_result); $dad_result = mysql_query("select * from oc_product_to_category where (product_id = $product_id)") or die(mysql_error()); while ($oc_product_to_category = mysql_fetch_array($dad_result)) { $oc_product_to_category2 = $oc_product_to_category['category_id']; $dad_result23 = mysql_query("select * from oc_category where (category_id = $oc_product_to_category2)") or die(mysql_error()); $oc_product_description33 = mysql_fetch_array($dad_result23); $category_id23 = $oc_product_description33['category_id']; $parent_id23 = $oc_product_description33['parent_id']; if ($parent_id23 == 0) { $cat_azamat = ("$category_id23"); } else { $cat_azamat = ("$parent_id23"); } } $dads_result = mysql_query("select * from oc_url_alias where (query = 'product_id=$product_id')") or die(mysql_error()); $oc_url_alias = mysql_fetch_array($dads_result); $dsdf_result = mysql_query("select * from oc_manufacturer where (manufacturer_id = $manufacturer_id)") or die(mysql_error()); $oc_manufacturer = mysql_fetch_array($dsdf_result); $text .= ("<item id=\"$product_id\" available=\"true\"> <name>" . $oc_product_description['name'] . "</name> <categoryId>$cat_azamat</categoryId> <price>" . $data['price'] . "</price> <url>site.ru" . $oc_url_alias['keyword'] . "</url> <picture>site.ruimage/" . $data['image'] . "</picture> <vendor>" . $oc_manufacturer['name'] . "</vendor> <description> " . $oc_product_description['description'] . " </description> <warranty>1</warranty> </item>"); } $parse_date = date("Ymd"); $parse_time = date("H:i"); $full_text = ("<price date=\"$parse_date $parse_time\"> <name>Тайтл</name> <currency id=\"UAH\" rate=\"1\" plus=\"0\"/> <catalog>" . $cat_text . "</catalog> <items>" . $text . "</items> </price>"); echo $full_text; $file_name = '/var/www/***/data/www/***/parser.xml'; $w = fopen($file_name, 'w'); fwrite($w, $full_text); fclose($w); mysql_close($connect_to_db); $time = microtime(true) - $start; printf('Скрипт выполнялся %.4F сек.', $time); ?>
  • I am too lazy to read, but it is not clear why you are referring to the same table several times - splash58
  • Instead of select *, you first need to use the enumeration of only the necessary columns. Further you climb into Google find the elementary textbook on SQL. Get rid of the cycles reading one table according to another table - convert them into slightly more complex queries that select the necessary data from several tables at once (this is the basic level of SQL, you can study in a day or two) - Mike

1 answer 1

Data should be linked by means of the DBMS Modify the SQL query so that there are fewer database accesses. Select only the necessary data from several tables at once, defining the relationships between the tables in the SQL query.

Here is this piece of code.

 $qrs_result = mysql_query("select * from oc_category ") or die(mysql_error()); while($data_s = mysql_fetch_array($qrs_result)){ $category_id = $data_s['category_id']; $parent_id = $data_s['parent_id']; $advx_result = mysql_query("select * from oc_category_description where (category_id = $category_id)") or die(mysql_error()); $oc_category_description = mysql_fetch_array($advx_result); if ($parent_id == 0) { $cat_text .= ("<category id=\"$category_id\">".$oc_category_description['name']."</category>"); } else { $cat_text .= ("<category id=\"$category_id\" parentID=\"$parent_id\">".$oc_category_description['name']."</category>"); } }

can be cut approximately (wrote blindly) like this:

 $qrs_result = mysql_query("select c.category_id, c.parent_id, d.name from oc_category c, oc_category_description d where d.category_id = c.category_id") or die(mysql_error()); while($data_s = mysql_fetch_array($qrs_result)) $cat_text .= "<category id=\" . $data_s['category_id'] . "\"" . ($data_s['parent_id'] > 0 ? " parentID=\"".$data_s['parent_id']."\"") . ">".$data_s['name']."</category>");