How to solve this problem PHP?

[yegor]

Here is what Im trying to do:

Im building a private messaging system, which is threaded (like in facebook pretty much). Im stuck on the deletion portion.

Both members of the conversation can delete the conversation thread from their inbox, but you obviously cannot delete it from the DB, because it will disappear for the 2nd person as well. So I created 2 columns, sender_del and receiver_del. When the sender of the thread deletes it, sender_del gets set to 1. If receiver deletes it, receiver_del gets set to 1. But then you need to display the list of PMs and not include the PMs which have receiver_del or sender_del set to 1, but the messages are all mixed together, since for some you are the sender, for others the receiver, so you can just say SELECT * FROM pm WHERE receiver_del == 0 OR sender_del == 0

What would be a good solution for this problem?

 

[O[H]-Zone]

Are you assuming the default value in the DB is 0? It isn't; it's NULL. If I'm reading this right (much less than a 100% chance) you'd want to do:

SELECT * FROM pm WHERE receiver_del != 1 OR sender_del != 1

for all visible pm's

SELECT * FROM pm WHERE reciever == <name> AND receiver_del != 1

for the reciever to see his pm's and

SELECT * FROM pm WHERE sender == <name> AND sender_del != 0

for the sender to see his.

 

[yegor]

The last 2 queries have to be put together, because each receiver, is a potential sender as well, so you need 1 query that will get the PM list for both people.

 

[Arainach]

You just said that you wanted a threaded PM system. So why wouldn't you have a table for threads with the user1del and user2del columns and a "threadID" column in the PM table?

 

[Sgraffite]

Why not have a table that links the user to the thread? If they delete the thread, remove that link for them.

 

[yegor]

I actually got a better idea.

Just have 1 column "delete" or something. If one of the people in the convo deletes the PM, their userid goes into the field. The select query that lists the PMs will just say WHERE delete != '$userid'. When the 2nd person in the convo deletes the convo, it checks to see if delete is set to 0, if its not, then it drops the record from the DB.

 

[Sgraffite]

I actually got a better idea.

Just have 1 column "delete" or something. If one of the people in the convo deletes the PM, their userid goes into the field. The select query that lists the PMs will just say WHERE delete != '$userid'. When the 2nd person in the convo deletes the convo, it checks to see if delete is set to 0, if its not, then it drops the record from the DB.

Just something to think about, is it possible to have 3+ people in a conversation?

 

[yegor]

Nope, its just 2 people, which is why it works.

 

[jmackay]

Nope, its just 2 people, which is why it works.

Couldn't you have a counter somewhere if you did want it to be sent to more people and when it hits 0 you completely delete.

Eg:

Message is sent to Bob, James, and Sally (counter is 3)
Bob reads and deletes, (C = 2)
James reads and doesn't delete (C = 2)
Sally reads and deletes (C = 1)
James goes to remove old stuff a month later and deletes it (C = 0, script knows to delete from db)

This would also allow you to forward messages, just add to the counter instead of deleting (unless the forward would be a new message )

 

[yegor]

Thats highly unnecessary, since there is no need to forward messages or have more than 2 participants. But thanks for the suggestion

 

[Krazie]

Why not have a table that links the user to the thread? If they delete the thread, remove that link for them.

I think that would probably be your easiest way to do this. Basically have an id column with auto increment for each PM sent. Have both users names in the table. Then on the two peoples accounts have it show that id. If one deletes it have it not show. If the other deletes drop it. Sounds the easiest way to do it