Re: Message storing problem.

In case you care, here's the table structure that the Everything Chatterbox system uses:

CREATE TABLE message (
  message_id int(11) DEFAULT '0' NOT NULL auto_increment,
  msgtext char(255) DEFAULT '' NOT NULL,
  author_user int(11) DEFAULT '0' NOT NULL,
  tstamp timestamp(14),
  for_user int(11) DEFAULT '0' NOT NULL,
  PRIMARY KEY (message_id),
  KEY for_user (for_user)
);
[download]

This basically breaks down to:

message_id -- arbitrary AUTO_INC primary key

msgtext -- 255 chars of communication

author_user -- reference to authors's unique ID

tstamp -- timestamp set on insertion of message, used for ordering

for_user -- optional field, if left blank it's considered "chat" if set, it becomes a "message" for whatever user ID it references

For chat, your sql query looks something like:

  my $expiration_seconds = 500;
  $dbh->prepare("SELECT * FROM message 
     WHERE for_user=0 AND unix_timestamp(now())-unix_timestamp(tstamp)
+ < $expiration_seconds ORDER BY tstamp");
[download]

then for viewing a user's specific messages:

  my $UID = getUserIdSomehow();
  $dbh->prepare("SELECT * FROM message 
     WHERE for_user=$UID ORDER BY tstamp");
[download]

It's really a pretty simple system, but it does depend on having numeric IDs that you can look up your users with. And don't worry too much about running out of INT -- 32-bits gives you lots of room to talk.

-nate

Comment on Re: Message storing problem. Select or Download Code

Replies are listed 'Best First'.
Re: Re: Message storing problem. by salvadors (Pilgrim) on Jan 21, 2001 at 00:30 UTC
`my $UID = getUserIdSomehow(); $dbh->prepare("SELECT * FROM message WHERE for_user=$UID ORDER BY tstamp");` [download] Surely you don't prepare the query with a variable in it? You should be using placeholders: `my $UID = getUserIdSomehow(); my $sth = $dbh->prepare("SELECT * FROM message WHERE for_user=? ORDER BY tstamp");` [download] which then gets executed with `$sth->execute($UID)` Tony	[reply] [d/l] [select]