PHP: keep track of requests by user and by product brand?

Question

I have a MySQL table that has price requests in it, it has date, first, last, and product_id fields. The product brand can be found from the product table from the product_id.

Given a date range, they want to know the total number of requests, by people and by brand for each day in the date range, and total for date range. Here is the tricky part, if the same person makes a request more than once in a day, then it only counts as 1 for people. But if a person makes a request for 2 different brands in 1 day, each brand gets 1 for that day. but if they make a requests for mulitples of a brand in a single day that brand only gets counted 1 for that day.

For example lets say on a given date John Doe made 3 price requests, for a Burberry product and 2 swarovski products. That would only count 1 for people, 1 for burberry, and 1 for swarovski. But if another person made a request for burberry then there would be 2 burberry for that day and 2 people for that day.

I hope this makes since. Anyways what is the best way to do this? I am using PHP4 and MySQL4

Thanks!

Answer 1

Note: I made this table, since you didn't provide your SQL DDL for your tables. Just give me some comments and I'll edit the answer until we get it right.

CREATE TABLE priceRequestsProducts AS (
    userId INT NOT NULL,
    productId INT NOT NULL,
    date DATE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX(userId),
    INDEX(productId),
    PRIMARY KEY (userId, productId, date),
    CONSTRAINT fk_prp_users FOREIGN KEY (userId) REFERENCES (Users.id),
    CONSTRAINT fk_prp_products FOREIGN KEY (productId) REFERENCES (Products.id)
) ENGINE=InnoDb;

-- # Shows all dates each user has made a priceRequest for at least one product:
SELECT U.userId, U.firstName, U.lastName, U.username, date
FROM Users U
JOIN priceRequestsProducts as prp ON u.id = prp.id
GROUP BY prp.userId, prp.date;

-- # Shows number of price requests for a product on all dates
SELECT P.id, P.name, count(1) as numRequests, prp.date
FROM Products P
JOIN priceRequestsProducts prp ON prp.productId = P.id
GROUP BY prp.productId, prp.date;

Answer 2

Assuming the table definition of Machine, try

-- # Shows number of people doing a request per day
SELECT
  Count(DISTINCT userId) AS NumberOfUsers,
FROM
  priceRequestsProducts prp,
  date
GROUP BY
  prp.date;

-- # Shows number of request for a given product per day
SELECT
  Count(DISTINCT userId) AS NumberofUsers,
  productId,
  date
FROM
  priceRequestsProducts prp
GROUP BY
  prp.date, prp.productId;

It is not desirable to use on query. For people, you would expect one row per day, for products you would expect one row per pair (product, day). If you really want to do this, introduce a value not used as a productId (I would make productId UNSIGNED INT NOT NULL AUTO_INCREMENT) or use NULL, and use it to designate users:

SELECT
  Count(DISTINCT userId) AS NumberOfUsers,
FROM
  priceRequestsProducts prp,
  NULL,
  date
GROUP BY
  prp.date;
UNION ALL
SELECT
  Count(DISTINCT userId) AS NumberofUsers,
  productId,
  date
FROM
  priceRequestsProducts prp
GROUP BY
  prp.date, prp.productId;