Page 1 of 1

Pruning private messages

Posted: Sun May 28, 2006 4:16 pm
by evilmonkey
Hi everyone,

I have a privae mesasging system that works by storing message information in one table and the actual message in the other. The table that stores information looks like this:

Code: Select all

convo_id   |   user_id    |   inbox    |   sent  | trash
Each of the two users in the conversation has one of these entries. The inbox, sent, and trash take on a value of 1 or 0. Basically, what I want is to delete all entries in this table and all associated entries on my second table when inbox, sent, and trash are set to 0 for both users. My second table tooks like this:

Code: Select all

convo_id   |   message
So if I were to send a message from me (id=1) to you (id=2), the entries would be like this:

Code: Select all

Table 1:
12233 | 1 | 1 | 1 | 0
12233 | 2 | 1 | 0 | 0

Table2:
12233 | 'Hey, what's up?'
I can't figure out how to write a query that would delete that when trash, inbox, and sent are 0 for both users. Help appreciated.

Thanks! :)

Posted: Sun May 28, 2006 7:56 pm
by timvw
You need a trigger... everytime you delete (or update) something you need to check if the count of all the messages where (message_id = 'the id being deleted or udpate' and (inbox = 1 or sent = 1 or trash=1) > 0. As soon as it's 0, you can delete the message...

Imho, it makes more sense to have 3 tables (inbox, sent, trash) which all have a foreign-key to the table2 messages..
This way you can simply use constraints (and/or cascades)

Posted: Sun May 28, 2006 9:34 pm
by evilmonkey
This is my idea, I just have no clue how to finish it.

Code: Select all

$old_msgs = sql_pull("SELECT * FROM `convos` WHERE `user_id`={$_SESSION['id']} AND `trash`=1");
	if (sizeof($old_msgs)>100){
		sql_pull("UPDATE `convos` SET `trash`=0 WHERE `user_id`={$_SESSION['id']} ORDER BY `date` DESC LIMIT ".$old_msgs-100, $db);
		sql_pull("DELETE FROM `convos`, `convos_text` WHERE  "); //<===where?
	}
Thanks. :)