query help.. possible join? [solved]

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

query help.. possible join? [solved]

Post 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?
Last edited by s.dot on Wed Nov 22, 2006 3:35 pm, edited 1 time in total.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post 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)
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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? :wink: :P
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

4.1 added subquery support. So yes, there's plenty wrong. ;)
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

easily fixed :D

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.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
Post Reply