To give you an idea of what I'm trying to achieve here, I've set up a very simple mail system where users can "Star" incoming or outgoing mail (To read later or save, etc...). The system is VERY simple and works with a similar table design to below, please note that I've removed some columns which aren't relevant here:
Random Digit
User ID of Recipient
User ID of Sender
Message
If recipient has starred message (0 for no, 1 for yes)
If sender has starred the message (0 for no, 1 for yes)
mail_id | recip_account | sender_account | message | recip_starred | sender_starred
1 5 10 Hello 0 1
2 10 5 A Reply 0 1
3 10 20 Test 1 0
4 15 20 Message 1 1
From the examples above, if we are getting starred messages for user ID 10, the query would show Message ID's 1, 3.
As you can probably work out - I can retrieve starred messages easily if viewing only Received or Sent messages separately. Although, the issue here is I would like to show all "Starred" messages in a single folder for each user, and obviously that requires working out which user has starred the message, or by connecting the recip_account & recip_starred OR sender_account & sender_starred and reference that against a user ID?
Can anybody help me out here, the currently way I am retrieving results is as follows:
$sql2 = "SELECT * FROM `ap_mail_system` WHERE `recip_account` = '$user_id' OR `sender_account` = '$user_id' ORDER BY `sent_date` DESC LIMIT 0, 12";
You just need a bit of boolean logic in your WHERE clause. Try:
SELECT * FROM `ap_mail_system` WHERE (`recip_account` = '$user_id' AND `recip_starred` = 1) OR (`sender_account` = '$user_id' AND `sender_starred` = 1) ORDER BY `sent_date` DESC LIMIT 0, 12