Simple Message System

[napsterdj]

Hello All,

 

I'm new to PHP and with databases.

I'm actually trying to implement a Message system which has Inbox , Sent Box and an option for the user to delete the messages.

The condition is , if one user deletes the message from inbox it still needs to be present in the other user's sentbox.

 

The tables I have created so far are :

 

CREATE TABLE MESSAGE

(

MID VARCHAR(30) NOT NULL,

FROM_EMAIL VARCHAR(30) NOT NULL,

TO_EMAIL VARCHAR(30) NOT NULL,

DATE DATE,

SUBJECT VARCHAR(30) NOT NULL,

BODY VARCHAR(1000) ,

PRIMARY KEY(MID),

FOREIGN KEY(FROM_EMAIL) REFERENCES USER(EMAIL),

FOREIGN KEY(TO_EMAIL) REFERENCES USER(EMAIL)

)ENGINE=INNODB;

 

 

CREATE TABLE SENDMSG

(

FROM_EMAIL VARCHAR(30) NOT NULL,

TO_EMAIL VARCHAR(30) NOT NULL.

MID VARCHAR(30) NOT NULL,

FOREIGN KEY(FROM_EMAIL) REFERENCES USER(EMAIL),

FOREIGN KEY(TO_EMAIL) REFERENCES USER(EMAIL),

FOREIGN KEY(MID) REFERENCES MESSAGE(MID)

)ENGINE=INNODB;

 

 

CREATE TABLE RECMSG

(

FROM_EMAIL VARCHAR(30) NOT NULL,

TO_EMAIL VARCHAR(30) NOT NULL.

MID VARCHAR(30) NOT NULL,

FOREIGN KEY(FROM_EMAIL) REFERENCES USER(EMAIL),

FOREIGN KEY(TO_EMAIL) REFERENCES USER(EMAIL),

FOREIGN KEY(MID) REFERENCES MESSAGE(MID)

)ENGINE=INNODB;

 

 

I get an error message while creating the SENDMSG and the RECMSG tables.

I want the MID to be autoincremented and not displayed to the user. How do i do that ?

Im not sure if i am doing this correctly. And also I'm using Dreamweaver to implement this . Any help in rectifying the tables , usage of the correct query's or even using Dreamweaver to implement the above or just the code if you wanna share is greatly appreciated.

 

I've been breaking my head for the past 2 days and I havent been able to figure this out.

 

Thanks

 

[AbydosGater]

Hi napsterdj,

 

Perhaps you could take another approach to your database table design, you could do it with only using one table.

 

table_messages

MID    |    FromEmail    |    ToEmail    |    Body    |    ShowInInbox    |    ShowInSentBox

 

Using the same design idea as you already have, just include an extra two columns in your database table, one for if the messages should show in the Inbox of the ToEmail user (ShowInInbox) and one for if the message should show in the Sent Box of the "FromEmail" user.

 

So if I sent you a message, you insert it into the database with both ShowInInbox and ShowInSentBox set to 1, but then you read and delete the message from your inbox, so when it is deleted set the ShowInInbox to 0 for the MID.

 

Then all you have to do is add an IF statement to your PHP for the inbox and sentbox code that only displays messages that should be in the related inbox/sent box.

 

Does that make sense?

 

Andy

[plznty]

ID - member id

TO - email to

FROM - email from

BODY - content

STATUS - 0/1.

if STATUS = 1 then show in both peoples inbox/sentbox.

if STATUS = 0 then just show in sent box.

 

I'de recommend that if you didn't want to allow people to delete items from their sent box.

Otherwise go with the option above.