[compsci]

Hello all,

I am creating a forum from scratch and i have go to the stage where i want to create a private messaging system, just like the one we have in this forum.

So my idea is to have a table called messages with fields such as usersent and userreceived. This means i will search for each user their messages from this table. Is this efficient? Is there a better way?

Thanks all, I appreciate any help.

[georgev]

This would seem fairly efficient...
One message can be sent by one user to one user

--all messages to one user (inbox)
SELECT msgID
     , userSent
     , msgTitle
     , replied
     , read
     , dateSent
FROM   messages
WHERE  userTo = 'compsci'

--Sent messages for one user (sent items)
SELECT msgID
     , userTo
     , msgTitle
     , dateSent
FROM   messages
WHERE  userSent = 'compsci'

One thing it does limit you is that you can only send one message to one user at a time.

[r937]

Originally Posted by georgev

One thing it does limit you is that you can only send one message to one user at a time.

each message to only one user ever

[compsci]

Originally Posted by r937

each message to only one user ever

Is this what you mean georgev?

[georgev]

Nope; I mean that you can only send one message at a time to one recipient.
I don't follow what Rudy is getting at I'm afraid!

[r937]

if the table has usersent and userreceived, then each message can be sent to only one user

ever



perhaps you could explain what you meant by "one at a time"

[compsci]

Originally Posted by r937

if the table has usersent and userreceived, then each message can be sent to only one user

ever

The above fields [usersent and userreceived] are what i declared. The table i am creating will not contain those fields only. My idea, is that when a user sends a message. The fields of who sent it and who the sender is sending to are both recorded by the messages table.

So when someone checks there inbox. I will test who is logged in and based on this:

"SELECT from messages WHERE userrceived='$loggedonuser'"

and for the outbox:

"SELECT from messages WHERE usersent='$loggedonuser'"

So this meas a user can receive many emails and send many emails.

[r937]

can a user send a PM to more than one other user?

with your current setup, he would have to send separate messages, which means that there would be multiple rows identical in everything except the recipient, which would actually bloat the table and inflate the message count

[georgev]

That was my point

[r937]

yeah, but your point ("... to one user at a time") gives the false impression that you can send the same message to another user later

and my point is that it isn't the same message!!!

[compsci]

Originally Posted by r937

can a user send a PM to more than one other user?

with your current setup, he would have to send separate messages, which means that there would be multiple rows identical in everything except the recipient, which would actually bloat the table and inflate the message count

--> I guess yor talking about georgev setup??

[r937]

no, i'm talking about your setup, compsci

[compsci]

Originally Posted by r937

no, i'm talking about your setup, compsci

lol ok

Well for my setup you can send to different users.

If a user wishes to send a message then they will enter the username they wish to send to [which will go in the "userreceived" field]. They have a choice of all registered users of course. This means that they can send to different people!

Those who wish to recieve their messages will got to their inbox and i will use a statement like this:

"SELECT from messages WHERE userreceived='$loggedonuser'"

and for the outbox:

"SELECT from messages WHERE usersent='$loggedonuser'"

-So i can send to multiple users one at a time or even many users at a time, since all i have to do is create a row for each user its being sent to.

Is this clear? Am i missing something?

[r937]

so you can only send a message to one user, right?

[compsci]

Originally Posted by r937

so you can only send a message to one user, right?

No, i can send to many.

To: john, paul, david
From: compsci
Message: ~~

Now i will create 3 rows for the above, since there are three people and for each of these people to receive their messages the query will look like this:

"SELECT from messages WHERE userreceived='john'"
"SELECT from messages WHERE userreceived='paul'"
"SELECT from messages WHERE userreceived='david'"

So i can send to many people at different times and i can also send to many people at the same time. Where they get the same message.

Is my logic flawed?