m3rajk — Thu, 07 Aug 2003 17:36:10 +0000

i figured there had to be something easier... i store the utc offset in hours, so the page just needs to multiply that by 60*60 and add that to a utc timestamp....

<?php
# this is a test to get utc/gmt times

$metime=date(\"M d Y H:i:s\", time());
$gmttime=gmdate(\"M d Y H:i:s\", time());
$gmttimestamp=time();

echo <<<END
<html>
<head>
<title>simple time test page</title>
</head>
<body>
<p>my time is: $metime
<br />if this works right, this will be 4 or 5 hours ahead since 
it's<!--'--> suppossed to be UTC/GMT time: $gmttime
<br />gmt timestamp? $gmttimestamp
</p>
</body>
</html>
END;
?>

does gmt time rather well.. so i've got half a mind to move from a datetime to a string that's the utc timestampa char that size is what... 2 bytes? while i'd need an int that's 4 bytes if i do it as a number...
in either case it's much cmaller than an 8 byte datetime....i'll still use the 4byte timestamp for last_login_date since i want that to update automatically when i update the last_login_ip

m3rajk — Tue, 05 Aug 2003 04:14:22 +0000

yeah. that's what i meant, it uses the system time, so i can't use it as a gmt. i wanna calculate from that. i was hoping for something simple inside fo sql to do that (maybe i can suggest it for an update?)

the only thing i could think of was using php's gmmktime() somehow.

for some reason it suprises me there's not built in function

Mark Hensler — Tue, 05 Aug 2003 03:42:25 +0000

NOW() inserts the system time. Be carefull how you word it. I have a server in my house (Pacific),
as well as a server in Central, and Eastern. The NOW() function returns the system time for each,
not the time in my timezone.

I got this from the User Comments section of 6.3.4 Date and Time Functions:

SELECT DATE_SUB(NOW(), INTERVAL TIME_TO_SEC(DATE_SUB(NOW(), INTERVAL UNIX_TIMESTAMP()-12*3600 SECOND))-12*3600 SECOND) AS GMT

m3rajk — Mon, 04 Aug 2003 23:25:20 +0000

kewl.. umm.. NOW() gives MY date and time. is there a way to have it insert the date and time for UTC/GMT?

Mark Hensler — Fri, 01 Aug 2003 07:34:23 +0000

Yes. You can add columns to tables after creation.
ALTER table_name ADD column_name data_type_and_whatnot

m3rajk — Fri, 01 Aug 2003 03:33:56 +0000

i like that. i'm still shuffling around a few feilds instead of making the db.

i think we're close right now. there's three things i want for forums that i don't have anywhere and one of them could easily be put into the user stats where one other thing i want for the forums already is.

the items and their functionality are
postcount -- to keep track of postcount even though i feel like displaying it could encourage "postwhoring", i may want to display it in the future if the users want it (postranks would be calculated on the fly on that i guess)
affilliation -- this is in the stats right now, basically saying if the user donated to the site, or if i make a paid level down the road (something i'm thinking fo doing) to denote those of that level
signature -- same thing as here. i'd use a tinytext feild and limit it that way. odd note: i've yet to see an asp based site with a signature and i've yet to see a php based one without it. (personally i like it)
"tagline" -- since i'm not going to have postranks and people like that, i figured this is something the mods and admins can set once we see what a user is like and have fun witht hem and give them something in it's place (personally i'm also looking for a better name for it)

i'm thinking right now i should add them to the stats table and then call that from the forums to get them. i'm wondering what the opinion of those that have more experience with this is.

also, i know you can drop a column from a table and change the type of data in it (ie alter an int to a big int) but is there a way to add a column? the fact i don't know of one has had me hesitant to make the db until i'm sure i have all the columns, especially when somethings are being tossed around so columns are being moved slightly etc. on that note, if anyone with more experience would like to look over my current table set up and give me pointers to optimized the db, i would appreciate it. either message me or post here and let me know

Mark Hensler — Fri, 01 Aug 2003 00:16:55 +0000

No. MySQL will not allow the default value for columns to be a function.

Yes. You can run an INSERT statement setting the datetime_field=NOW().

As for the Who's Online...

With your current DB table setup, I would just add a field to the user table called `last_activity`. Whenever that user loads a page, or performs any other trackable action, set that fields value to NOW().

To see who's online:
SELECT * FROM user_table WHERE last_activity>=DATE_SUB(NOW(), INTERVAL 5 MINUTE)

m3rajk — Thu, 31 Jul 2003 19:21:16 +0000

let me add to this......

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?