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?!


SELECT requestor, requestee, username, photo_name, photo_label, photo_approved, requestor_approved, requestee_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, requestee, username, photo_name, photo_label, photo_approved, requestor_approved, requestee_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

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

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

the goodness or badness of a strategy can easily be ascertained by a test

what happened when you tested it?

:slight_smile:

Re-read my original post, and you’ll find the answer… :wink:

Debbie

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…