Run an SQL Query

[ayz1]

In Admin > Products I want to run an SQL query to update the CubeCart_pricing_group table when the Save button is pressed when editing an exisiting product or adding a new product. Any idea how I would do that?

[bsmither]

There are two hooks available, one before any form POSTed data is processed by CubeCart, and another after it all has been processed and databased.

So, I think we need to determine at what point would be easiest: massage the form's POSTed data and let CubeCart database that changed data, or actually update the database table.

[ayz1]

I need to run the queries to update the table after the database has been updated. 

[bsmither]

We will create a Code Snippet.

Backup the affected tables.

In admin, Manage Hooks, Code Snippets tab, click Add Snippet.

Enabled: Checked
Unique ID: grp_update@ayz1    -- 32 chars max
Execution Order: 99
Description: Makes a few updates to CubeCart_pricing_group after databasing Add/Edit product
Trigger: admin.product.save.post_process
Version: 1.0
Author: AYZ1
PHP Code:
<?php
/**
 * The following will update the Group Prices according to the following rules:
 * Determine the factors that will influence the outcome
 * Determine the formulas to derive the outcome
 * Create the $ayz1_record that will update the table with the new data
 * Create the $ayz1_where filter to positively identify which records to update
 * $GLOBALS['db']->update('CubeCart_pricing_group',$ayz1_record,$ayz1_where);
 */
// Put your code here
?>

Save.

Test.

[ayz1]

Thanks for that. I have set it up but getting a blank screen. I think the queries are being run BUT it appears that each time a product is updated if Groups are involved the product group table is updated and the group product is given a new ID which I find a bit strange. Also this appears to be happening after the queries in the hook have been run thereby overriding the changes I made with the queries. I've checked this on two back up stores and both do the same.

 

[ayz1]

Every time a product is edited the price_id in the pricing_group table is regenerated. So if you edit a product with original price_id of 1329 and you have say 4378 products when the edit to 1329 is saved the price_id will change to 4379. I am wondering if this is correct or is it a bug?

[ayz1]

Not a bug apparently so mystery solved on that one.

[bsmither]

I posted a question in the Github asking the developers to research why the code does this: Delete all Group Prices for this product, then work through the POSTed array of group prices (gets POSTed every time) inserting the values. There is code to update any changes but because there was a unilateral deletion first, there is nothing left to update. So, all the inserted rows gets new primary key numbers - not that getting new numbers affects anything.