Delete records older than a few minutes?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Delete records older than a few minutes?

Post by Stryks »

Hi all,

I'm wanting to make a routine which updates a table in the database every time a page view is made so that I can track who is online. I have set this up so far, saving the users id and the time in a datetime field which is set with now().

How would I go about calling entries made in the last, say, 2 minutes?

Also, what would be the best procedure in terms of disposing of this data? Would it be best to delete all entries older than the 2 minutes and then requery to get what is left? Perhaps this could be achieved with one query?

Or would it be best to store all of this data for a set period of say, 1 month, so that it could be used to gather statistics.

Any ideas greatly appreciated.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

generally.. the who's online list is generated by those sessions that were last accessed within X minutes. All sessions older than, say 2 hrs are deleted. This has been talked about before.. plenty.
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Post by Stryks »

Thanks for the reply feyd, but this isnt really what I'm looking for.

I need it to be very much more accurate than that, within the space of a few minutes.

Then, if I am to use the sessions to do this, how do I do it keeping in mind that I am using a session handler to store my sessions in my database. If I pull this data from there, then I am effectively facing the same problem. How do I get the data from just the last few minutes.

Just a comment for your consideration, telling me that it has been talked about plenty doesnt really help anyone. If I had been able to find something in search then I wouldnt have posted this message. I do appreciate the response though.

Any further assistance would be great.
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Post by Stryks »

Actually, further to that ... how can I associate a session with a particular user?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Code: Select all

session_table
------------
session_id (32 or 16 chars for md5's)
session_start (time type or int, the timestamp of when the session was started)
session_last (time type or int, the timestamp of when the session was last accessed) (updated on each page call)
session_ip (8 or 4 chars if ascii encoded, int if numericly encoded)
session_user_id (the exact same type of your user id's)
session_page (optional, whatever you type want, suggested is integer types to reference a pages table)
session_logged_in (optional, tinyint: 1 or 0)
session_data (optional)
  • updating all rows that match the current user_id and current ip with a new timestamp on each page access keeps those users that are browsing alive in the session table.
  • selecting all rows that have a session_last >= now - X minutes ago gives you the who's online list.
  • deleting all rows that have session_last < now - Y minutes/hours/whatever destroys the older sessions that you want to lose.
the update and delete queries are done on every page. The selection is done when the who's online list needs to be shown.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

now would probably be a good time to search the forums for some of the previous topics about sessions and databases...
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Post by Stryks »

I'm not really sure what to make of your instructions. I'm not sure if you are saying to save that information into the session or to change my session handler to store the data with the session.

This is my session handing code:

Code: Select all

<?php
/* Set up and start session handling using the database */
if (!$SESS_LIFE = get_cfg_var('session.gc_maxlifetime')) {
  $SESS_LIFE = 1440;
}

function _sess_open($save_path, $session_name) {
  return true;
}

function _sess_close() {
  return true;
}

function _sess_read($key) {
  $value_query = mysql_query("select value from Sessions where sesskey = '" . addslashes($key) . "' and expiry > '" . time() . "'")
  or die("Invalid query: " . mysql_error());
  $value = mysql_fetch_array($value_query);

  if (isset($value['value'])) {
    return $value['value'];
  }
    return false;
}

function _sess_write($key, $val) {
  global $SESS_LIFE;

  $expiry = time() + $SESS_LIFE;
  $value = $val;

  $check_query = mysql_query("select count(*) as total from sessions where sesskey = '" . addslashes($key) . "'");
  $check = mysql_fetch_array($check_query);

  if ($check['total'] > 0) {
    return mysql_query("update sessions set expiry = '" . addslashes($expiry) . "', value = '" . addslashes($value) . "' where sesskey = '" . addslashes($key) . "'");
  } else {
    return mysql_query("insert into sessions values ('" . addslashes($key) . "', '" . addslashes($expiry) . "', '" . addslashes($value) . "')");
  }
}

function _sess_destroy($key) {
  return mysql_query("delete from sessions where sesskey = '" . addslashes($key) . "'");
}

function _sess_gc($maxlifetime) {
  mysql_query("delete from sessions where expiry < '" . time() . "'");
  return true;
}

session_set_save_handler('_sess_open', '_sess_close', '_sess_read', '_sess_write', '_sess_destroy', '_sess_gc');
  
/* Start the session */
session_start();

/* If there is SESSION data, break it into friendly variables */
if(count($_SESSION) > 0){ extract($_SESSION, EXTR_SKIP); }
?>
Can you explain how you would go about storing the data you suggest?

Thanks
?>
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

your session handler looks fine... the information I was talking about is all stored in the database. to link the user to the session, you'll either need an field in the user table or a field in the session table that references into the other.
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Post by Stryks »

Nope.

I've tried messing around with all kinds of things and I just cant for the life of me figure out what it is you are telling me I should do.

I'll have to try to find another way.

Thanks for the feedback
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Post by Stryks »

I cant seem to come up with anything satisfactory. I've searched and searched but I cant seem to come up with anything relevant. Its hard to know what to search for. None of the obvious terms bring up anything anyhow.

Can I get some constructive help here guys?

At the start of each page I am calling that session script, whether the user is logged in or not. The session then contains some information, including the users IP and so on.

So what is it you are suggesting? Inserting the data into the session variable (which doesnt really make sense to me) or creating a table somewhere to link the session to a user, in which case arent I just creating a table separate to the sessions one in which to store the times which was my original idea?

Also ... it doesnt seem to work ... this whole now() - x minutes thing doesnt appear to have the desired effect when applied to a datetime field, and an int field doesnt make sense to me as it doesnt appear mathematically correct.

I'm really at a loss here. Any code or links to threads explaining the process would be a real help.

Thanks
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

viewtopic.php?t=23781

the code there will need some fiddling to match your usage.. but it's an example of what I'm talking about.
Post Reply