Oh no! Where's the JavaScript?
Your Web browser does not have JavaScript enabled or does not support JavaScript. Please enable JavaScript on your Web browser to properly view this Web site, or upgrade to a Web browser that does support JavaScript.
Sign In
Not a member yet? Click here to register.

V9 PM Table Structrure Explained

This thread is to clarify the mechanics and the table structure for the version 9 core difference than other existing versions.

Column Name Description
message_id ID unique
message_to Receipient User ID
message_from Sender User ID
message_user Owner user ID
message_subject Heading of message
message_message Content of message
message_smileys Use smileys in message, 'y' or 'n', default 'y' indicates yes
message_read Read status, 1 or 0 - 0 indicates new
message_datestamp timestamp
message_folder designated sections inbox - 1 inbox , 2 outbox, 3 archive

Typical situation and data row entry value examples - User 1 sends a message to User 201:

First, entry is the message sent to user 201 with the following values.

message_id = auto increment
message_to = 201
message_from = 1
message_user = 201
message_subject = "Test"
message_message = "Test"
message_smileys = "y"
message_read = 0
message_datestamp = time()
message_folder = 0

AND a copy of your sent message will be made available for user 1

message_id = auto increment
message_to = 201
message_from = 1
message_user = 201
message_subject = "Test"
message_message = "Test"
message_smileys = "y"
message_read = 0
message_datestamp = time()
message_folder = 1

- message_user is 201, indicating this message only can be viewed, deleted, and archived by user 201.
- message_folder must be 0 if current message_user is a recipient. 1 if current message_user is a sender.
- There was no swapping of the `message_to` and `message_from` values in any case. `message_to` always means that original message was sent to whom. Vice versa for `message_from`.
- Only difference here is that we keep a copy for ourselves when sending a message. This is because message_user is the owner of the message entry. Nobody will have access to them. If you delete your message, the other party will still be able to view the original message since he has a copy of it as well.

The SQL callback for all received PM will be:
CodeDownload  
$my_inbox = dbquery("SELECT message_id FROM ".DB_MESSAGES." WHERE message_to=:my_id AND message_folder=0");



The SQL callback for all sent PM will be:
CodeDownload  
$my_inbox = dbquery("SELECT message_id FROM ".DB_MESSAGES." WHERE message_to=:my_id AND message_folder=1");



Notice that in both occassion, the condition column is "message_to" and "message_folder"

The SQL callback for all received and sent PM will be:
CodeDownload  
$my_inbox_outbox = dbquery("SELECT message_id FROM ".DB_MESSAGES." WHERE message_user=:my_id");


or
CodeDownload  
$my_inbox_outbox = dbquery("SELECT message_id FROM ".DB_MESSAGES." WHERE (message_to=:my_id AND message_folder=0) OR (message_to=:my_id1 AND message_folder=1)");

I know this thread is old, but I find it relevant. 

I updated from v7 to v9. 

I don't know what happened, but my site's private messages aren't working correctly. None show up. 

I noticed if I use the new table created from a new site and not using my old stuff there's a difference with the "message_user" numbers. On the old table the "message_user" id is always 0
on the new table it seems to match the "message_to"

Is this code above a script to update the table manually? 

How would I go about fixing this?

Edit: I took a look at the old table in the database before I updated and the 'message_user" category isn't present. So I guess it's a new thing for v9? 

Thanks for any help. 
The structure for private messages table is different in V7 and V9.
If you are using infusions sendings PM's, this one needs updated.

There was a post about it, but is seems removed.

in V9 the Table has an extra column witch needs to be filled to make the system work
Is there an easy way to fill those columns?

Quote

There was a post about it, but is seems removed.

It had nothing to do with Infusions upgrade and our Standards. Using the infusion_include.php and the send_pm() function will work.
If you hard code the PM send with direct sql injection, naturally you need to do amends. But that is not Fusion standards, the correct way is to use send_pm() function from infusions_include. We do not cover possible customizations and non-standard approaches in our guides.
 
If you have an old infusion, this function send_pm is not used. It is the common sense to inject sql directly.
In stead of throwing mud, please explain how the guy can adapt his code with this function.

The function is not described in the developper documentation on this site.

And please stop modifying forums posts if there is something in it you do not agree with. 

In Post#1 from Chan, I guess something is also not right, see remarks below:

Column Name Description
message_id ID unique
message_to Receipient User ID
message_from Sender User ID
message_user Owner user ID
message_subject Heading of message
message_message Content of message
message_smileys Use smileys in message, 'y' or 'n', default 'y' indicates yes
message_read Read status, 1 or 0 - 0 indicates new
message_datestamp timestamp
message_folder designated sections inbox - 1 inbox , 2 outbox, 3 archive

Typical situation and data row entry value examples - User 1 sends a message to User 201:

First, entry is the message sent to user 201 with the following values.

message_id = auto increment
message_to = 201
message_from = 1
message_user = 201
message_subject = "Test"
message_message = "Test"
message_smileys = "y"
message_read = 0
message_datestamp = time()
message_folder = 0  Should be 1 according to above

AND a copy of your sent message will be made available for user 1

message_id = auto increment
message_to = 201
message_from = 1
message_user = 201
message_subject = "Test"
message_message = "Test"
message_smileys = "y"
message_read = 0
message_datestamp = time()
message_folder = 1 Should be 2 according to above

- message_user is 201, indicating this message only can be viewed, deleted, and archived by user 201.
- message_folder must be 0 if current message_user is a recipient. 1 if current message_user is a sender. Message folder 0 is not defined
- There was no swapping of the `message_to` and `message_from` values in any case. `message_to` always means that original message was sent to whom. Vice versa for `message_from`.
- Only difference here is that we keep a copy for ourselves when sending a message. This is because message_user is the owner of the message entry. Nobody will have access to them. If you delete your message, the other party will still be able to view the original message since he has a copy of it as well.

The SQL callback for all received PM will be:
CodeDownload  
should be: $my_inbox = dbquery("SELECT message_id FROM ".DB_MESSAGES." WHERE message_to=:my_id AND message_folder=1");


CodeDOWNLOAD  
CodeDownload  
$my_inbox = dbquery("SELECT message_id FROM ".DB_MESSAGES." WHERE message_to=:my_id AND message_folder=0");

PHP
Copy



The SQL callback for all sent PM will be:
 Should be: 
CodeDownload  
$my_inbox = dbquery("SELECT message_id FROM ".DB_MESSAGES." WHERE message_from=:my_id AND message_folder=2");



CodeDOWNLOAD  
CodeDownload  
$my_inbox = dbquery("SELECT message_id FROM ".DB_MESSAGES." WHERE message_to=:my_id AND message_folder=1");


PHP
Copy

Notice that in both occassion, the condition column is "message_to" and "message_folder"

The SQL callback for all received and sent PM will be:
CodeDOWNLOAD  
CodeDownload  
$my_inbox_outbox = dbquery("SELECT message_id FROM ".DB_MESSAGES." WHERE message_user=:my_id");
NO, only received


PHP
Copy

or
CodeDOWNLOAD  
CodeDownload  
$my_inbox_outbox = dbquery("SELECT message_id FROM ".DB_MESSAGES." WHERE (message_to=:my_id AND message_folder=0) OR (message_to=:my_id1 AND message_folder=1)");



PHP

Copy

should be: 
CodeDownload  
$my_inbox_outbox = dbquery("SELECT message_id FROM ".DB_MESSAGES." WHERE (message_to=:my_id AND message_folder=1) OR (message_from=:my_id AND message_folder=2)");

You don´t get to decide what any Admin moderate on this site. 
I once more edit your post : There are no need to quote whole posts above own reply!

It is great that you want to help, but send_pm function is what we tell people to use. It has been present since PHP-Fusion 7.01
Yes it has been in our Wiki a very long time : https://www.php-fusion.co.uk/infusion...page_id=44
We avoid to encurage any teachings regarding modifications of faulty implementations., esp under our guides. 
You can think whatever you want of that. But we like to stick to the standards for ease of upgrade etc etc!.
 
This is a lot to take in. So is there a standard to upgrade just the messages table from v7 to v9? I don't even know what an sql call back is. 
Thread Information
Author
Replies
9 posts
Views
321 times
Last Post
Last updated on 13 days ago
You can view all discussion threads in this forum.
You cannot set up a bounty in this discussion thread.
You can start a new discussion thread in this forum.
You cannot reply in this discussion thread.
You cannot start on a poll in this forum.
You cannot upload attachments in this forum.
You can download attachments in this forum.
You cannot up or down-vote on the post in this discussion thread.
Users who participated in discussion: Falk, Chan, Cupid, douwe_yntema