DoomGuyMM2 DoomGuyMM2 - 2 months ago 17
SQL Question

Sql: "Where" and "And"

I want to make a search that you can put the e-mail of the username and it will give you the result.
But for some reason it don't works...

I think this is wrong:

$w_search = "WHERE u.user_name LIKE '%$q%' AND u.user_email LIKE '%$q%'";


Edit:
The complete query is:

SELECT u.user_id, u.user_name, u.user_rank, u.user_email, p.p_name, p.p_profimg FROM u_registered AS u LEFT JOIN u_profile AS p ON u.user_id = p.user_id '.$w_search.' ORDER BY u.user_id


Could you help me?
Thank you.

Answer Source

Your problem is that you have TWO conditions on your where clause what is probably making it to "not work", see you have:

WHERE u.user_name LIKE '%$q%' AND u.user_email LIKE '%$q%'

Which means that the user you are looking for need to have that $q in PART (that is what like is for) of its name (user_name) AND at the same time in part of it's emails. So you if you have a guy that his name is Bart and his email is simpsom@whatever.com your query will never return it because one filter will return true and the other return false.

The correct way is to have one of then depending on what $q is or both with an OR operator so:

WHERE u.user_name LIKE '%$q%' OR u.user_email LIKE '%$q%'

But if this is some kind of test if the name or email is exactly as given in the database you need:

WHERE u.user_name = '$q'

OR

WHERE u.user_email = '$q'