Trying to query users that haven't logged in for thirty days.

[matthewst]

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:

('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:

$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 '';}
                        }
                        }

[Xager]

Not sure but i assume it's because you have "2008/04/13 02:59:48 PM" in the timestamp and only "2008/04/13" in the date

[bradgrafelman]

Why isn't column DateTime a DATETIME column type?

[matthewst]

The datetime column was like that when I took over. The solution below works but is a little slow. Any ideas on how to make it faster?

$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'";

[bradgrafelman]

matthewst wrote:

Any ideas on how to make it faster?

Convert the datetime column to a DATETIME type.