Have this code that pulls the info and puts in a list, it is a very simple list, now I want to expand and add info to it but when I do the last code is what get implemented.
What do I use to make it all work?
This is the very simple code:
PHP Code:
$sql = "SELECT * FROM nudges WHERE sender='$clientID' AND s_status IN (1,0) AND accepted=1
UNION
SELECT * FROM nudges WHERE receiver='$clientID'
ORDER BY posted DESC ";
$result=$mysqli->query($sql);
What I get is:
Sent test - harkly
Received test - fassbender
Received test - theRory
So now I add more details and it will only do the last if statement, I sort of get why but what I can't figure out is what I need to do to fix it
More detailed code:
PHP Code:
$sql = "SELECT * FROM nudges WHERE sender='$clientID' AND s_status IN (1,0) AND accepted=1
UNION
SELECT * FROM nudges WHERE receiver='$clientID'
ORDER BY posted DESC ";
$result=$mysqli->query($sql);
$sql = $mysqli->query("SELECT * FROM nudges WHERE receiver='$clientID' ORDER BY posted DESC");
while($r = $sql->fetch_assoc()) {
$posted = date(" F j",$r[posted]);
$id=$r['id'];
$sender=$r['sender'];
$receiver=$r['receiver'];
$r_status=$r['r_status'];
$s_status=$r['s_status'];
$accepted=$r['accepted'];
$result = $mysqli->query("SELECT city, state, birth_date,
CURDATE(),
(YEAR(CURDATE())-YEAR(birth_date)) - (RIGHT(CURDATE(),5)<RIGHT(birth_date,5))
AS age FROM user WHERE userID = '$sender'");
while ($r=$result->fetch_assoc()) {
$city = $r['city'];
$sql = $mysqli->query("SELECT * FROM nudges WHERE sender='$clientID' ORDER BY posted DESC");
while($r = $sql->fetch_assoc()) {
$posted = date(" F j",$r[posted]);
$id=$r['id'];
$sender=$r['sender'];
$receiver=$r['receiver'];
$result = $mysqli->query("SELECT city, state, birth_date,
CURDATE(),
(YEAR(CURDATE())-YEAR(birth_date)) - (RIGHT(CURDATE(),5)<RIGHT(birth_date,5))
AS age FROM user WHERE userID = '$receiver'");
while ($r=$result->fetch_assoc()) {
$age = $r['age'];
$city = $r['city'];
echo "<br>Sent $sender - $receiver $city";
}
}
}
} // END while
What I get from this is:
Sent test - harkly Winston Salem
Sent test - charlieMain Winston Salem
I'm not sure a union is what you want to do here (unions do not consider relations). And you definitely don't want to stack up the queries within a while, so you need to join. What I'm not sure of yet is if you want to use a union of joins or if you can get away with just joins.
What is the overall picture you have here? Are you simply wanting to take the city, state, birth_date, and age for each user and associate the sender and receiver?
If so, to me this appears what you want to do is:
Code:
SELECT s.city AS sender_city, s.state AS sender_state, s.birth_date AS sender_birth_date, YEAR(CURDATE()) - YEAR(s.birth_date) AS sender_age, r.city AS receiver_city, r.state AS receiver_state, r.birth_date AS receiver_birth_date, YEAR(CURDATE()) - YEAR(r.birth_date) AS receiver_age, n.sender, n.receiver, n.r_status, n.s_status, n.accepted, n.id
FROM nudges n
INNER JOIN user s ON s.userID = n.sender
INNER JOIN user r ON r.userID = n.receiver
WHERE n.sender = '$clientID' AND n.s_status IN (0, 1) AND n.accepted = 1
ORDER BY n.posted ASC
Where it gathers the city, state, birth_date and age of the sending user, as well as the associated sender, reciever, r_status, s_status, accepted and id from the nudges table.
When you run that query does it give you all the data you expect to receive?
I am using the Union to pull out users based on the 2 different credentials
I need to pull out all users where
PHP Code:
WHERE sender='$clientID' AND s_status IN (1,0) AND accepted=1
and
PHP Code:
sender='$clientID' AND s_status IN (1,0) AND accepted=1
table layout:
nudge
ID sender receiver posted s_status r_status accepted
Then I want to pull their info from another table (users)
I am not exactly sure what is going on with the SELECT you did, not familiar with that layout - still using the simple versions
I know that this "s.city AS sender_city" is changing the name to sender_city but how does it know what s.city is? I know this is probably a stupid question
The condition you have only applies to the first query in the union. The second query doesn't have that condition. You can use a select. . . (select . . . union. . . select). . . where query to take the where condition result of the two select statement's unioned together. Not really ideal since you need to iterate the entire recordset.
Easy enough to add that refinement to the where clause of a join to put both sender and receiver. The 's' comes from the inner join on the user table: inner join user s is identical to inner join user AS s, so the s is now the aliased name to the table user. Again, a join is performed on user AS r, so that aliases the user to r. The s and r were chosen to represent "sender" and "receiver" based on their ON conditions. Since this table joins user table into it twice, you'll need to provide aliasing to determine which table it draws its results as well as which table and fields it uses for its where clauses. Without an alias it would likely trigger an ambiguous error.
SELECT
s.city AS sender_city,
s.state AS sender_state,
r.city AS receiver_city,
n.sender,
n.receiver,
n.r_status,
n.s_status,
n.accepted,
n.posted,
n.id
FROM nudges n
INNER JOIN user s ON s.userID = n.sender
INNER JOIN user r ON r.userID = n.receiver
WHERE (s.userID='$clientID' AND n.s_status IN (1,0) AND n.accepted=1) OR r.userID='$clientID'
Will return where the receiver is clientID OR (sender is cliendID AND statis is 1 or 0 AND accepted is 1 [which I added since you didn't have a condition for it]).
Before you post, read our: 
