Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 1 of 1
  1. #1
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,072
    Thanks
    26
    Thanked 0 Times in 0 Posts

    Problem updating Form with UNION Query

    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
    Last edited by doubledee; 02-05-2013 at 01:46 AM.


 

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •