|
M
mickna author
This is a great program and a great support and community. I feel I should also gave a little bit back:
I think I have read a request in the forum about a private messages system.
As I also need this for a page I set up one today. It is very rough and may only a "outline". But maybe it is a good starting point?
Please be aware: I am not a programmer and I still play with PHPRunner since a few days (this means also with MySQL and PHP). So if my code is not a nice one, excuse me (BTW: also for my English....)
We need at least two tables:
- a User Table with an ID (of course we need users to send PMs)
- Our Table which holds the Messages
Table users
userID <- Primary key, Auto-increment
userName
CREATE TABLE `users` (
`userID` int(11) NOT NULL AUTO_INCREMENT,
`userName` varchar(50) DEFAULT NULL,
PRIMARY KEY (`userID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Table privatemessages
ID <- Primary key, Auto-increment
to_id <- stores the recipient ID (UserID)
from_id <- stores the sender ID (UserID)
dateSent
subject
message
opened <- for later use. I have not implement this yet, but it should be still useful to have it
recipientDelete
senderDelete
CREATE TABLE `privatemessages` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`to_id` int(11) NOT NULL,
`from_id` int(11) NOT NULL,
`dateSent` date DEFAULT NULL,
`subject` varchar(255) NOT NULL,
`message` text NOT NULL,
`opened` tinyint(4) DEFAULT NULL,
`recipientDelete` tinyint(4) DEFAULT NULL,
`senderDelete` tinyint(4) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
PHPRunner Datasource tables (Tables):
- Add a Custom view from table privatemessages -> Name privateMessagesRead
- Add a second Custom view from table privatemessages -> Name privateMessagesSend
- uncheck your original Table privatemessages, as we only use the both costum views
- click and drag userID to privatMessagesRead to_id (= Table link property userID -> to_id)
- click and drag userID to privatMessagesSend from_id (= Table link property userID -> fromid)
PHPRunner Querys:_
Nothing to do <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=question&id=17085&image=3&table=forumtopics' class='bbcemoticon' alt=':)' />
PHPRunner Pages:
privateMessagesRead
Check only "List page", "Delete record", "View record", "Printer-friendly", "Export page"
Show in Popup "Add page", "View page"
privateMessagesSend
Check only "List page", "Add new", "Delete record", "View record", "Printer-friendly", "Export page"
Show in Popup "Add page", "View page"
PHPRunner Fields:
See images
PrivateMessagesRead
PrivateMessagesSend
PHPRunner Totals:_
privateMessagesRead
from_id
dateSent
subject
message
privateMessagesSend
toid
subject
message
PHPRunner Security:
I use dynamic security (if you do also, do not forget to login as admin in your page and check the permissions of the two views!)
So under Advanced Settings I have "User can see and edit theier own data only" for both tables.
PHPRunner Events:_
We do not want to simply delete the messages. We have to take care, if the recipient delete the message AND the sender. If only one delete the message it should still viewable to the other onew.....
privateMessagesRead - List page
Before record deleted
//set recipientDelete to 1
global $conn;
$str = "UPDATE `privatemessages` SET `recipientDelete` = '1' WHERE ".$where;
db_exec($str,$conn);
//Now we take a look, if also senderDelete was set to 1 and if so, we can delete the message.
$str = "SELECT `senderDelete` FROM `privatemessages` WHERE ".$where;
$rs = db_query($str,$conn);
$data = db_fetch_array($rs);
if ($data["senderDelete"]) {
$strdelete = "DELETE FROM `privatemessages` WHERE ".$where;
List page: Before SQL query
//show only messages which are not set as deleted
$strWhereClause = whereAdd($strWhereClause, "recipientDelete = 0" );
db_exec($strdelete,$conn);
}
return false;
---
privateMessagesSend - List page
Before record deleted
//set senderDelete to 1
global $conn;
$str = "UPDATE `privatemessages` SET `senderDelete` = '1' WHERE ".$where;
db_exec($str,$conn);
//Now we take a look, if also recipientDelete was set to 1 and if so, we can delete the message.
$str = "SELECT `recipientDelete` FROM `privatemessages` WHERE ".$where;
$rs = db_query($str,$conn);
$data = db_fetch_array($rs);
if ($data["recipientDelete"]) {
$strdelete = "DELETE FROM `privatemessages` WHERE ".$where;
db_exec($strdelete,$conn);
}
return false;
List page: Before SQL query
//show only messages which are not set as deleted
$strWhereClause = whereAdd($strWhereClause, "senderDelete = 0" );
privateMessagesSend - Add page
Before record added
// Insert Date and set open and delete to 0
$values["dateSent"] = now();
$values["opened"] = 0;
$values["recipientDelete"] = 0;
$values["senderDelete"] = 0;
return true;
Ok, time to get coffee <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=question&id=17085&image=14&table=forumtopics' class='bbc_emoticon' alt=':)' />
As I said, it is a rough outline and you have to tweak it.
I also hope all makes sense <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=question&id=17085&image=15&table=forumtopics' class='bbc_emoticon' alt=':)' />
Cheers,
mickna
|
|