Trying to query records less than six months old but older than 30 days.

Codewalkers Forums Sponsor:

You eat, breathe and sleep innovation. Build your mobile intelligence with BlackBerry® experts this July. Register Today!

April 22nd, 2008, 01:53 PM

matthewst

Contributing User

Join Date: Apr 2007

Posts: 136

Time spent in forums: 1 Day 22 h 12 m 32 sec
Reputation Power: 2

Trying to query records less than six months old but older than 30 days.

Trying to query users that haven't logged in for at least thirty days but not more than six months ago.

Table structure is datetime - username:

Code:

('2008/04/13 02:59:48 PM', 'user3'),
('2008/02/18 03:03:13 PM', 'user99'),
('2008/02/18 02:59:48 PM', 'user3'),

Because user3 has logged in recently the code should only display user99. I can only get it to display all the records or no records.

Here are the queries:

Code:

$twentynine_days_ago = date("Y/m/d", strtotime("-29 days"))."\n";
                        $six_months_ago = date("Y/m/d", strtotime("-180 days"))."\n";
                        $get_thirtyday_login = "SELECT UserName FROM salesloginlog WHERE DateTime BETWEEN '$six_months_ago' AND '$twentynine_days_ago' ORDER BY UserName DESC";
                        $result_thirtyday_login=mysql_query($get_thirtyday  _login);
                        while ($row_thirtyday_login = mysql_fetch_assoc($result_thirtyday_login))
                        {
                        $thirty_day = $row_thirtyday_login['UserName'];
                        $get_last_login = "SELECT UserName, DateTime FROM salesloginlog WHERE UserName = '$thirty_day' AND DateTime > '$twentynine_days_ago' ORDER BY UserName DESC LIMIT 1";
                        $result_last_login=mysql_query($get_last_login);
                        while ($row_last_login = mysql_fetch_assoc($result_last_login))
                        {
                        $last = $row_last_login['UserName'];
                        if ($last == ''){
                        echo "$thirty_day<br>";
			}
                        else{
                        echo '';}
                        }
                        }

April 23rd, 2008, 03:03 AM

DavidMR

Contributing User

Join Date: Apr 2007

Location: Ireland

Posts: 710

Time spent in forums: 1 Week 4 Days 13 h 26 m
Reputation Power: 2

ok havent actually tried this out, but how about something like:

PHP Code:


		
			
 $today = mktime(0, 0, 0, date("m")  , date("d"), date("Y"));

$lastmonth = mktime(0, 0, 0, date("m") , date("d")-29, date("Y"));

$sixmonths = mktime(0, 0, 0, date("m")-6 , date("d"), date("Y"));



$query = "SELECT UserName, DateTime FROM salesloginlog WHERE DateTime BETWEEN '" . $lastmonth . "' AND '" . $sixmonths . "' ORDER BY UserName DESC LIMIT 1";

__________________
When I die, I want to go peacefully like my Grandfather did, in his sleep -- not screaming, like the passengers in his car.

April 23rd, 2008, 07:00 AM

matthewst

Contributing User

Join Date: Apr 2007

Posts: 136

Time spent in forums: 1 Day 22 h 12 m 32 sec
Reputation Power: 2

Thanks DavidMR, but that would select every user between six months and thirty days ago (including the ones that have logged in recently). I was trying to select the users who logged in six mon to 30 days ago and compare that to the users who have logged in less then thirty days ago.

The solution below works but is a little slow. Any ideas on how to make it faster?

Code:

$get_thirtyday_login = "SELECT s1.UserName, s1.DateTime FROM salesloginlog AS s1
						WHERE
						s1.DateTime = (SELECT max(s2.DateTime) FROM salesloginlog AS s2 WHERE s2.UserName = s1.UserName)
						AND
						s1.DateTime <= '$twentynine_days_ago'
						AND
						s1.DateTime >= '$six_months_ago'";

April 23rd, 2008, 07:14 AM

matthewst

Contributing User

Join Date: Apr 2007

Posts: 136

Time spent in forums: 1 Day 22 h 12 m 32 sec
Reputation Power: 2

Sorry double posted.

April 23rd, 2008, 08:13 AM

DavidMR

Contributing User

Join Date: Apr 2007

Location: Ireland

Posts: 710

Time spent in forums: 1 Week 4 Days 13 h 26 m
Reputation Power: 2

sorry if im not getting this right, but could you use 2 betweens?

PHP Code:


		
			
 $today = mktime(0, 0, 0, date("m")  , date("d"), date("Y"));

$lastmonth = mktime(0, 0, 0, date("m") , date("d")-29, date("Y"));

$sixmonths = mktime(0, 0, 0, date("m")-6 , date("d"), date("Y"));



$query = "SELECT UserName, DateTime FROM salesloginlog WHERE DateTime BETWEEN '" . $sixmonths . "' AND '" . $lastmonth . "' AND DateTime Between '" . $lastmonth . "' AND '" . $today . "' ORDER BY UserName DESC LIMIT 1";

the numbers in the variables etc, -6 and -29 can be changed to change the ranges...

May 6th, 2008, 01:04 PM

gmt2001

Contributing User

Join Date: Apr 2007

Posts: 80

Time spent in forums: 3 h 9 m 41 sec
Reputation Power: 2

On the date ('2008/04/13 02:59:48 PM') try the following php function:
strtotime( String $time );

Usage: strtotime('2008/04/13 02:59:48 PM'); //returns that date/time as a unix timestamp (seconds from epoch).

PHP Code:


		
			
 $unixtime=strtotime($db_timestamp);
if($unixtime + 15552000 < now()) //more then 6 months (assuming 30 days per month)

__________________
-gmt2001

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: