Integration of TecDoc DB with an online auto parts store
The writing of the article was the result of working in a rather interesting project, the result of which should be an online store, with the possibility of linking the nomenclature to the “TecDoc” parts catalog.
“TecDoc” product is a kind of database that includes not only links between manufacturers of spare parts and specific parts of a catalog, but also contains images of goods, as well as the recommended price and, most importantly, the ability to search for analogues.
To link the site with the catalog «TecDoc», its database had to be downloaded to MySQL. We will not dwell on this, since this information can be found on the World Wide Web without problems.
This online store sells parts for cars.
')
The basis of the online store - CMS "HostCMS" . Since the customer had a license for this CMS, he had to work, so to speak, with what is. Although out of the box, this product can also please with its capabilities.
The main refinement of the online store is the possibility of linking the goods to the catalog - the “TecDoc” database, taking into account that the nomenclature can be downloaded either from the price list in the * .xls format, or manually - through the control panel of the site.
For the user, there are tools to find the product in the store:
Now for more details on solving each of the tasks.
The very first task was to load a new item from the generated price list.
Since the price list was in the * .xls format, and there is no ready tool for working with this format in this CMS - it was decided to use “PHPExcel”
"PHPExcel" is a fairly handy tool that allows you not only to read data from a file, but also to generate its own files.
In order for everything to look beautiful, we created a module in the control panel of the site and displayed a link to it in the list of installed modules.
After clicking on the link, the form for downloading the file is displayed to the administrator. If you look at this part from the programmer's side, everything is standard, that is, a form has been created, the file is received, renamed, and transferred to the correct directory. It is advisable to add all sorts of checks like file type, protection against "XSS", etc.
In the form, the administrator selects a file that should successfully upload to the server.
So the file is uploaded to the server. Now we have two options:
1. Run the entire file for processing. A good option, but if there are tens of thousands of records there, then the import process will be uncontrollable.
2. The second option, we used it.
2.1 After downloading a file, the number of filled lines is determined.
2.2 Generated script that sends line numbers for processing
2.2.1 Import process is displayed in percent
2.2.2 The result of import is displayed in the form of a table growing down during the import.
2.3 From the resulting table, the administrator can see the result and possible actions on each item.
For specifics, here is an example of products from the price list.
When the script sends line numbers to be processed, we will receive an array of properties for each item. That is, each line in the price list corresponds to a separate product, and for this product we pull out properties such as price, quantity, analogs of the “TecDoc” catalog, etc.
If you need to create a tool in which the price list fields can change, you should write an identifying attribute in the first row of each column (for example, 'V.numb', 'Name.manufacturer', etc.), and the second line will be the title for columns. As a result, the import will have to start from the third line. And before importing, we need to determine the fields we need, based on the identification attributes.
Our further steps are to link the internal number to the catalog goods of the store and the TecDoc base.
1. Search the catalog by internal number
2. If the product is found, the characteristics for it are updated (such as prices and quantity)
3. If the product is not found in the store do a search on "TecDoc"
3.1 If for a given original number and corresponding to it analogues found 1 product go to the "addition of a new product"
3.2 If several products are found, we display a link to “select one of the goods”
3.3 If the product is not found, we give a link to "the choice of any product"
3.4 If there are several products or none have been found, then we make modifications to the analogue records (deleting unnecessary characters or splitting into an array, when there are several numbers in the same field, separated by the symbol “/”). If, after performing operations with values, one position is found, we perform “ adding a new product "
4. “Adding a new product” - a new product is created in the catalog, based on the fields in the price list, the missing properties of the product receive default values. The entry “Added” appears in the result table.
5. "Select one of the goods" - a dialog box, is the result of the script. The script is activated by clicking on the product, for which several positions are found in TecDoc. It accepts the found “TecDoc” position numbers, and allows you to select the necessary one among them:
6. "Select any product" - the dialog box is the result of the script. The script is activated by clicking on the product, for which no positions were found in TecDoc and allows you to select the necessary part among all manufacturers:
Appearance of the module:
Import process:
All queries can be found using the search on the Internet.
The given queries were modified for the specialization of a particular task, as well as for optimizations. But much can be understood by reviewing the following tasks:
Task 1. Search for image address
Add the path to the image catalog, make the existence check, resize - if needed, and you can safely display it on the site.
Task 2. Search for analogues:
This request can be used when importing goods, slightly modify it in order to verify the compliance of this product and its counterparts.
Task 3. Displaying information about the product
Do not forget about @LNG_ID - and put there a value, for example equal to 16, for the Russian language.
Catalog
The easiest option is when a visitor enters the catalog, he is listed on all the products, divided into pages. Here, I think, there are no questions. The programmer displays all the products catalog.
Filtration
As a filter, you can create a category tree, and allow the visitor to navigate in it. For example:
-Cars
--Body
--- Body Parts / Fender / Buffer
--- Glazing / mirrors
--- Covers / hoods / doors / sliding roof / folding roof
--- Additional headlight / accessories
--- Lighting system / alarm
--Engine
--- Gaskets
---Lubrication system
---Cylinder head
--- Air supply system
- Release system
--- Installation details
--- Carbamide injection
--- Silencer Assembly
---Catalyst
---The Lambda probe
And now when choosing a filter to display, only those parts of the catalog that are associated with this category, that is, we add filtering by category, thanks to the binding to the position from “TecDoc”. Updating the list of products in the catalog when choosing a filter can be done both dynamically using Ajax and with updating the page.
Below is the query that displays the tree subcategories for the category cars (10001):
Since the query will be executed literally on each page of the directory, it is desirable to add its result to the cache. Yes, and in general, should study the bottlenecks, giving a large load and delay.
The “Caching” module in this CMS can be very useful. Although if you look around, then probably any framework will allow you to cache the most loaded places on the site.
Search by internal number
This section will not cause questions, because here we check the product number, and if it exists, we will display the page of this position. You can add this section by auto-filling when entering the article number for the user.
Search by original number
It seems that we have everything to solve this problem. We can easily display all the products in the catalog, taking into account the original number or the number of its analogue, added to the additional properties of the product.
But…
The speed of execution of such a query is rather slow Even using all sorts of optimizations for the MySQL server, the result is distressing.
“Sphinx” (search engine) was used more than once in projects, and this technology always allowed only to improve the project. And this time when using “Sphinx” we were surprised:
Corrected the configuration file for the needs of the project, re-indexed, and the search speed really increased, compared with the absence of "Sphinx".
Keyword Search
Since the problem in speed was with the original number, it was decided that the main search should also be done using the “Sphinx” technology.
In addition, this technology allows you to use:
By the way, at the beginning of the project, your car tool was mentioned.
This tool allows you to select a single vehicle modification from the entire TecDoc catalog and view all products related to this vehicle anyway.
The tool is a set of fields that are updated dynamically depending on the choice of the previous field. For example, after choosing the car's production year, the available car brands are selected, then choose the model and type of engine. The result of his work is shown below:
As a result, the main modifications of this car are displayed, among which we choose the one we need. After selecting a car, the catalog will open, but your car will already be the filter, and the site should remember the user's car and allow them to switch between them and choose a new car.
Despite the high threshold of entry, the task was not difficult, but routine. In order to write this module we can advise the study of the following approaches, technologies and tools:
And most importantly, a similar result can be achieved without the use of ready-made engines, but, for example, on your own autos-catalog of cars or on the Internet store, developed using frameworks.
PS As a supplement, I cite a couple of scripts from the project that are at least inconclusive, well, you can understand the essence of them.
The script responsible for splitting the process into requests is responsible for adding from the list, as well as informing about the import process:
The function of adding goods based on the part number and manufacturer
PS To receive our new articles before others or simply not to miss new publications - subscribe to us on Facebook , VK , Twitter , LiveJournal and LinkedIn
PPS Very soon the course starts in our business school Digitov: I want to become Junior PHP Developer! Subscribe to the course now and you can buy it at a discount.
Article prepared
Vladimir, Middle PHP Developer, SECL Group
Nikita, CEO, SECL Group
“TecDoc” product is a kind of database that includes not only links between manufacturers of spare parts and specific parts of a catalog, but also contains images of goods, as well as the recommended price and, most importantly, the ability to search for analogues.
To link the site with the catalog «TecDoc», its database had to be downloaded to MySQL. We will not dwell on this, since this information can be found on the World Wide Web without problems.
This online store sells parts for cars.
')
The basis of the online store - CMS "HostCMS" . Since the customer had a license for this CMS, he had to work, so to speak, with what is. Although out of the box, this product can also please with its capabilities.
The main refinement of the online store is the possibility of linking the goods to the catalog - the “TecDoc” database, taking into account that the nomenclature can be downloaded either from the price list in the * .xls format, or manually - through the control panel of the site.
For the user, there are tools to find the product in the store:
- By name and company
- According to the original number from TecDoc,
- According to the internal catalog number of the online store,
- By category tree,
- And most importantly using the tool "Your car".
Now for more details on solving each of the tasks.
Import goods.
The very first task was to load a new item from the generated price list.
Since the price list was in the * .xls format, and there is no ready tool for working with this format in this CMS - it was decided to use “PHPExcel”
"PHPExcel" is a fairly handy tool that allows you not only to read data from a file, but also to generate its own files.
In order for everything to look beautiful, we created a module in the control panel of the site and displayed a link to it in the list of installed modules.
After clicking on the link, the form for downloading the file is displayed to the administrator. If you look at this part from the programmer's side, everything is standard, that is, a form has been created, the file is received, renamed, and transferred to the correct directory. It is advisable to add all sorts of checks like file type, protection against "XSS", etc.
In the form, the administrator selects a file that should successfully upload to the server.
So the file is uploaded to the server. Now we have two options:
1. Run the entire file for processing. A good option, but if there are tens of thousands of records there, then the import process will be uncontrollable.
2. The second option, we used it.
2.1 After downloading a file, the number of filled lines is determined.
2.2 Generated script that sends line numbers for processing
2.2.1 Import process is displayed in percent
2.2.2 The result of import is displayed in the form of a table growing down during the import.
2.3 From the resulting table, the administrator can see the result and possible actions on each item.
For specifics, here is an example of products from the price list.
When the script sends line numbers to be processed, we will receive an array of properties for each item. That is, each line in the price list corresponds to a separate product, and for this product we pull out properties such as price, quantity, analogs of the “TecDoc” catalog, etc.
If you need to create a tool in which the price list fields can change, you should write an identifying attribute in the first row of each column (for example, 'V.numb', 'Name.manufacturer', etc.), and the second line will be the title for columns. As a result, the import will have to start from the third line. And before importing, we need to determine the fields we need, based on the identification attributes.
Our further steps are to link the internal number to the catalog goods of the store and the TecDoc base.
1. Search the catalog by internal number
2. If the product is found, the characteristics for it are updated (such as prices and quantity)
3. If the product is not found in the store do a search on "TecDoc"
3.1 If for a given original number and corresponding to it analogues found 1 product go to the "addition of a new product"
3.2 If several products are found, we display a link to “select one of the goods”
3.3 If the product is not found, we give a link to "the choice of any product"
3.4 If there are several products or none have been found, then we make modifications to the analogue records (deleting unnecessary characters or splitting into an array, when there are several numbers in the same field, separated by the symbol “/”). If, after performing operations with values, one position is found, we perform “ adding a new product "
4. “Adding a new product” - a new product is created in the catalog, based on the fields in the price list, the missing properties of the product receive default values. The entry “Added” appears in the result table.
5. "Select one of the goods" - a dialog box, is the result of the script. The script is activated by clicking on the product, for which several positions are found in TecDoc. It accepts the found “TecDoc” position numbers, and allows you to select the necessary one among them:
6. "Select any product" - the dialog box is the result of the script. The script is activated by clicking on the product, for which no positions were found in TecDoc and allows you to select the necessary part among all manufacturers:
Appearance of the module:
Import process:
MySQL
All queries can be found using the search on the Internet.
The given queries were modified for the specialization of a particular task, as well as for optimizations. But much can be understood by reviewing the following tasks:
Task 1. Search for image address
SELECT CONCAT(GRA_TAB_NR, '/',GRA_GRD_ID, '.', IF(LOWER(DOC_EXTENSION)='jp2', 'jpg', LOWER(DOC_EXTENSION))) AS PATH FROM LINK_GRA_ART INNER JOIN GRAPHICS ON GRA_ID = LGA_GRA_ID INNER JOIN DOC_TYPES ON DOC_TYPE = GRA_DOC_TYPE WHERE LGA_ART_ID = @ARTICLE AND (GRA_LNG_ID = 16 OR GRA_LNG_ID = 255) AND GRA_DOC_TYPE <> 2 ORDER BY GRA_GRD_ID Add the path to the image catalog, make the existence check, resize - if needed, and you can safely display it on the site.
Task 2. Search for analogues:
SELECT ARL_KIND, IF (ART_LOOKUP.ARL_KIND = 2, SUPPLIERS.SUP_BRAND, BRANDS.BRA_BRAND) AS BRAND, ARL_DISPLAY_NR FROM ART_LOOKUP LEFT JOIN BRANDS ON BRA_ID = ARL_BRA_ID INNER JOIN ARTICLES ON ARTICLES.ART_ID = ART_LOOKUP.ARL_ART_ID INNER JOIN SUPPLIERS ON SUPPLIERS.SUP_ID = ARTICLES.ART_SUP_ID WHERE ARL_ART_ID = @ART_ID AND ARL_KIND IN (2, 3, 4) ORDER BY ARL_KIND, BRA_BRAND, ARL_DISPLAY_NR LIMIT 100 This request can be used when importing goods, slightly modify it in order to verify the compliance of this product and its counterparts.
Task 3. Displaying information about the product
SELECT ART_ARTICLE_NR, SUP_BRAND, DES_TEXTS.TEX_TEXT AS ART_COMPLETE_DES_TEXT, DES_TEXTS2.TEX_TEXT AS ART_DES_TEXT, DES_TEXTS3.TEX_TEXT AS ART_STATUS_TEXT FROM ARTICLES INNER JOIN DESIGNATIONS ON DESIGNATIONS.DES_ID = ART_COMPLETE_DES_ID AND DESIGNATIONS.DES_LNG_ID = @LNG_ID INNER JOIN DES_TEXTS ON DES_TEXTS.TEX_ID = DESIGNATIONS.DES_TEX_ID LEFT JOIN DESIGNATIONS AS DESIGNATIONS2 ON DESIGNATIONS2.DES_ID = ART_DES_ID AND DESIGNATIONS2.DES_LNG_ID = @LNG_ID LEFT JOIN DES_TEXTS AS DES_TEXTS2 ON DES_TEXTS2.TEX_ID = DESIGNATIONS2.DES_TEX_ID INNER JOIN SUPPLIERS ON SUP_ID = ART_SUP_ID INNER JOIN ART_COUNTRY_SPECIFICS ON ACS_ART_ID = ART_ID INNER JOIN DESIGNATIONS AS DESIGNATIONS3 ON DESIGNATIONS3.DES_ID = ACS_KV_STATUS_DES_ID AND DESIGNATIONS3.DES_LNG_ID = @LNG_ID INNER JOIN DES_TEXTS AS DES_TEXTS3 ON DES_TEXTS3.TEX_ID = DESIGNATIONS3.DES_TEX_ID WHERE ART_ID = @ART_ID; Do not forget about @LNG_ID - and put there a value, for example equal to 16, for the Russian language.
Search for goods.
Catalog
The easiest option is when a visitor enters the catalog, he is listed on all the products, divided into pages. Here, I think, there are no questions. The programmer displays all the products catalog.
Filtration
As a filter, you can create a category tree, and allow the visitor to navigate in it. For example:
-Cars
--Body
--- Body Parts / Fender / Buffer
--- Glazing / mirrors
--- Covers / hoods / doors / sliding roof / folding roof
--- Additional headlight / accessories
--- Lighting system / alarm
--Engine
--- Gaskets
---Lubrication system
---Cylinder head
--- Air supply system
- Release system
--- Installation details
--- Carbamide injection
--- Silencer Assembly
---Catalyst
---The Lambda probe
And now when choosing a filter to display, only those parts of the catalog that are associated with this category, that is, we add filtering by category, thanks to the binding to the position from “TecDoc”. Updating the list of products in the catalog when choosing a filter can be done both dynamically using Ajax and with updating the page.
Below is the query that displays the tree subcategories for the category cars (10001):
SELECT STR_ID, STR_ID_PARENT, TEX_TEXT AS STR_DES_TEXT, IF(EXISTS( SELECT * FROM SEARCH_TREE AS SEARCH_TREE2 WHERE SEARCH_TREE2.STR_ID_PARENT <=> SEARCH_TREE.STR_ID LIMIT 1 ), 1, 0) AS DESCENDANTS FROM SEARCH_TREE INNER JOIN DESIGNATIONS ON DES_ID = STR_DES_ID INNER JOIN DES_TEXTS ON TEX_ID = DES_TEX_ID WHERE STR_ID_PARENT <=> 10001 AND DES_LNG_ID = 16; Since the query will be executed literally on each page of the directory, it is desirable to add its result to the cache. Yes, and in general, should study the bottlenecks, giving a large load and delay.
The “Caching” module in this CMS can be very useful. Although if you look around, then probably any framework will allow you to cache the most loaded places on the site.
Search by internal number
This section will not cause questions, because here we check the product number, and if it exists, we will display the page of this position. You can add this section by auto-filling when entering the article number for the user.
Search by original number
It seems that we have everything to solve this problem. We can easily display all the products in the catalog, taking into account the original number or the number of its analogue, added to the additional properties of the product.
But…
The speed of execution of such a query is rather slow Even using all sorts of optimizations for the MySQL server, the result is distressing.
“Sphinx” (search engine) was used more than once in projects, and this technology always allowed only to improve the project. And this time when using “Sphinx” we were surprised:
- Easy integration and customization.
- Indexing speed.
- And finally, the search speed.
Corrected the configuration file for the needs of the project, re-indexed, and the search speed really increased, compared with the absence of "Sphinx".
Keyword Search
Since the problem in speed was with the original number, it was decided that the main search should also be done using the “Sphinx” technology.
In addition, this technology allows you to use:
- additional attributes when searching,
- distributed search
- Russian language support
- create multiple indexes.
By the way, at the beginning of the project, your car tool was mentioned.
This tool allows you to select a single vehicle modification from the entire TecDoc catalog and view all products related to this vehicle anyway.
The tool is a set of fields that are updated dynamically depending on the choice of the previous field. For example, after choosing the car's production year, the available car brands are selected, then choose the model and type of engine. The result of his work is shown below:
As a result, the main modifications of this car are displayed, among which we choose the one we need. After selecting a car, the catalog will open, but your car will already be the filter, and the site should remember the user's car and allow them to switch between them and choose a new car.
Summarize
Despite the high threshold of entry, the task was not difficult, but routine. In order to write this module we can advise the study of the following approaches, technologies and tools:
- OOP without him, it will be difficult here ...
- Basis, so to say the backbone of the site "MySQL" , "PHP" , "HTML" , "JS" , "CSS" .
- Understanding the principle of the CMS "HostCMS"
- Ability to work with the "API" of other developers
- Search "Sphinx"
- Ability to investigate the loaded sites of the site and optimize the code
- Desirable "jQuery" or "Prototype"
- And naturally, it’s true that the ability to work with some kind of “Integrated Development Environment” is debatable.
And most importantly, a similar result can be achieved without the use of ready-made engines, but, for example, on your own autos-catalog of cars or on the Internet store, developed using frameworks.
PS As a supplement, I cite a couple of scripts from the project that are at least inconclusive, well, you can understand the essence of them.
The script responsible for splitting the process into requests is responsible for adding from the list, as well as informing about the import process:
< script> // knum1,knum2,knum3,knum4,knum5 - // inum - // pz,pr - // lim - // wh1, wh2 - // count - // art_id - //name - //num_fnd - tecDoc var from = 3; var to = 3; var percent = 0; var chk = 0; var cond = ''; function get_list(fromf,tof) { $.post( "/admin/import_excel_new/get_list.php", { from: fromf, to: tof },outputt ); function outputt(data) { if(chk == 0) { from += 1; to += 1; percent = ( from / cond ) * 100; } else { from = cond; percent = ( from / cond ) * 100; } if ((to - cond) > 0) { to = cond; chk = 1; get_list(from,to); to = 0; }else if((to - cond) == 0) { chk = 1; get_list(from,to); to = 0; } document.getElementById("perc").innerHTML=percent.toFixed(2)+"%"; $("#progressbar").children(".ui-progressbar-value").css("width", percent+"%"); document.getElementById("tab_cont").innerHTML+=data; if(chk == 1) { $("#progressbar").css("display", "none"); $("#progresspercent").css("display", "none"); } if ( chk == 0) { get_list(from,to); //setTimeout(get_list, 100, from, to); } } } alert(" , !"); get_list(from,to); function createWindow(count,art_id,inum,knum1,knum2,name,knum3,knum4,knum5,wh1, wh2,pz,pr,num_fnd,lim) { $.post( "/admin/import_excel_new/get_tab.php", { id: art_id, num_fnd: num_fnd },outputt2 ); function outputt2(data) { document.getElementById("inum").innerHTML=inum; document.getElementById("cntnt").innerHTML=data; document.getElementById("btn").innerHTML="<input type=\"button\" name=\"add\" id=\"add_id\" style=\"float:right; margin-top:5px;\" value=\"\" onclick=\"insert_item(\'"+count+"\', \'"+inum+"\',\'"+knum1+"\',\'"+knum2+"\',\'"+name+"\', \'"+knum3+"\',\'"+knum4+"\',\'"+knum5+"\',\'"+wh1+"\', \'"+wh2+"\',\'"+pz+"\',\'"+pr+"\',\'"+num_fnd+"\', \'"+lim+"\')\" />"; } document.getElementById("open_block").style.display="block"; } function loadNum() { bnum = document.getElementById("brand_id").value; $.post( "/admin/import_excel_new/get_number.php", { num: bnum },outputt11 ); function outputt11(data) { document.getElementById("list_num").innerHTML=data; } } function insert_item(count,inum,knum1,knum2,name,knum3,knum4,knum5,wh1,wh2,pz, pr,num_fnd,lim) { if (document.getElementById("opt_id_1").checked === true) art_id=document.getElementById("art_list_id").value; else if (document.getElementById("opt_id_2").checked === true) art_id=document.getElementById("txtnum_id").value; if(document.getElementById("txtnum_id").value!="") { $.post( "/admin/import_excel_new/insert_item.php", { count: count, id: art_id, inum: inum, knum1: knum1, knum2: knum2, name: name, knum3: knum3, knum4: knum4, knum5: knum5, wh1: wh1, wh2: wh2, pz: pz, pr: pr, num_fnd: num_fnd, lim: lim },outputt3 ); function outputt3(data) { document.getElementById("id_"+inum).innerHTML="<span style=\'color:green;\'></span>"; document.getElementById("id_opt_"+inum).innerHTML="<a style=\"cursor:pointer; text-decoration:underline; color:#0090ff;\" onclick=\"createWindow(\'1\',\'"+data+"\', \'"+inum+"\',\'"+knum1+"\',\'"+knum2+"\',\'"+name+"\',\'"+knum3+"\',\'"+knum4+"\',\'"+knum5+"\', \'"+wh1+"\',\'"+wh2+"\',\'"+pz+"\',\'"+pr+"\',\'"+num_fnd+"\',\'"+lim+"\')\"></a>"; } } document.getElementById(\'open_block\').style.display=\'none\'; } </ script> The function of adding goods based on the part number and manufacturer
< ?php // $DataBase - // $knum1,$knum2,$knum3,$knum4,$knum5 - // $inum - // $pz,$pr - // $user - // $date - // $lim - // $wh1, $wh2 - // $nameProizvoditel, $numberProizvod - , function addToCatalogForNoTecdoc($DataBase,$knum1,$knum2,$knum3,$knum4, $knum5,$inum,$pr,$user,$date,$pz,$lim,$wh1,$wh2,$nameProizvoditel,$numberProizvod){ $symbols = array("`"," ", "~", "!", "@", "#", "$", "%", "^", "&", "*", "(", ")", "-", "_", "=", "+", "/", "\\", "|", "?", ":", ";", "\"", "'", "â„–", ",", "."); $numberProizvodVithSymbols = str_replace($symbols, '', $numberProizvod); $nameProizvoditelUpper=strtoupper($nameProizvoditel); $query = " SELECT DISTINCT IF (ART_LOOKUP.ARL_KIND IN (3, 4), BRANDS.BRA_BRAND, SUPPLIERS.SUP_BRAND) AS brand, ART_LOOKUP.ARL_SEARCH_NUMBER AS NUMBER, `ARL_ART_ID` AS item_id, `ARL_SEARCH_NUMBER` AS num_fnd, `DES_TEXTS`.`TEX_TEXT` AS item_txt FROM ART_LOOKUP LEFT JOIN BRANDS ON BRANDS.BRA_ID = ART_LOOKUP.ARL_BRA_ID INNER JOIN ARTICLES ON ARTICLES.ART_ID = ART_LOOKUP.ARL_ART_ID INNER JOIN SUPPLIERS ON SUPPLIERS.SUP_ID = ARTICLES.ART_SUP_ID INNER JOIN DESIGNATIONS ON DESIGNATIONS.DES_ID = ARTICLES.ART_COMPLETE_DES_ID INNER JOIN DES_TEXTS ON DES_TEXTS.TEX_ID = DESIGNATIONS.DES_TEX_ID WHERE ART_LOOKUP.ARL_SEARCH_NUMBER = '".$numberProizvodVithSymbols."' AND ART_LOOKUP.ARL_KIND IN (1, 2, 3, 4) AND DESIGNATIONS.DES_LNG_ID = 16 AND (BRANDS.BRA_BRAND='".$nameProizvoditelUpper."' OR SUPPLIERS.SUP_BRAND='".$nameProizvoditelUpper."') GROUP BY brand, NUMBER ;"; $result1 = $DataBase->query($query); $row1 = mysql_fetch_array($result1); echo '<div class="'.$inum.'" style="display: none;"><b>'; var_dump($query); echo '</b>'; echo '<pre>'; var_dump($row1); echo '</pre></div>'; $art_id=$row1["item_id"]; $txt = $row1['item_txt']; $brand = $row1['brand']; $rez=array(); if($row1){ $rez['status']=true; }else{ $rez['status']=false; } if($rez['status']){ // $DataBase->Insert('shop_items_catalog_table', array( 'shop_currency_id' => '4', 'shop_shops_id' => '3', 'shop_items_catalog_name' => $brand.': '.$txt, 'shop_items_catalog_marking' => $art_id, 'shop_vendorcode' => $knum1, 'shop_extension' => $inum, 'shop_items_catalog_price' => $pr, 'users_id' => $user, 'shop_items_catalog_date_time' => $date, 'shop_items_catalog_putoff_date' => $date, 'shop_items_catalog_weight'=>0 )); $last_id = mysql_insert_id(); // function addToMarking($DataBase,$marking_id, $vendor_id){ if(trim($vendor_id)!="" && $vendor_id!=" "){ $DataBase->Insert('marking', array('marking_id' => $marking_id, 'vendor_id' => $vendor_id)); } } addToMarking($DataBase,$art_id, $numberProizvod); addToMarking($DataBase,$art_id, $knum1); addToMarking($DataBase,$art_id, $knum2); addToMarking($DataBase,$art_id, $knum3); addToMarking($DataBase,$art_id, $knum4); addToMarking($DataBase,$art_id, $knum5); // function addToShop_warehouse_items_table($DataBase,$shop_warehouse_id, $shop_items_catalog_item_id, $shop_warehouse_items_count, $users_id){ if(trim($vendor_id)!="" && $vendor_id!=" "){ $DataBase->Insert('shop_warehouse_items_table', array( 'shop_warehouse_id' => $shop_warehouse_id, 'shop_items_catalog_item_id' => $shop_items_catalog_item_id, 'shop_warehouse_items_count' => $shop_warehouse_items_count, 'users_id' => $users_id )); } } $whResult=0; if(trim($wh1)!=""){ $whResult=$wh1; }else{ $whResult=0; } addToShop_warehouse_items_table($DataBase,4, $last_id, $whResult, $user); if(trim($wh2)!=""){ $whResult=$wh2; }else{ $whResult=0; } addToShop_warehouse_items_table($DataBase,3, $last_id, $whResult, $user); addToShop_warehouse_items_table($DataBase,5, $last_id, 0, $user); function addToShop_prices_to_item_table ($DataBase,$last_id, $prices, $p){ $DataBase->Insert('shop_prices_to_item_table', array('shop_items_catalog_item_id' => $last_id, 'shop_list_of_prices_id' => $prices, 'shop_prices_to_item_value' => $p)); } // addToShop_prices_to_item_table ($DataBase,$last_id, 4, $pz); addToShop_prices_to_item_table ($DataBase,$last_id, 5, $pr); // $DataBase->Insert('shop_properties_items_table', array('shop_items_catalog_item_id' => $last_id, 'shop_list_of_properties_id' => 155, 'shop_properties_items_value' => $lim)); $param = array(); $param['item_id'] = $last_id; $param['path'] = 'item_' . $last_id; $DataBase->Update('shop_items_catalog_table', array('shop_items_catalog_item_id' => $param['item_id'], 'shop_items_catalog_path' => $param['path'] ), " `shop_currency_id` = '4' and `shop_shops_id` = '3' and `shop_items_catalog_name` = '".$brand . ': ' . $txt."' and `shop_items_catalog_marking` = ".$art_id ." and `shop_vendorcode` = '".$knum1."' and `shop_extension` = '".$inum."' and `shop_items_catalog_price` = ".$pr." " ); // $img=getImgForARTid($DataBase,$art_id); if($img) { $DataBase->Insert('shop_properties_items_table', array('shop_items_catalog_item_id' => $last_id, 'shop_list_of_properties_id' => 164, 'shop_properties_items_value' => $img)); } $img=''; } $rez['str_item']=$art_id; $rez['str_num_find']=$row1["num_fnd"]; return $rez; } ?> PS To receive our new articles before others or simply not to miss new publications - subscribe to us on Facebook , VK , Twitter , LiveJournal and LinkedIn
PPS Very soon the course starts in our business school Digitov: I want to become Junior PHP Developer! Subscribe to the course now and you can buy it at a discount.
Article prepared
Vladimir, Middle PHP Developer, SECL Group
Nikita, CEO, SECL Group
Source: https://habr.com/ru/post/195946/
All Articles