I have an external database connected, which has price histories for all of my products by sku (called identifier). I use the following MySQL query to construct a table and average the prices of multiple products on a given day. This is so I can make a chart that shows average prices over time for all products.
The issue is I would like to be able to include ONLY products that the current user has saved in a data table called kdn_user_my_collection. kdn is my database prefix. I can't combine the queries because they are on separate servers. I have tried to use placeholder variables with a shortcode that generates a list of all the SKUs in the current user's collection, but it seems like a variable cannot recognize an array, so I can do sku 1 but can't do all 3 skus in one variable. I've tried lots of different ways - any ideas here? Thanks in advance!
Just wanted to follow up on this for help. The simplest solution to me seems like there must be a way to populate a list as a variable, but I can't seem to do it. For example, if I want to generate this:
WHERE identifier IN ('sku 1', 'sku 2', 'sku 3')
I can't seem to do it with this:
WHERE identifier IN ('%VAR1%')
On my end I've tried tweaking the output formatting that goes into the variable, like removing the " ' " before the first and after the last list item, but I still cannot use a list in place of a variable. Any ideas? Thank you.
I have an external database connected, which has price histories for all of my products by sku (called identifier). I use the following MySQL query to construct a table and average the prices of multiple products on a given day. This is so I can make a chart that shows average prices over time for all products.
The issue is I would like to be able to include ONLY products that the current user has saved in a data table called kdn_user_my_collection. kdn is my database prefix. I can't combine the queries because they are on separate servers. I have tried to use placeholder variables with a shortcode that generates a list of all the SKUs in the current user's collection, but it seems like a variable cannot recognize an array, so I can do sku 1 but can't do all 3 skus in one variable. I've tried lots of different ways - any ideas here? Thanks in advance!
SELECT
date,
AVG(rolling_avg_of_median_price)
FROM metrics.wcc_wkl_prices_and_stats_mixed
WHERE identifier IN ('sku 1', 'sku 2', 'sku 3')
GROUP BY date
Just wanted to follow up on this for help. The simplest solution to me seems like there must be a way to populate a list as a variable, but I can't seem to do it. For example, if I want to generate this:
WHERE identifier IN ('sku 1', 'sku 2', 'sku 3')
I can't seem to do it with this:
WHERE identifier IN ('%VAR1%')
On my end I've tried tweaking the output formatting that goes into the variable, like removing the " ' " before the first and after the last list item, but I still cannot use a list in place of a variable. Any ideas? Thank you.