CODEWITHSUNDEEP
phpmysqlphpmyadmin
Rinzler
Rinzler

Reputation: 2116

Select rows from table in Php/Mysql based on time or two dates?

hi i have players table in mysql .

i need to select inactive players who have not played a match for 6 months in the league .

for example ->

select * from players where player (matchplayed == true && (in last 6 months condition));

i am newbie in mysql

so will like some help how to deal with time .

Upvotes: 1

Views: 1009

Answers (3)

talha2k
talha2k

Reputation: 25650

some thing like this may work:

SELECT * from players where player_matchplayed == true
 && lastmatch_played_date > DATE_SUB(curdate(),INTERVAL 6 MONTH);

DATE_SUB(curdate(),INTERVAL 6 MONTH) will give you the interval of last six months from current date. Which you can use to check the lastmatch_playeddate is greater than it or not.

Hope this helps .

Upvotes: 5

dan-lee
dan-lee

Reputation: 14492

Try it with this query

SELECT * FROM players
WHERE matchplayed IS NOT NULL
AND last_match < NOW() - INTERVAL 6 MONTH

last_match is a mysql DATE field with the last match information
matchplayed is either NULL (not played) or another value for already played

Upvotes: 3

Ander2
Ander2

Reputation: 5658

Have a look to mysql date / time functions: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

I think that DATE_SUB will do the job or a DATE_DIFF for comparison.

Upvotes: 2

Related Questions