Best Practice for Keeping 'Top Products' Counter Current

General Web Dev
1

Hi! I developed a product comparison engine in which a database field keeps count of the number of times each product was compared. I can then sort the products by this number to show users the “top products.” The problem, however, is that this number is cumulative and does not reset, so it’s showing the top products of all time, rather than the top products of, say, the past week. I suppose I could run a cron job every week to clear the counters with a mySQL update query, but I’m not sure this is the best solution. Is there a best practice as far as how to determine what the current trend of top items are?

Thanks!

2

One of some methods to do it, you can create a table to keep the date or time when the product is compared. Using this method you can have a flexibility to query how many times it was compared for any period, whether weekly, bi-weekly, monthly etc. The other way, if you have specific period (eg. weekly), you can make the table to keep the counter by week. The latter method would have less records, so it’s faster to query too, but less flexible as you can’t query top products in the last 2 days for example. You can also have a summarized table, for example to keep current year counts, either by week or by month, to make the query even faster if you have really lots of products and if you think it’s necessary as you often show top products of current year, monthly on this year etc.

3

I like that approach.

Instead of
prodA_id, prodB_id comp_count
where comp_count gets incremented each time.
prodA_id prodB_id timestamp

Of course instead of one row like
prodA_id = 4353 prodB_id = 6758 comp_count = 57
there would be 57 rows

The database could grow quite large quite quickly if there are a lot of products and the app is busy.
But the statistics that could be got would be less general and much more informative and useful.

4

Yes, it does sound resource intensive, but it also sounds pretty useful and extensible. Thanks!

5

If I want a separate row for each product, and sometimes I’m adding timestamps for multiple products at a time, is it super inefficient and resource intensive to do this? It seems like there must be a better way:

foreach($products as $product) { if(in_array($product['shortname'], $products_selected)) { $SQL_insert = mysqli_query($connection, "INSERT INTO products_stats SET pid = '".$product['pid']."', product_shortname = '".$product['shortname']."', comparison_new_datetime = NOW(), comparison_products_count = ".count($products_selected)); } }

6

Nevermind. Got it! :slight_smile:

// Update stats for product $SQL_insert_query = 'INSERT INTO products_stats (pid, product_shortname, comparison_new_datetime, comparison_new_products_count) VALUES '; $products_to_insert_count = 0; foreach($products as $product) { if(in_array($product['shortname'], $products_selected)) { $products_to_insert_count++; if($products_to_insert_count >= 2) { $SQL_insert_query .= ', '; } $SQL_insert_query .= '('.$product['pid'].", '".$product['shortname']."', NOW(), ".count($products_selected).')'; } } if($products_selected > 0) { $SQL_insert = mysqli_query($connection, $SQL_insert_query) or die(mysqli_error($connection)); }

1 Like
7

Is it possible to modify the query to set set cid = X, where X is a unique identifier so that I can count the number of unique comparisons that are run? The rouble is that there will be Y rows for each comparison, where Y is the count of product in the comparison. So, I can’t simply use an auto-increment.

To rephrase, I’d like all of rows inserted with this query to have the same ID. And then the next time the query runs, I’d like all of THOSE rows to also have the same ID. How is this accomplished?

8

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.