Well, if I have ever gotten in over my head, and felt like crying like a little girl, it has to be now.
This past weekend I thought I had finished a complex script, when I noticed my query results were off somewhat. After trying to get the right data, it appears to me that this is a much more complex problem than I've seen in some time, and I am in some need of some serious help...
Let me try to summarize my problem...
Using mod_rewrites, my "manage-requests" script allows a user to view Friend-Requests as follows:
- Friend-Requests (New)
- Friend-Requests (Declined by Me)
- Friend-Requests (Made by Me)
Each "view" displays a listing of Friend-Requests, and for each row, displays:
- Username
- Member Photos
- Radio Buttons (i.e. Decide Later, Accept, Decline)
The idea is that each "view" shows you Friend-Requests in a certain "state", and in addition, the first two views allow you to select a new state. For example, a "Declined Request" would be marked as "Decline", but you could click the "Accept" radio button, and change the person to a "Friend". Or, a "New Request" would be marked as "Decide Later", but you would probably want to mark it as "Accept" - to become "Friends" - or "Decline" to place it on your "Friend-Requests (Declined by Me)" list.
Also, a "friend" record looks like this...
- requestor_id
- requesterr_id
- requestor_approved
- requestee_approved
1, 19, 1, 2 means "User1" (1) made a Friend-Request with "DoubleDee" (19), and the requestor "Accepted" their own request (1), and the requestee "Declined" (2) the request.
2, 19, 1, 1 means "User2" (2) made a Friend-Request with "DoubleDee" (19) and the requestor "Accepted" their own request (1), and the requestee also "Accepted" (1) the request, so they are now "Friends".
And so on...
------
This weekend, under the part of my code which populates my Form (i.e. "Create 'Declined Incoming-Requests' Dataset"), I had this query...
PHP Code:
$q3 = "SELECT requestor, username, photo_name, photo_label, photo_approved, requestee_approved
FROM friend AS f
INNER JOIN member AS m
ON f.requestor = m.id
WHERE requestee = ?
AND requestor_approved = 1
AND requestee_approved = 2
ORDER BY username";
This was working fine, until I realized that it only shows me Friend-Requests that the Requestor "Accepted" and the Requestee "Declined".
However, what if I was the Requestor, sent a friend-request to some Requestee, and then at some point I decided to "decline" them (e.g. after we were "Friends")?
So I changed my query to this...
PHP Code:
// Build query.
// 1st query: Request by Other, Declined by Me
// 2nd query: Request by Me, Declined by Me
$q3 = "SELECT requestor AS memberID, username, photo_name, photo_label,
photo_approved, requestee_approved AS approved
FROM friend AS f
INNER JOIN member AS m
ON f.requestor = m.id
WHERE requestee = ?
AND requestor_approved = 1
AND requestee_approved = 2
UNION
SELECT requestee AS memberID, username, photo_name, photo_label,
photo_approved, requestor_approved AS approved
FROM friend AS f
INNER JOIN member AS m
ON f.requestee = m.id
WHERE requestor = ?
AND requestor_approved = 2
AND requestee_approved = 1
ORDER BY username";
Because the radio buttons in my Form work on having ONE Key and ONE Value, I had to use aliases in my two queries above to collapse things so the query results work with my Form code...
PHP Code:
<!-- Set Radio Buttons -->
<input id='Requestor" . $memberID . "_1' name='friendRequestDecision["
. $memberID . "]' type='radio' value='0' "
. ((isset($friendRequestDecision[$memberID]) && $friendRequestDecision[$memberID] == '0') ? "checked='checked'" : "")
. $disable . " />
<label for='Requestor" . $memberID . "_1'>Decide Later</label>
<input id='Requestor" . $memberID . "_2' name='friendRequestDecision["
. $memberID . "]' type='radio' value='1' "
. ((isset($friendRequestDecision[$memberID]) && $friendRequestDecision[$memberID] == '1') ? "checked='checked'" : "")
. $disable . " />
<label for='Requestor" . $memberID . "_2'>Accept</label>
<input id='Requestor" . $memberID . "_3' name='friendRequestDecision["
. $memberID . "]' type='radio' value='2' "
. ((isset($friendRequestDecision[$memberID]) && $friendRequestDecision[$memberID] == '2') ? "checked='checked'" : "")
. $disable . " />
<label for='Requestor" . $memberID . "_3'>Decline</label>
In the end, my Form is still displaying the proper Friend-Requests, but when I go to update a record, my code crashes, because it doesn't know if the
memberID is for the
$requestorID or the
$requesteeID if you follow me?!
What would seem to be the easiest would be if I could make my UNION query have both a
requestor_id and a
requestee_id and then I could someone return both values - along with my Radio Button value - when my Form is submitted.
If I could grab a
$requestorID,
$requesteeID, and a
$requesteeApproved value, then my previous code would still work.
Otherwise, I need to figure out a way to convert ambigous results like this into something the rest of my script can recognize...
Code:
memberID username photo_name approved
1 username1 NULL 2
2 username2 NULL 2
Hope my description is making some sense - especially since this script is close to 1,000 lines and not something I can easily post here!!
Thanks,
Debbie
Before you post, read our: 
