I have this table for my users:
user_id name email address score
1 Name 1 email1@yu.com address 1 0
2 Name 2 email2@yu.com address 2 30
3 Name 3 email3@yu.com address 3 0
And table for their products
p_id user_id product start_date
231 1 product b 2016-02-01
232 3 product c 2016-02-01
233 4 product b 2016-02-01
234 1 product b 2016-01-01
235 1 product a 2016-03-01
Now What i want to accomplish here is to get the users(3000+ users) who is active on the past 3 cycles(start date).
Maybe declaring three dates like: $date1 = "2016-01-01" , $date2 = "2016-02-01" and $date3 = "2016-03-01".
What is easiest way to get all user that was active on the past 3 cycle or with the $dates on their start-date. Once I got them all I will insert score on their score field on user table.
Note that start-date can be duplicated. Example: user_id 1 can have Product A,B,C,D etc. and may belong to same or different date. Hope you understand. I need an idea.
PHP - SQL query and condition
Moderator: General Moderators
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: PHP - SQL query and condition
You can JOIN the tables to get user records in a date range with something like this:
You could add a condition to limit results to a date range with:
Or a condition to limit results to specific dates with:
Code: Select all
SELECT users.* FROM users JOIN products ON users.user_id=products.user_id GROUP BY products.user_idCode: Select all
WHERE products.start_date>='2016-01-01' AND products.start_date<'2016-04-01'Code: Select all
WHERE products.start_date='2016-01-01' OR products.start_date='2016-02-01'(#10850)