I have two tables:
Table 1:
store_products
----------------
ID | name | sold
----------------
ID => INT
name => STRING
sold => INT
Table 2
store_product_views
---------------------
productID | timestamp
---------------------
productID => INT
timestamp => INT
The relationship is set up as one-to-many where there are multiple store_product_views for one store_product.
The code is set up to log into store_product_views a productID and timestamp whenever an item from store_products is viewed.
I want to generate data that gets the product ID, the product name, the number of sold products and the total views of that product within a range of timestamp. This works correctly if I leave out the AND views.timestamp >... part.
Here is the query:
SELECT store_products.ID as id, name, sold, COUNT(views.productID) AS views
FROM store_products
JOIN store_products_views AS views ON store_products.ID = views.productID
AND views.timestamp > '".$options->start."' AND views.timestamp < '".$options->end."'
GROUP BY store_products.ID
$options->start and $options->end contain a timestamp that I want to filter for the range. The problem is I only get data when timestamp is it its max value in the table. If I set the $options->end value to get a subset of the data, I get nothing at all.
