Page 1 of 1
query help.. possible join? [solved]
Posted: Wed Nov 22, 2006 3:23 am
by s.dot
When someone sends a private message and it is not read yet, it goes into their outbox. And into the receivers inbox.
2 sample records.
Code: Select all
id | message_id | from_id | to_id | folder_id
---------------------------------------------------
1 | 834 | 1 | 2 | 2
---------------------------------------------------
2 | 834 | 1 | 2 | 1
---------------------------------------------------
In this scenario, the sender has it in folder_id 2... the outbox.
The receiver now has a pending message in folder_id 1... the inbox.
Now, I want to have a "delete all" option for my users to delete all the messages from their outbox. Obviously I would need to delete both records.
I can take care of the first one quite easily with this query.
Code: Select all
DELETE FROM `privatemessages_data` WHERE `from_id` = '$user_logged_in' AND `folder_id` = 2
How can I delete the second record WITHOUT deleting a bunch of other records that don't need to be deleted. The only things the two records have in common are the message_id, from_id, and to_id. So perhaps some kind of join on those?
Posted: Wed Nov 22, 2006 4:29 am
by JayBird
Wouldn't all you have to do is perform another delete like this?
Code: Select all
DELETE FROM `privatemessages_data` WHERE `from_id` = '$user_logged_in' AND `folder_id` = 1
Posted: Wed Nov 22, 2006 4:33 am
by onion2k
Can't you do it with a sub-select? EG:
Code: Select all
delete from privatemessages_data where message_id in (select message_id from privatemessages_data where from_id = $user_logged_in and folder_id = 2)
Posted: Wed Nov 22, 2006 4:48 am
by volka
Maybe it's easier (haven't thought about it much, there might be pitfalls) if you have only one record in your database for unread/outbound messages. From your example
Code: Select all
id | message_id | from_id | to_id | folder_id
---------------------------------------------------
1 | 834 | 1 | 2 | 3
Where folder_id has the meaning: 1-inbox(stored), 2-outbox(stored), 3-pending.
user #1 will see message 834 as unread message in her outbox as long as it has folder_id 3
user #2 will see message 834 as unread message in his inbox as long as it has folder_id 3
Posted: Wed Nov 22, 2006 4:54 am
by s.dot
JayBird wrote:Wouldn't all you have to do is perform another delete like this?
Code: Select all
DELETE FROM `privatemessages_data` WHERE `from_id` = '$user_logged_in' AND `folder_id` = 1
Wouldn't this delete all of the receivers inbox messages from the sender? It won't just delete ones not read yet, it will delete ones already read.
onion2k wrote:Can't you do it with a sub-select? EG:
Code: Select all
delete from privatemessages_data where message_id in (select message_id from privatemessages_data where from_id = $user_logged_in and folder_id = 2)
This looks like the best idea, assuming my MySQL version supports subqueries (not sure). If not, it'd be easy to loop, implode, and delete. I'll have to back up my tables of course (learned that the hard way not too long ago).
volka wrote:Maybe it's easier (haven't thought about it much, there might be pitfalls) if you have only one record in your database for unread/outbound messages. From your example
Code: Select all
id | message_id | from_id | to_id | folder_id
---------------------------------------------------
1 | 834 | 1 | 2 | 3
Where folder_id has the meaning: 1-inbox(stored), 2-outbox(stored), 3-pending.
user #1 will see message 834 as unread message in her outbox as long as it has folder_id 3
user #2 will see message 834 as unread message in his inbox as long as it has folder_id 3
That seems logical, but both inbox & "pending not read" messages are stored with folder_id of 1. I use a "read" field to determine if new ones are in the inbox. Then update that 0 to 1 after it is viewed.
Thanks for all the help guys, I think i'll try Onion's idea.
Posted: Wed Nov 22, 2006 5:22 am
by JayBird
scottayy wrote:JayBird wrote:Wouldn't all you have to do is perform another delete like this?
Code: Select all
DELETE FROM `privatemessages_data` WHERE `from_id` = '$user_logged_in' AND `folder_id` = 1
Wouldn't this delete all of the receivers inbox messages from the sender? It won't just delete ones not read yet, it will delete ones already read.
Yes, that is correct. I didn't realise you had a read/not read status stored somewhere else you see
Posted: Wed Nov 22, 2006 5:29 am
by s.dot
JayBird wrote:scottayy wrote:JayBird wrote:Wouldn't all you have to do is perform another delete like this?
Code: Select all
DELETE FROM `privatemessages_data` WHERE `from_id` = '$user_logged_in' AND `folder_id` = 1
Wouldn't this delete all of the receivers inbox messages from the sender? It won't just delete ones not read yet, it will delete ones already read.
Yes, that is correct. I didn't realise you had a read/not read status stored somewhere else you see
Ah, you got me thinking. I was over-complicating things.
Code: Select all
DELETE FROM `privatemessages_data` WHERE `from_id` = '$user_logged_in AND `folder_id` = 1 AND `read` = 0
Logically thinking, that should work perfectly. Who knows, my brain's been fried all night. Anyone up for fried brains, well done?

Posted: Wed Nov 22, 2006 6:27 am
by volka
scottayy wrote:
volka wrote:Maybe it's easier (haven't thought about it much, there might be pitfalls) if you have only one record in your database for unread/outbound messages. From your example
Code: Select all
id | message_id | from_id | to_id | folder_id
---------------------------------------------------
1 | 834 | 1 | 2 | 3
Where folder_id has the meaning: 1-inbox(stored), 2-outbox(stored), 3-pending.
user #1 will see message 834 as unread message in her outbox as long as it has folder_id 3
user #2 will see message 834 as unread message in his inbox as long as it has folder_id 3
That seems logical, but both inbox & "pending not read" messages are stored with folder_id of 1. I use a "read" field to determine if new ones are in the inbox. Then update that 0 to 1 after it is viewed.
a) could be changed
b) doesn't matter wether you have a the states (1,2,3) or (1a,1b,2) where (1a) is 1+"read"field and (1b) 1+!"read"field
=> no need to split the message entries before the message is read.
Posted: Wed Nov 22, 2006 3:13 pm
by s.dot
Code: Select all
SELECT count( * )
FROM `privatemessages_data`
WHERE `message_id`
IN (
SELECT `message_id`
FROM `privatemessages_data`
WHERE `from_id` =1
AND `folder_id` =2
)
Is there anything wrong with this syntax? Or does my mysql version not support subqueries?
I get this error:
Code: Select all
#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT `message_id` FROM `privatemessages_data` WHERE `from_id`
mysql 4.0.27
Posted: Wed Nov 22, 2006 3:16 pm
by feyd
4.1 added subquery support. So yes, there's plenty wrong.

Posted: Wed Nov 22, 2006 3:35 pm
by s.dot
easily fixed
Code: Select all
$result = select messages;
$container = array();
while(looooop)
{
$container[] = $loop['message_id'];
}
$container = implode(',', $container);
$result = select * where id in($container);
In psuedo-psuedo code. Would be much easier with a subquery, since once again i might run into a max_packet_size problem. I should really upgrade my MySQL.
Posted: Wed Nov 22, 2006 5:15 pm
by feyd
Your original subquery based one could have been written as the inner query alone (where the field selected was the count) as that's all it would actually do. Now, if in your new pseudoquery the IN statement was the same result, then I again don't see the need for a second query on the same table doing the same basic thing.
I'm confused as to what you're trying to do now.