SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,762
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Problem updating data with UNION Query

    Is it more trouble than it is worth when you combine two separate queries together, and then you have to update things?

    I was having a productive weekend finishing up my Friend-Request module, when I noticed this scenario...

    Originally I had just the top query which finds all Friend-Requests which "I" declined (as the "Requestee"), but then it occurred to me that I could send someone a Friend-Request and then decline the initiated request (as the "Requestor"), so I added the second query.

    Creating the UNION query below seemed easy enough for *displaying* the data, but it has turned into a real nightmare with my PHP and Form when it comes to making things updatable?!

    PHP Code:
    SELECT requestorrequesteeusernamephoto_namephoto_labelphoto_approvedrequestor_approvedrequestee_approved
    FROM friend 
    AS f
    INNER JOIN member 
    AS m ON f.requestor m.id
    WHERE requestee 
    =19
    AND requestor_approved =1
    AND requestee_approved =2
    UNION
    SELECT requestor
    requesteeusernamephoto_namephoto_labelphoto_approvedrequestor_approvedrequestee_approved
    FROM friend 
    AS f
    INNER JOIN member 
    AS m ON f.requestee m.id
    WHERE requestor 
    =19
    AND requestor_approved =2
    ORDER BY username 

    The problem is that all of my PHP code is just looking for a "RequestorID" to update things, but in cases when I sent a Friend-Request to someone and then decided to retract it, I need to work with the "RequesteeID".

    I thought I figured things out by creating aliases and rolling the "RequestorID" and "RequesteeID" into a field called "memberID", and "requestor_approved" and "requestee_approved" into "approved".

    The problem is my PHP code doesn't know what to do with a generic "memberID" since it needs to stick it in either a "RequestorID" or "RequesteeID".

    Since this is a MySQL forum, I guess I am wondering if I should surrender, and just create Two Pages, one displaying "Friend-Requests from Others that I Declined" and then a second page displaying "Friend-Requests from Me that I Declined"?

    Hope that makes sense?!

    Thanks,



    Debbie

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    i don't see the logic of a showing a page which lists "Friend-Requests from Me that I Declined"

    that's almost as goofy as a "Friend-Requests from Others to Me that They Declined" page

    you're asking questions about what an application's user interface should or shouldn't show a user, and that's not really a mysql topic, is it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,762
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i don't see the logic of a showing a page which lists "Friend-Requests from Me that I Declined"
    If you understood the way my table is laid out - which is somewhat in the SQL I posted - you'd see that that includes "Friendships that I Ended".


    that's almost as goofy as a "Friend-Requests from Others to Me that They Declined" page
    Why is it "goofy" to know who I declined Friend-Requests from? (That is pretty similar to viewing an "Ignore List"...)


    you're asking questions about what an application's user interface should or shouldn't show a user, and that's not really a mysql topic, is it
    No, I asked if combining two different data sets using a UNION and then wanting to update it was a bad strategy....



    Debbie

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    No, I asked if combining two different data sets using a UNION and then wanting to update it was a bad strategy....
    the goodness or badness of a strategy can easily be ascertained by a test

    what happened when you tested it?


  5. #5
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,762
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    the goodness or badness of a strategy can easily be ascertained by a test

    what happened when you tested it?

    Re-read my original post, and you'll find the answer...


    Debbie

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    Re-read my original post, and you'll find the answer...
    try approaching the problem from the point of view of running the UPDATE statement outside of php
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,762
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    try approaching the problem from the point of view of running the UPDATE statement outside of php
    I got it figured out. (Only took about 1,000 lines of code?!)

    I ended up having to use UNION queries for each of my 3 views, and I needed to break up my UPDATE query into 2 separate queries, using one depending on what the Requestor and Requestee were doing.

    This was one hell of a drill in SQL, PHP and Logic!!!

    Thanks,


    Debbie

    P.S. This would make a nice case study if there was anyone around who cared enough to discuss it at length...


Bookmarks

Posting Permissions

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