What Are Custom SQLs and How to Manage VirtueMart Data Using Them?
When you are involved into online business, you must try utmost to maintain your store on efficient level and use advanced techniques for its management. Store Manager for VirtueMart provides extra possibilities for shop administrators who have got SQL skills. More precisely, there is the possibility to use custom SQLs to lighten and enhance data management.
What Is Custom SQL?
Custom SQL is an instrument that allows to execute SQL commands and manage VirtueMart database. They are executed according to logic, determined by the user. The scope of SQL includes data insert, query, update, ets. Custom SQL is developed by user and applied for his/her shop only, in other words, it is adapted to certain web shop.
For example, you want product status to be changed if less than 3 items of this or that product is left in warehouse. Another example, you need to increase quantity for products produced by Sony company. There might occur different situations that can be resolved with the help of custom SQLs.
Although, there exist other tools used for massive updates that accelerate product management, for example Multi Editor allows to manage price, status, quantity and other fields for abundance of products at once. You can also apply expressions (set of SQL statements) in Multi Editor or in the process of VirtueMart import to get necessary value and update numerous products.
Of course, it is highly convenient and useful, but sometimes it is not sufficient. At times you may have specific requirements to updates, manage products or customers basing on some conditions or perform after import tasks. SQL will come to assistance.
How to Launch SQL?
Custom SQL option in Store Manager is available in product context menu and in Import Actions window of product import wizard.
- Context menu
- Import Wizard
When you select some merchandise and call product (customer or shopper) context menu, you will find Custom SQLs option below in the list. Put it into focus to derive available SQLs.
VirtueMart import settings are being designated within several steps of import wizard. In Import Options window you will find After Import Actions field, where all available SQLs are listed. If you want this or that action to be performed, check the box for it. Directly from this window you can create new SQL if needed.
How to Create Custom SQL?
As it has been mentioned above, in order to create SQL commands, technical skills are required. Learn structure of VirtueMart database tables before you start working with custom SQL. Database tables are available in Tools -> Raw Table Editor
Afterwards open Tools -> Custom SQL where custom SQLs are managed. This section consists of two parts - in the column to the left you can find already existing SQLs. When you select any from the list, you will get SQL itself displayed to the right.
Above on the toolbar you can find wide array of operations possible over SQLs, namely, there is the possibility to filter commands, execute selected one, call SQL help window, save SQLs to the file and so on.
Looking over options, available on the toolbar, you will find Insert Macro option. What does it stand for?
When you expand the drop-down you will find two selections:
- /*PREFIX*/ - used when you are not sure about VirtueMart database table prefix. Usually if you have several stores and, correspondingly, more than one database, they can be distinguished by prefixes. For example - x0xj3_virtuemart_products, where x0xj3_ is prefix. If you want to execute SQL for another shop, you do not need to change prefix since it is being automatically replaced depending on store connection you have selected.
- /*LANG_SUFFIX*/ - this is language suffix, which works similarly. As for example, language suffix is present in the following record - x0xj3_virtuemart_categories_en_gb
Below in this article you can find SQL samples and cases when you might need to apply them
Suppose, you want to automatically add to product name “IS AVAILABLE” text, if more than 10 items of these products are available in warehouse.
- Firstly you must create new custom SQL and give it a name.
- Afterwards you must assign it to corresponding entities. Since this SQL concerns VirtueMart products, select Linked to Products option from the drop-down available on the toolbar.
IF and WHERE statements will be used in this SQL and it will look as following:
SELECT p.virtuemart_product_id AS `ID`, IF(p.`product_in_stock` > 10, CONCAT(pl.product_name, ' IS AVAILABLE'), pl.product_name) AS `Name`, p.product_sku AS `SKU`, p.product_in_stock AS `In stock`, cl.category_name AS `Category`, IF(pp.override = 1, pp. `product_override_price`, pp. `product_price`) AS `Price` FROM /*PREFIX*/virtuemart_products p LEFT JOIN /*PREFIX*/virtuemart_products/*LANG_SUFFIX*/ pl ON pl.virtuemart_product_id = p.virtuemart_product_id LEFT JOIN /*PREFIX*/virtuemart_product_categories c ON c.virtuemart_product_id = p.virtuemart_product_id LEFT JOIN /*PREFIX*/virtuemart_categories/*LANG_SUFFIX*/ cl ON cl. `virtuemart_category_id` = c.virtuemart_category_id LEFT JOIN /*PREFIX*/virtuemart_product_prices pp ON pp.virtuemart_product_id = p.virtuemart_product_id WHERE p.`virtuemart_product_id` IN(&`prod.SelProd`) GROUP BY p.virtuemart_product_id ORDER BY cl.category_name
&`prod.SelProd` means that this SQL can be applied to selected products only, as it is shown on the screenshot
Let’s complicate the task and in addition to above described SQL we will add one more condition - product manufacturer. Thus, our requirement is to add “IS AVAILABLE” text to merchandise, if more than 10 items of these merchandise are available and they are produced by definite manufacturer. As a result, we will get the following SQL
SELECT p.virtuemart_product_id AS `ID`, IF(p.`product_in_stock` > 10, CONCAT(pl.product_name, ' IS AVAILABLE'), pl.product_name) AS `Name`, p.product_sku AS `SKU`, p.product_in_stock AS `In stock`, cl.category_name AS `Category`, IF(pp.override = 1, pp. `product_override_price`, pp. `product_price`) AS `Price` FROM /*PREFIX*/virtuemart_products p LEFT JOIN /*PREFIX*/virtuemart_products/*LANG_SUFFIX*/ pl ON pl.virtuemart_product_id = p.virtuemart_product_id LEFT JOIN /*PREFIX*/virtuemart_product_categories c ON c.virtuemart_product_id = p.virtuemart_product_id LEFT JOIN /*PREFIX*/virtuemart_categories/*LANG_SUFFIX*/ cl ON cl. `virtuemart_category_id` = c.virtuemart_category_id LEFT JOIN /*PREFIX*/virtuemart_product_prices pp ON pp.virtuemart_product_id = p.virtuemart_product_id LEFT JOIN /*PREFIX*/virtuemart_product_manufacturers pm ON pp.`virtuemart_product_id` = pm.`virtuemart_product_id` WHERE p.`virtuemart_product_id` IN(&`prod.SelProd`) and pm.`virtuemart_manufacturer_id` = :`manf.Only for Manufacturer` GROUP BY p.virtuemart_product_id ORDER BY cl.category_name
Custom SQL Fulfillment
Let’s apply SQL command to see how it works. We will take Sample 2 custom SQL After SQL is created, select it from the list, provided to the left and press Execute SQL button on the toolbar or press F9. There will come Custom SQL Parameters window, where you have to select target manufacturer and indicate what products will be processed in the course of SQL carrying out.
Custom SQL provides more flexible way to manage VirtueMart database. This functionality is highly convenient and enhances data handling. When creating custom SQLs store owners can indicate specific parameters and link commands to necessary entities (products, orders, customers) or to product import.
Use trial version of Store Manager for VirtueMart and explore fully-functional program free for 30 days virtuemart-manager.com/free-download