does this make sense? (optimally)

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
m3rajk
DevNet Resident
Posts: 1191
Joined: Mon Jun 02, 2003 3:37 pm

does this make sense? (optimally)

Post by m3rajk »

ok. so i'm making a rateme site. i think that's known.. well i wanna track who's actively online (like loaded a page in the last 5 min or such)

would creating a table with the following and then setting 0 for offline and 1 for online and touching it (setting online to 1) with every pageload make sense in an optimally speacking way, or is there a better way (other tables i have are: users (the main table which is checked on any page that is restricted access as well as logging in since it stores the pws with an md5 mask on them... all cookies are currently plaintext with a check that the username an pw match what's on record AND the username has permission to access the page when going to a restricted page), stats (holds members stats), comments (holds comments on members), bio (holds member bios), interests (holds member's interests), friends (entries used to create a member's friends list), votes (tracks who you've voted for.. will be manually reset once a month so that scores aren't changed by one person voting for him/herself 1000 times)):

tracking who's online
CREATE TABLE online(
username varchar(15) NOT NULL PRIMARY KEY,
last_page_load timestamp,
online tinyint(1) default '0' NOT NULL PRIMARY KEY
) TYPE=MyISAM;

or is there something else that would be better to optimize this?
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

OK a few comments:

1. I appluad the use of username as your primary key. Usernames are unique and static and capture useful knowledge; therefore, they are a much better choice of PK than a system-provided auto-increment.

2. online shouldn't be a PK.

3.online and last_page_load are redundant. You only need one (last_page_load). in which case I would usggest folding it into the main user/people relation.
JPlush76
Forum Regular
Posts: 819
Joined: Thu Aug 01, 2002 5:42 pm
Location: Los Angeles, CA
Contact:

Post by JPlush76 »

I'm actually about to make the same thing for a site I'm doing.. online users.

How is your system going to work?
Everytime the user loads a page they are update in the online table?

Are you running some kind of cron job that clears out old entries or how are you working that?
User avatar
mikusan
Forum Contributor
Posts: 247
Joined: Thu May 01, 2003 1:48 pm

Post by mikusan »

I would suggest what i did. You must rewrite and use your own session handler, but that gives you a LOT of power. For example, i have written mine so that i will store all sessions in the database, and yes of course every session is timestamped. The advantage is that when you write your session handler it will automatically have you program a set of special functions like garbage collect (no need for a cron job).

So, you open a page, start sessions, your session handler sees you have no session so it assigns a new session ID and timestamps you and writes to database... you load another part of the page and start sessions again, this time, your session handler checks for an existing session and since there is it updates the row in the database containing the sess id, and renewing your timestamp (i use a timestamp called expiry where i just set time() + $expirytime.

You can find session handlers in the phpmanual, i believe they are wtritten for file-based sessions... if you need help with a database driven session handler let me know... but mine works miracles... ;)
Judas
Forum Commoner
Posts: 67
Joined: Tue Jun 10, 2003 3:34 pm
Location: Netherlands

Post by Judas »

I should do it all in a .csv file (don't alway's use db's)
Post Reply