How can one list all nodes that have been added since a uid's last login? Which table contains the login information?
Something like SELECT nid FROM node WHERE created > ( SELECT MAX( login ) FROM users) should do the work
----- Original Message ----- From: "Haisam K. Ido" haisam@ido.org To: support@drupal.org Sent: Wednesday, May 02, 2007 2:56 PM Subject: [support] how to get the list of new nodes since last login?
How can one list all nodes that have been added since a uid's last login? Which table contains the login information? -- [ Drupal support list | http://lists.drupal.org/ ]
Thanks Nicolas. But I don't think that would work because by the time that query runs the 'login' field in the 'users' table would have been updated with the latest 'login' time. Is there another table that keeps the history of user logins? I know there must be one since users' logins are logged.
Nicolas Tostin wrote:
Something like SELECT nid FROM node WHERE created > ( SELECT MAX( login ) FROM users) should do the work
----- Original Message ----- From: "Haisam K. Ido" haisam@ido.org To: support@drupal.org Sent: Wednesday, May 02, 2007 2:56 PM Subject: [support] how to get the list of new nodes since last login?
How can one list all nodes that have been added since a uid's last login? Which table contains the login information? -- [ Drupal support list | http://lists.drupal.org/ ]
On Thursday May 3 2007 4:29 am, Haisam Ido wrote:
Thanks Nicolas. But I don't think that would work because by the time that query runs the 'login' field in the 'users' table would have been updated with the latest 'login' time. Is there another table that keeps the history of user logins? I know there must be one since users' logins are logged.
You would probably have to create a custom module and use hook_user to get the login time (from the &$user variable, with either the load, login or update ops) before Drupal has actually logged in the user, and save it somewhere in another database table/field, or using the variable_set/get() functions.
http://api.drupal.org/api/5/function/hook_user
Jason:
This does it for finding out the last time a uid closed their session.
SELECT uid,timestamp FROM `watchdog` where message like 'Session closed for%'
Jason Flatt wrote:
On Thursday May 3 2007 4:29 am, Haisam Ido wrote:
Thanks Nicolas. But I don't think that would work because by the time that query runs the 'login' field in the 'users' table would have been updated with the latest 'login' time. Is there another table that keeps the history of user logins? I know there must be one since users' logins are logged.
You would probably have to create a custom module and use hook_user to get the login time (from the &$user variable, with either the load, login or update ops) before Drupal has actually logged in the user, and save it somewhere in another database table/field, or using the variable_set/get() functions.
uid=$uid
$sql = "SELECT node.uid,watchdog.timestamp, node.changed FROM `watchdog`,node where watchdog.message like 'Session closed for%' AND node.changed >= watchdog.timestamp and node.uid=watchdog.uid and node.uid=$uid";
I believe that the above query returns all nodes which have changed since $uid's last login.
Haisam K. Ido wrote:
Jason:
This does it for finding out the last time a uid closed their session.
SELECT uid,timestamp FROM `watchdog` where message like 'Session closed for%'
Jason Flatt wrote:
On Thursday May 3 2007 4:29 am, Haisam Ido wrote:
Thanks Nicolas. But I don't think that would work because by the time that query runs the 'login' field in the 'users' table would have been updated with the latest 'login' time. Is there another table that keeps the history of user logins? I know there must be one since users' logins are logged.
You would probably have to create a custom module and use hook_user to get the login time (from the &$user variable, with either the load, login or update ops) before Drupal has actually logged in the user, and save it somewhere in another database table/field, or using the variable_set/get() functions.
On Thursday May 3 2007 10:23 am, Haisam K. Ido wrote:
uid=$uid
$sql = "SELECT node.uid,watchdog.timestamp, node.changed FROM `watchdog`,node where watchdog.message like 'Session closed for%' AND node.changed >= watchdog.timestamp and node.uid=watchdog.uid and node.uid=$uid";
I believe that the above query returns all nodes which have changed since $uid's last login.
So I guess you have your site configured to never delete any log entries?
admin/settings/error-reporting
Agreed that's a weakness of my approach.
Jason Flatt wrote:
On Thursday May 3 2007 10:23 am, Haisam K. Ido wrote:
uid=$uid
$sql = "SELECT node.uid,watchdog.timestamp, node.changed FROM `watchdog`,node where watchdog.message like 'Session closed for%' AND node.changed >= watchdog.timestamp and node.uid=watchdog.uid and node.uid=$uid";
I believe that the above query returns all nodes which have changed since $uid's last login.
So I guess you have your site configured to never delete any log entries?
admin/settings/error-reporting
How long will it be between logins for any user? If it is very long, all content since last login would be impractical anyway - there would be too much new content for such a feature to be useful. If it isn't long, you can just delete log entries older than, say, twice the average time between logins or something like that.
On 5/3/07, Jason Flatt drupal@oadaeh.net wrote:
On Thursday May 3 2007 10:23 am, Haisam K. Ido wrote:
uid=$uid
$sql = "SELECT node.uid,watchdog.timestamp, node.changed FROM `watchdog`,node where watchdog.message like 'Session closed for%' AND node.changed >= watchdog.timestamp and node.uid=watchdog.uid and node.uid=$uid";
I believe that the above query returns all nodes which have changed since $uid's last login.
So I guess you have your site configured to never delete any log entries?
admin/settings/error-reporting
-- Jason Flatt http://www.oadaeh.net/ Father of Six: http://www.flattfamily.com/ (Joseph, 13; Cramer, 11; Travis, 9; Angela; Harry, 5; and William, 12:04 am, 12-29-2005) Linux User: http://www.kubuntu.org/ Drupal Fanatic: http://drupal.org/ -- [ Drupal support list | http://lists.drupal.org/ ]
Quoting "Haisam K. Ido" haisam@ido.org:
Jason:
This does it for finding out the last time a uid closed their session.
SELECT uid,timestamp FROM `watchdog` where message like 'Session closed for%'
What if the age for the watchdog row becomes old and is purged?
Earnie
-
Earnie Boyd -
Haisam Ido -
Haisam K. Ido
-
Jason Flatt -
Jean Gazis -
Laura Scott -
Nicolas Tostin