Jump to content

[SOLVED] Complex Query Help


Recommended Posts

I have a mail table:

  `mid` int(11) NOT NULL auto_increment,
  `parentid` int(11) NOT NULL default '0',
  `u_to` int(11) NOT NULL default '0',
  `u_from` int(11) NOT NULL default '0',
  `subject` varchar(100) NOT NULL default '',
  `message` text NOT NULL,
  `status` tinyint(1) NOT NULL default '0',
  `status2` tinyint(1) NOT NULL default '1',
  `date_sent` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`mid`)

INSERT INTO `mail` (`mid`, `parentid`, `u_to`, `u_from`, `subject`, `message`, `status`, `status2`, `date_sent`) 
VALUES (1, 0, 1, 306, 'Hi!', 'This is the first message', 1, 1, '2007-08-10 00:11:44'),
(2, 1, 306, 1, '', 'This is the second', 1, 1, '2007-08-10 00:12:06'),
(3, 2, 1, 306, '', 'This is the third', 1, 1, '2007-08-10 00:12:35');


status is for the u_to, status2 is for u_from

the status codes are 0=unread, 1=read, 2=deleted


What I want to do is make a query that if one user deleted a message, the other user can still view it (if they didn't delete it). So in the exampe above both users read the thread. if you change status on mid #1 to '2' user number 1 shouldn't see it. User #306 should still be able to see it. How would I create this query?


I have this so far:

SELECT * FROM `mail` WHERE (u_to = $userid OR u_from = $userid) AND status != 2 AND parentid = 0 ORDER BY date_sent DESC

but this doesn't show the message to the opposite user that deleted it.


Please let me know if you need any more info.


Link to comment
Share on other sites

Unless i missunderstood, dont you need to check that status is not 2 if its the user is who the message was to, and check status2 if the message was from the user? I think this might be what you are after:


SELECT * FROM `mail` WHERE ((`u_to` = $userid AND `status`!=2) OR (`u_from` = $userid AND `status2` != 2)) AND `parentid` = 0 ORDER BY `date_sent` DESC

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.