WHERE LIKE Issues

[shanejeffery86]

Hey all.

 

Let me give you the code first before I start explaining:

 

$query = "SELECT phpfox_user.id, phpfox_user.type, phpfox_user.user, phpfox_user.email, phpfox_user.reg_id, 
phpfox_user.fullname, phpfox_conference_passes.pass_name FROM phpfox_user 
LEFT JOIN phpfox_user_passes ON (phpfox_user.user = phpfox_user_passes.user)
LEFT JOIN phpfox_conference_passes ON (phpfox_user_passes.conference_id = phpfox_conference_passes.conference_id) 
AND (phpfox_user_passes.pass_id = phpfox_conference_passes.pass_id) WHERE phpfox_user.email LIKE '%$search_email%' 
AND phpfox_user.fullname LIKE '%$search_fullname%' AND phpfox_user.reg_id=$search_regid 
AND phpfox_conference_passes.pass_name LIKE '%$search_passtype%' 
ORDER BY phpfox_user.email";

$user_count = mysql_query($query);

 

Here is what is happening --

 

Whenever the $search_regid has a value, the query executes fine.  Also, the $search_regid cannot have a LIKE in the query because it pulls back the wrong results. 

 

Now, whenever $search_fullname or $search_email or $search_passtype have values (the proper values to return query results mind you), the query does not return anything and instead errors out.

 

Error:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND phpfox_conference_passes.pass_name LIKE '%One Reg%' ORDER BY phpfox_use' at line 7

 

Any ideas what is wrong with my query?

 

Thanks!

 

[shanejeffery86]

Nevermind.  I figured it out.

 

The $search_regid must have a LIKE in order for the other conditions to work.

 

If there is a way around this, please let me know.  Still screws up my results.

[Barand]

WHERE conditions don't work too well when they apply to the right table of a LEFT JOIN. They need to be part of the join condition.

 

eg

SELECT A.blah, B.foo, B.bar
FROM A
LEFT JOIN B ON A.id = B.aID
WHERE A.blah = 'avalue'  AND B.foo = 'something'

 

should be

SELECT A.blah, B.foo, B.bar
FROM A
LEFT JOIN B ON A.id = B.aID AND B.foo = 'something'
WHERE  A.blah = 'avalue'