CODEWITHSUNDEEP
mysql
PwnageAtPwn
PwnageAtPwn

Reputation: 431

LEFT JOIN breaks WHERE Clause

I've recently been required to input more information from my database and I've just LEFT JOIN to help me, it works almost perfectly(it does actually get the right field from the other table) but my WHERE clause is nullified giving the user access to both tables without the restriction of my where clause.

MySQL doesn't crap out any errors, so I'm assuming it's something to do with my where clause or something happened in the join.

SELECT * FROM students 
LEFT JOIN courses ON students.appliedforCourse = courses.idNumber 
WHERE 
    students.telephone LIKE '%$var' 
    OR students.email LIKE '%$var' 
    OR students.address like'%$var%' 
    OR (CONCAT(students.firstName,' ',students.lastName) LIKE '%$var%')
    AND addedBy ='$userid'
LIMIT $s,limit

Upvotes: 1

Views: 279

Answers (2)

arturro
arturro

Reputation: 1606

I suspect that simply you did not set $var value. Then condition e.g. students.telephone LIKE '%$var' will become students.telephone LIKE '%' (always true for not null address), which will match every record of the join , exactly what you are getting.

Upvotes: 0

mkk
mkk

Reputation: 7693

The query itself is correct (although really inefficient due to ORs and % % [ indexes will not be used] ).
I would suggest to echo the query, are you sure that $var is evaluated correctly ? Try to run the query directly in mysql (via phpmyadmin for example or using console).

Upvotes: 5

Related Questions