Trying to query records older than 6 months but not more than 30 days old

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

[947740]

That does not make any sense to me.  Everything older than 6 months is going to be more than 30 days old.  Right?

 

EDIT: I read that wrong.

[Barand]

Choose a better name than "datetime", it's a reserved word

SELECT UserName, MAX(`datetime`) as latest
FROM salesloginlog
GROUP BY UserName
HAVING latest BETWEEN CURDATE() - INTERVAL 6 MONTH AND CURDATE() - INTERVAL 30 DAY

[matthewst]

I couldn't get this to work:

$get_thirtyday_login = "SELECT UserName, MAX(`datetime`) as latest
                        FROM salesloginlog
                        GROUP BY UserName
                        HAVING latest BETWEEN CURDATE() - INTERVAL 6 MONTH AND CURDATE() - INTERVAL 30 DAY";
                        $result_thirtyday_login=mysql_query($get_thirtyday_login);
                        while ($row_thirtyday_login = mysql_fetch_assoc($result_thirtyday_login))
                        {
                        $thirty_day = $row_thirtyday_login['UserName'];

                        if ($thirty_day != ''){
                        $get_name = "SELECT fname, lname FROM sales_rep WHERE username = '$thirty_day'";
                        $result_get_name=mysql_query($get_name);
                        while ($row_get_name = mysql_fetch_assoc($result_get_name))
                        {
                        $fname = $row_get_name['fname'];
                        $lname = $row_get_name['lname'];
                        }
		echo "These users have not loggen in for 30 days:<br>";
                        echo "$fname " . "$lname<br";
                        }
                        else{
                        echo '';}
                        }}