(Mysql) How to mark different WHERE OR rows?

For example i have requested:

WHERE (friend_id=? OR client_id=?)

How do i know which row meets friend_id condition开发者_开发技巧 and which meets client_id condition?

Is it possible to mark/flag rows depending of meeting condition somehow?

Thanks.

SELECT friend_id=? FROM yourtable WHERE (friend_id=? OR client_id=?);

You will get a true if the friend_id clause matches: Or even:

SELECT friend_id=?, client_id=? FROM yourtable WHERE (friend_id=? OR client_id=?);

To get both matches. In this way you can see if one or both matches.

Use CASE operator

If knowing which row was hit because by any condition you can of course add this data to your result columns using case operators. The only downside is that your variables in your prepared statement (if that's what you're having here) are going to be doubled.

You can use UNION.

For example:

SELECT name, 1

FROM friends

WHERE friend_id=?

UNION

SELECT name, 0

FROM friends

WHERE client_id=?

Then when receiving data, you can check for that flag

SELECT *,
       'Friend' AS Source
  FROM TABLE
 WHERE friend_id = ?
UNION
SELECT *,
       'Client' AS Source
  FROM TABLE
 WHERE client_id = ?

But you'll have an issue if your entry is both friend and client. What do you want to happen in this case?

You can use IF(), but then you have to bind one of id twice:

SELECT IF(friend_id=?, 'friend', 'client') AS type
FROM table
WHERE (friend_id=? OR client_id=?)

you can add flags to the query:

SELECT *, IF((friend_id=$friend_id), 1, 0) AS friend_matched, IF((client_id=$client_id), 1, 0) AS client_matched FROM table WHERE (friend_id=? OR client_id=?)