Page 1 of 1

PM System

Posted: Tue Oct 17, 2006 3:20 pm
by bob_the _builder
Hi,

I am tidying up a pm system I made awhile ago .. A few questions I have.

When a message is submited, I write two copys of the message to the same table, in the table I have a field called "sent" one message gets marked with "1" for inbox, the other "0" for outbox. Is that okay or should I really be storing inbox and outbox PM's to seperate tables?

Also I store the users name of the sender and the receiver in the database as well for ease of getting all the data I need for output. Should I be storing the userid's instead and use joins in the query to get the user names for output?


Thanks

Posted: Tue Oct 17, 2006 3:46 pm
by s.dot
No need for separate tables for different folders. On my web site, I have a very complex system going that uses 2 tables. One for the actual message, then one for the data. When a new message is sent, the message gets inserted into the messages table. Then I store two records into the data table.. one for the sender and one for the receiver. This is also where the folder gets stored. The first record has a 1 for outbox, the second record a 2 for inbox. Then I update them accordingly.

Secondly, store user IDs. Querying for them is easy, and faster than using a JOIN. Like this:

Code: Select all

//query for messages!
$result1 = mysql_query("SELECT * FROM `msg_data` WHERE `folder` = '$folder' AND `user` = '$user'");

//store USERIDS
$user_id_list = array();

while($array = mysql_fetch_assoc($result1))
{
     $user_id_list[] = $array['from_id'];
}

//this should now look like 1,2,3,4
$user_id_list = implode(',', $user_id_list);

//query for these usernames
$result2 = mysql_query("SELECT `id`,`username` FROM `users` WHERE `id` IN($user_id_list)");

//store usernames id => username
$usernames = array();
while($array2 = mysql_fetch_assoc($result2))
{
   $usernames[$array2['id']] = $array2['username'];
}

//reset our original query
mysql_data_seek($result1,0);

//now we can loop through and display messages WITH usernames
while($messages = mysql_fetch_assoc($result1))
{
    echo $usernames[$messages['from_id']];
}

//this should output all the usernames
//bob, mary, tim, josh