MySQL INNER join depending if php variable equal to a field

Question

I am struggling with an SQL query on the inner join. I want to have it so it joins on the field not the same as the $user_id. Hope that made sense, this might be easier to understand:

if ($user_id = wallfriends.mem_id_from) {
INNER JOIN wallfriends ON wallusers.mem_id = wallfriends.mem_id_to
} else if ($user_id = wallfriends.mem_id_to) {
INNER JOIN wallfriends ON wallusers.mem_id = wallfriends.mem_id_from
}

My Current query:

SELECT * FROM wallusers 
INNER JOIN wallfriends ON wallusers.mem_id = wallfriends.mem_id_from
WHERE ((wallfriends.mem_id_from =".$user_id.") OR (wallfriends.mem_id_to =".$user_id.")) AND wallfriends.confirm=1

Thanks in advance

Mickle Foretic · Accepted Answer · 2012-11-29 14:37:45Z

3

I think what you need is a putting a ternary comparison on the sql string

$sql = " SELECT * FROM wallusers 
INNER JOIN wallfriends ON wallusers.mem_id = ".( $user_id == "wallfriends.mem_id_to"?"wallfriends.mem_id_from":"wallfriends.mem_id_to")."
WHERE wallfriends.confirm=1";

answered Nov 29, 2012 at 14:37

Mickle Foretic

1,41914 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

eggyal Over a year ago

Hang on, doesn't this perform the comparison between $user_id and the literal string wallfriends.mem_id_to in PHP, rather than between $user_id and the value of the mem_id_to column in the wallfriends table in SQL (as I had understood the problem to be)?

Mickle Foretic Over a year ago

@eggyal yes, that is what it does. That is what I understood from the question. If you wanted to compare the string directly to the table and not use its content to decide on what to join, you could do INNER JOIN (($user_id = wallfriends.mem_id_to AND wallusers.mem_id = wallfriends.mem_id_from) OR ($user_id = wallfriends.mem_id_from AND wallusers.mem_id = wallfriends.mem_id_to)) ;)

Sashi Kant · Accepted Answer · 2012-11-29 14:29:48Z

1

Try this ::

SELECT * 
FROM wallusers 
INNER JOIN wallfriends ON wallusers.mem_id = wallfriends.mem_id_from
WHERE 
(wallfriends.mem_id_from =".$user_id.") 
OR 
(wallfriends.mem_id_to =".$user_id.")

answered Nov 29, 2012 at 14:29

Sashi Kant

13.5k9 gold badges50 silver badges71 bronze badges

1 Comment

Codded Over a year ago

I need the inner join to have the where clause. INNER JOIN wallfriends ON wallusers.mem_id = wallfriends.mem_id_from/wallfriends.mem_id_to depending on the $user_id

Nathan · Accepted Answer · 2012-11-29 15:43:03Z

1

SELECT * FROM wallusers 
INNER JOIN wallfriends ON 
wallusers.mem_id = 
CASE WHEN 
wallfriends.mem_id_from =".$user_id." 
THEN wallfriends.mem_id_to 
ELSE wallfriends.mem_id_from
END
WHERE wallfriends.confirm=1

edited Nov 29, 2012 at 15:43

answered Nov 29, 2012 at 14:35

Nathan

2,77524 silver badges28 bronze badges

3 Comments

Codded Over a year ago

this looks promising, although im getting an error right syntax to use near 'WHERE wallfriends.confirm=1 LIMIT 0, 30' at line 8

eggyal Over a year ago

You're missing the END keyword (to terminate the CASE expression) before WHERE.

Nathan Over a year ago

Thanks for the END. I forgot.

Collectives™ on Stack Overflow

MySQL INNER join depending if php variable equal to a field

3 Answers 3

2 Comments

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related