[RESOLVED] Extract results from next/previous month

TobyRT

Hi,

I've got a table of members. I want to be able to produce a list of:

a) Members who have been added/modified in the last 30 days
b) Members who are about to expire in the next 30 days (I mean their membership, not their lives!)

I know it's got something to do with Curdate() and INTERVAL 30 days, but I can't seem to make it work.

Here's my code:

$result = $connector->query('SELECT id, firstname, surname, memnum, email, UNIX_TIMESTAMP(expdate) AS expdate FROM members WHERE expdate >= DATE_ADD(CURDATE(), INTERVAL 30 DAY) ORDER BY joindate DESC')or die ("Error in Query: $query.".mysql_error());

I'm assuming the > operator means dates from now and < means before now, but I keep getting incorrect results. I've got 2 entries in the test db, one with an expiry at the beginning of the month, one with an expiry at the end of the month. I either get both showing or neither.

Could someone point me in the right direction please? I couldn't get much sense out of the SQL manual!

_RL_Ian

You want the DATEDIFF() function. Just compare today's date with their membership start and see if it's > 30. Same logic with the expiration. You have to be running MySQL 4.1+ to do this. If you have 4.0, you can use the TO_DAYS() function, but it takes a little more work.

TobyRT

This is the query I'm using, but it says the syntax is invalid - any suggestions?

$result = $connector->query('SELECT id, firstname, surname, memnum, email, UNIX_TIMESTAMP(expdate) AS expdate FROM members WHERE DATEDIFF(CURDATE(), joindate) >="30" ORDER BY joindate DESC')or die ("Error in Query: $query.".mysql_error());

TobyRT

OK, I've changed a couple of things:

Member 1 now has a join date of 1st Oct 2005
Member 2 now has a join date of 30th Oct 2005

Today's date is 14th October 2005, so if I run the query to find members who have joined in the last 30 days, it should only give me details for Member 1.

Here's my code:

$result = $connector->query('SELECT id, firstname, surname, memnum, email, UNIX_TIMESTAMP(joindate) AS joindate FROM members WHERE joindate > DATE_SUB(NOW(), INTERVAL 30 DAY) ORDER BY joindate DESC')or die ("Error in Query: $query.".mysql_error());

I took the DATE_SUB from my PHP/SQL Manual which I have at work - it uses that function to extract info from the previous year.

Why is it that my query is returning no results?

Please help - I'm going potty here. On my 5th coffee of the morning and that's not helping either!

TobyRT

OK, here's my revised query:

$result = $connector->query('SELECT id, firstname, surname, memnum, email, UNIX_TIMESTAMP(expdate) AS expdate FROM members WHERE DATE_ADD(expdate, INTERVAL 30 day) >= DATE_FORMAT(NOW(),"%Y-%m-%d") ORDER BY expdate DESC')or die ("Error in Query: $query.".mysql_error());

It's filtering out results from before today, but it's still returning results from more than 30 days in the future.

How can I stop it please?!

TobyRT

SUSSED IT!! Might not be very clean, but it works!

$result = $connector->query('SELECT id, firstname, surname, memnum, email, UNIX_TIMESTAMP(expdate) AS expdate FROM members WHERE DATE_ADD(expdate, INTERVAL 30 day) >= DATE_FORMAT(NOW(),"%Y-%m-%d") AND expdate < DATE_ADD(DATE_FORMAT(NOW(),"%Y-%m-%d"), INTERVAL 30 DAY) ORDER BY expdate ASC')or die ("Error in Query: $query.".mysql_error());