PHP - SQL query and condition

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
xkevin
Forum Newbie
Posts: 14
Joined: Thu Jun 09, 2016 9:01 pm

PHP - SQL query and condition

Post by xkevin »

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.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: PHP - SQL query and condition

Post by Christopher »

You can JOIN the tables to get user records in a date range with something like this:

Code: Select all

SELECT users.* FROM users JOIN products ON users.user_id=products.user_id GROUP BY products.user_id
You could add a condition to limit results to a date range with:

Code: Select all

WHERE products.start_date>='2016-01-01' AND products.start_date<'2016-04-01'
Or a condition to limit results to specific dates with:

Code: Select all

WHERE products.start_date='2016-01-01' OR products.start_date='2016-02-01'
(#10850)
Post Reply