Help With Max Date And Sql Queries

[Alechko]

Hello folks,

I got a problem with dates and because of it the queries is one big mess.

 

my SQL table look like this:

USERS

id (auto_increment), name (varchar), birthday(date) and so on..

 

ENTRIES

id (auto_increment), userID (int), time (datetime)

 

I'm trying to display the whole users who didn't entered mysite for 7/14/30 days.

 

I tried to do something like that:

 

 $sql = $db->Query("
SELECT `users`.`id` AS `userID`, `users`.`fullname` AS `fullName`.`mobile` AS `mobile`,`entries`.`time` AS `time` FROM `users` JOIN `entries` ON `users`.`id` = `entries`.`userID` WHERE `entries`.`time` BETWEEN CURDATE() AND CURDATE() - INTERVAL 8 DAY") or die(mysql_error());

 

and It don't show me nothing.. 0 queries.

 

I tried to do something like with 2 queries..:

 

$sql = $db->Query("SELECT `userID`,max(`time`) AS `time` FROM `entires`") or die(mysql_error());
while($row = $db->FetchAssoc($sql)) { .
$userID = $row['userID'];
$time = date('Y-m-d',strtotime($row['time']));
$sql2 = $db->Query("SELECT `fullName`,`registerDate`,`endDate`,`mobile` FROM `users` WHERE ('{$time}' BETWEEN CURDATE() AND CURDATE() - INTERVAL 8 DAY)") or die(mysql_error());
}

 

But it didn't work too.

 

Help someone?

 

thanks,

appriciate it.

Edited October 23, 2012 by Alechko

[shaddowman]

$sql = $db->Query("
SELECT `users`.`id` AS `userID`, `users`.`fullname` AS `fullName`.`mobile` AS `mobile`,`entries`.`time` AS `time` FROM `users` JOIN `entries` ON `users`.`id` = `entries`.`userID` WHERE `entries`.`time` BETWEEN CURDATE() AND CURDATE() - INTERVAL 8 DAY") or die(mysql_error());

 

I think you need to use date aritmethic functions like DATEADD and DATEDIFF. Please follow this URL http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_datediff

 

Example:

 

Select date_add(curdate(), interval 31 day);

[Barand]

CURDATE() - INTERVAL 8 DAY is fine (in fact I always prefer that to DATESUB() ) but when you use BETWEEN x AND y, x must be less then y. ie you need

BETWEEN CURDATE() - INTERVAL 8 DAY AND CURDATE()

[mikosiko]

In addition to Barand point, in your first SELECT you should:

- Fix the evident syntax errors around the `mobile` field

- Even when is not part of any error, the usage of alias is unnecessary and doesn't make sense in this case.

- because you are trying to get the users that HAVE NOT log-in in certain time frame you must use a LEFT JOIN and filter the record that has entries.time NULL (or 0000-00-00)

- finally you must use WHERE DATE_FORMAT(entries.time, '%Y-%m-%d') BETWEEN <rest of the sentence here>

[Alechko]

thanks for the replies, but it stiil don't want to work...

 

This is mySQL: (followed by your comments)

 
$sql = $db->Query("SELECT `users`.`id` AS `userID`, `users`.`registerDate` AS `registerDate`, `users`.`endDate` AS `endDate`, `users`.`fullname` AS `fullName`,`users`.`mobile` AS `mobile`,`entrygym`.`time` AS `time` FROM `users` JOIN `entrygym` ON `users`.`id` = `entrygym`.`userID` [b]WHERE DATE_FORMAT(`entrygym`.`time`,'%Y-%m-%d') BETWEEN CURDATE() - INTERVAL '{$DateString}' DAY  AND CURDATE() ORDER BY `entrygym`.`time` DESC"[/b]) or die(mysql_error());

 

The main problem:

I did 8 days interval and it showed me a user who visited at '23/10/2012' and I need the users who didn't visited for 8 days. (The last date from today - 17/10/12)

 

And I understood something:

For example, today is the 24/10/12 and user X last visit was at 16/10/12. so I need to count somehow the days between those dates and display if the count over 8,14,31 and so on.

[mikosiko]

you ignored my 3rd suggestion completely (well... the 2nd too, but that is not relevant to the issue)

[Alechko]

I didn't fully understand your 3rd suggestion - but thanks anyway, I solved it!

 

thanks!