[Skip to main content](#vessel-content) # Improvement saving/updating product prices ## - **[jameswadsworth](/forum/user/556-jameswadsworth "View jameswadsworth's Profile")** - [![jameswadsworth's Avatar](https://www.hikashop.com/media/kunena/core/svg/person.svg)](/forum/user/556-jameswadsworth "View jameswadsworth's Profile") Topic Author - [ Offline](/forum/user/556-jameswadsworth) Less More - Posts: 274 - Thank you received: 5 - - - - [](http://www.deanimaconsulting.com) - 15 years 9 months ago - 15 years 9 months ago [\#5145](#5145) by [jameswadsworth](/forum/user/556-jameswadsworth "View jameswadsworth's Profile") *[Improvement saving/updating product prices](/forum/2-general-talk-about-hikashop/5145-improvement-saving-updating-product-prices#5145 "Hello everyone, We have been testing hikashop and we love it. However we believe the saving of product prices can be improved. At the moment if a product is applied/saved, regardless if the prices have been changed or not, all the prices are deleted from the database then re-inserted. It works, but some products might have up to 10 steps in their prices, so this means 10 prices rows must be deleted from the database and then other 10 prices rows inserted again. And this happens every time a product is applied/saved. We have been testing with a few hundred products and already the ID number for prices is going stratospheric. We propose that besides 'deleted' and 'insert' we add an 'update' call using CASE, WHEN, THEN operators in mysql. The 'delete' and 'insert' are only called if there really is something to delete or insert. The 'update' function means the database compares the passed information with currently stored information and only makes a change if there is difference and we reduce the number of calls being made the database significantly. How do we get it work? First we had to make a small change to the price.php file in /adminstrator/components/com_hikashop/views/product/tmpl/ We want to pass the $row->price_id variable as hidden value so it becomes: We then rewrote the updatePrices function in the product.php file in /adminstrator/components/com_hikashop/classes/ We welcome your comments and if you think it is useful feel free to use it. James")* was created by *[jameswadsworth](/forum/user/556-jameswadsworth "View jameswadsworth's Profile")* Hello everyone, We have been testing hikashop and we love it. However we believe the saving of product prices can be improved. At the moment if a product is applied/saved, regardless if the prices have been changed or not, all the prices are deleted from the database then re-inserted. It works, but some products might have up to 10 steps in their prices, so this means 10 prices rows must be deleted from the database and then other 10 prices rows inserted again. And this happens every time a product is applied/saved. We have been testing with a few hundred products and already the ID number for prices is going stratospheric. We propose that besides 'deleted' and 'insert' we add an 'update' call using CASE, WHEN, THEN operators in mysql. The 'delete' and 'insert' are only called if there really is something to delete or insert. The 'update' function means the database compares the passed information with currently stored information and only makes a change if there is difference and we reduce the number of calls being made the database significantly. How do we get it work? First we had to make a small change to the price.php file in /adminstrator/components/com_hikashop/views/product/tmpl/ We want to pass the $row->price_id variable as hidden value so it becomes: Code: <td> <?php echo (!empty($row->price_id) ? $row->price_id : '--' ); ?><input type="hidden" id="price[price_id][<?php echo $i;?>]" name="price[price_id][<?php echo $i;?>]" value="<?php echo @$row->price_id; ?>" /> </td> We then rewrote the updatePrices function in the product.php file in /adminstrator/components/com_hikashop/classes/ Code: function updatePrices($element,$status){ if(count($element->prices)){ $delete_where = array(); $update_price_currency_id = array(); $update_price_value = array(); $update_price_min_quanity = array(); $update_where = array(); $insert = array(); foreach($element->prices as $price){ if(empty($price->price_value)){ $delete_where[] = (int)$price->price_id; } elseif($price->price_id){ $update_price_currency_id[] = sprintf("WHEN %d THEN %d ", $price->price_id, (int)$price->price_currency_id); $update_price_value[] = sprintf("WHEN %d THEN '%s' ", $price->price_id, (float)$price->price_value); $update_price_min_quanity[] = sprintf("WHEN %d THEN %d ", $price->price_id, (int)$price->price_min_quantity); $update_where[] = (int)$price->price_id; } else { $insert[]='('.(int)$price->price_currency_id.','.$status.','.(int)$price->price_min_quantity.','.(float)$price->price_value.')'; } } if(!empty($update_where)){ $query = 'UPDATE IGNORE '.hikashop::table('price').' SET price_currency_id = CASE price_id '.implode(' ',$update_price_currency_id).' END, price_value = CASE price_id '.implode(' ',$update_price_value).' END, price_min_quantity = CASE price_id '.implode(' ',$update_price_min_quanity).' END WHERE price_product_id='.$status.' AND price_id IN ('.implode(',',$update_where).');'; $this->database->setQuery($query); $this->database->query(); } if(!empty($insert)){ $query = 'INSERT IGNORE INTO '.hikashop::table('price').' (price_currency_id,price_product_id,price_min_quantity,price_value) VALUES '.implode(',',$insert).';'; $this->database->setQuery($query); $this->database->query(); } if(!empty($delete_where)){ $query = 'DELETE FROM '.hikashop::table('price').' WHERE price_product_id='.$status.' AND price_id IN ('.implode(',',$delete_where).');'; $this->database->setQuery($query); $this->database->query(); } } } We welcome your comments and if you think it is useful feel free to use it. James Last edit: 15 years 9 months ago by [jameswadsworth](/forum/user/556-jameswadsworth "View jameswadsworth's Profile"). Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzItZ2VuZXJhbC10YWxrLWFib3V0LWhpa2FzaG9wLzUxNDUtaW1wcm92ZW1lbnQtc2F2aW5nLXVwZGF0aW5nLXByb2R1Y3QtcHJpY2VzP3RtcGw9bWFya2Rvd24=) or [Create an account](/register) to join the conversation. - **[nicolas](/forum/user/63-nicolas "View nicolas's Profile")** - [![nicolas's Avatar](https://www.hikashop.com/media/kunena/avatars/resized/size144/users/avatar63.png?1793428657)](/forum/user/63-nicolas "View nicolas's Profile") - [ Away](/forum/user/63-nicolas) Less More - Posts: 86322 - Thank you received: 14280 - - 15 years 9 months ago - 15 years 9 months ago [\#5155](#5155) by [nicolas](/forum/user/63-nicolas "View nicolas's Profile") Replied by *[nicolas](/forum/user/63-nicolas "View nicolas's Profile")* on topic *[Re: Improvement saving/updating product prices](/forum/2-general-talk-about-hikashop/5145-improvement-saving-updating-product-prices#5155 "Hello everyone, We have been testing hikashop and we love it. However we believe the saving of product prices can be improved. At the moment if a product is applied/saved, regardless if the prices have been changed or not, all the prices are deleted from the database then re-inserted. It works, but some products might have up to 10 steps in their prices, so this means 10 prices rows must be deleted from the database and then other 10 prices rows inserted again. And this happens every time a product is applied/saved. We have been testing with a few hundred products and already the ID number for prices is going stratospheric. We propose that besides 'deleted' and 'insert' we add an 'update' call using CASE, WHEN, THEN operators in mysql. The 'delete' and 'insert' are only called if there really is something to delete or insert. The 'update' function means the database compares the passed information with currently stored information and only makes a change if there is difference and we reduce the number of calls being made the database significantly. How do we get it work? First we had to make a small change to the price.php file in /adminstrator/components/com_hikashop/views/product/tmpl/ We want to pass the $row->price_id variable as hidden value so it becomes: We then rewrote the updatePrices function in the product.php file in /adminstrator/components/com_hikashop/classes/ We welcome your comments and if you think it is useful feel free to use it. James")* Thank you for your feedback. We will implement that function in next version along with the modification to the view for the id : Code: function updatePrices($element,$status){ $filters=array('price_product_id='.$status); if(count($element->prices)){ $ids = array(); foreach($element->prices as $price){ if(!empty($price->price_id)) $ids = $price->price_id; } if(!empty($ids)){ $filters[]= 'price_id NOT IN ('.implode(',',$ids).')'; } } $query = 'DELETE FROM '.hikashop::table('price').' WHERE '.implode(' AND ',$filters); $this->database->setQuery($query); $this->database->query(); if(count($element->prices)){ $insert = array(); foreach($element->prices as $price){ if(empty($price->price_value)) continue; if(empty($price->price_id)) $price->price_id = 'NULL'; $line = '('.(int)$price->price_currency_id.','.$status.','.(int)$price->price_min_quantity.','.(float)$price->price_value.','.$price->price_id; if(hikashop::level(2)){ if(empty($price->price_access)){ $price->price_access = 'all'; } $line.=','.$this->database->Quote($price->price_access); } $insert[]=$line.')'; } if(!empty($insert)){ $select = 'price_currency_id,price_product_id,price_min_quantity,price_value,price_id'; if(hikashop::level(2)){ $select.=',price_access'; } $query = 'REPLACE '.hikashop::table('price').' ('.$select.') VALUES '.implode(',',$insert).';'; $this->database->setQuery($query); $this->database->query(); } } } Last edit: 15 years 9 months ago by [nicolas](/forum/user/63-nicolas "View nicolas's Profile"). Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzItZ2VuZXJhbC10YWxrLWFib3V0LWhpa2FzaG9wLzUxNDUtaW1wcm92ZW1lbnQtc2F2aW5nLXVwZGF0aW5nLXByb2R1Y3QtcHJpY2VzP3RtcGw9bWFya2Rvd24=) or [Create an account](/register) to join the conversation. - **[jameswadsworth](/forum/user/556-jameswadsworth "View jameswadsworth's Profile")** - [![jameswadsworth's Avatar](https://www.hikashop.com/media/kunena/core/svg/person.svg)](/forum/user/556-jameswadsworth "View jameswadsworth's Profile") Topic Author - [ Offline](/forum/user/556-jameswadsworth) Less More - Posts: 274 - Thank you received: 5 - - - - [](http://www.deanimaconsulting.com) - 15 years 9 months ago - 15 years 9 months ago [\#5164](#5164) by [jameswadsworth](/forum/user/556-jameswadsworth "View jameswadsworth's Profile") Replied by *[jameswadsworth](/forum/user/556-jameswadsworth "View jameswadsworth's Profile")* on topic *[Re: Improvement saving/updating product prices](/forum/2-general-talk-about-hikashop/5145-improvement-saving-updating-product-prices#5164 "Hello everyone, We have been testing hikashop and we love it. However we believe the saving of product prices can be improved. At the moment if a product is applied/saved, regardless if the prices have been changed or not, all the prices are deleted from the database then re-inserted. It works, but some products might have up to 10 steps in their prices, so this means 10 prices rows must be deleted from the database and then other 10 prices rows inserted again. And this happens every time a product is applied/saved. We have been testing with a few hundred products and already the ID number for prices is going stratospheric. We propose that besides 'deleted' and 'insert' we add an 'update' call using CASE, WHEN, THEN operators in mysql. The 'delete' and 'insert' are only called if there really is something to delete or insert. The 'update' function means the database compares the passed information with currently stored information and only makes a change if there is difference and we reduce the number of calls being made the database significantly. How do we get it work? First we had to make a small change to the price.php file in /adminstrator/components/com_hikashop/views/product/tmpl/ We want to pass the $row->price_id variable as hidden value so it becomes: We then rewrote the updatePrices function in the product.php file in /adminstrator/components/com_hikashop/classes/ We welcome your comments and if you think it is useful feel free to use it. James")* Thanks for the consideration. I had trouble posting the code correctly for the price.php file. I have attached the modified version of this file. #### *This attachment is hidden for guests. Please log in or register to see it.* Last edit: 15 years 9 months ago by [jameswadsworth](/forum/user/556-jameswadsworth "View jameswadsworth's Profile"). Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzItZ2VuZXJhbC10YWxrLWFib3V0LWhpa2FzaG9wLzUxNDUtaW1wcm92ZW1lbnQtc2F2aW5nLXVwZGF0aW5nLXByb2R1Y3QtcHJpY2VzP3RtcGw9bWFya2Rvd24=) or [Create an account](/register) to join the conversation. Board Categories HikaShop Install & Update Product & Category Display Content Creation Sales and Orders Orders management Reports & Statistics Customers Checkout Payment Methods Shipping Methods Import & Export (CSV, XLS…) Specific Features Affilation Taxes Wishlist Vote Carousel & Slider Mass Actions Filters & Search Discount & Coupon Feature Request Integrations Showcase General Talk about HikaShop Development HikaShop 3.0 Beta Translation Commercial Jobs HikaShop Components HikaMarket Market: Feature requests Market: How to? Market: Bug Report HikaSerial General talk about HikaSerial Serial: Bug Report Serial: How to? Support en Français de HikaSerial (French Support) HikaSubscription HikaAuction Auction: Feature requests Auction: How to? Auction: Bug report Hikashop around the world Support en Français (French Support) Russian FAQ. Курс выживания с Hikashop. Частые вопросы и о Расширения Hikashop Наши сайты на Hikashop 日本語版サポート(Japanese Support) Thai German Archives Bug Report How to? [](/forum/topics/feed/mode-topics?format=feed&type=rss) Time to create page: 0.074 seconds [Powered by](/forum/credits) [Kunena Forum](https://www.kunena.org)