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
PM System
Moderator: General Moderators
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:
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, joshSet Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.