searching

I have this search and it is working, but now I need to add one more thing to it and I just can't get it to work.

I need to add code to prevent a user showing if they are in the 'blockUser' table

I thought I could add that to the bottom of the $wheres but it doesn't work. Tried some different things as well.

this is the code I was trying to work with::
if (!isset($blockID)) {
$where .= " AND blockUser.blockID = user.userID";}


my search code::
$z = new zipcode_class;
$zips = $z->get_zips_in_range($zip_code, $miles, _ZIPS_SORT_BY_DISTANCE_ASC, true);
$zips_in_range = implode(',', array_keys($zips) );

$where = "WHERE zip IN (". $zips_in_range .") AND user.bd_year <= $year1 AND user.bd_year >= $year2 AND user.gender = $genderPref AND user.genderPref = $gender AND user.exp_date >= CURDATE() ";
if (!empty($smoke)) {
$where .= " AND about_me.smoking = $smoke"; }
if (!empty($religion)) {
$where .= " AND bkgd.relg = $religion"; }
if (!empty($heightMin)) {
$where .= " AND appearance.height >= $heightMin"; }
if (!empty($heightMax)) {
$where .= " AND appearance.height <= $heightMax"; }
if (!empty($hairColor)) {
$where .= " AND appearance.hair_color = $hairColor"; }
if (!empty($eyeColor)) {
$where .= " AND appearance.eye_color = $eyeColor"; }

$result = mysql_query("SELECT user.userID, user.gender, user.genderPref, user.city, user.state, photos.photo_1,
CURDATE(), (YEAR(CURDATE())-YEAR(birth_date)) - (RIGHT(CURDATE(),5)<RIGHT(birth_date,5)) AS age
FROM user
LEFT JOIN photos
ON user.userID = photos.userID
LEFT JOIN about_me
ON user.userID = about_me.userID
LEFT JOIN bkgd
ON user.userID = bkgd.userID
LEFT JOIN appearance
ON user.userID = appearance.userID
LEFT JOIN blockUser
ON user.userID = blockUser.blockID
$where
LIMIT $offset, $rowsperpage")or die(mysql_error());

I am not sure if it is a simple issue or if I am way off, could use some help

When you use a WHERE clause that is conditional upon a *dependent* table (e.g, the right side table in a LEFT JOIN), you convert that LEFT JOIN to an INNER JOIN, thus effectively destroying all your join logic.

Here's an old post of mine that explains why:
http://www.codingforums.com/showthread.php?p=818192#post818192

And not to ask a dumb question, but why do you need that part of the WHERE, in any case???

It's identical to the ON condition:

if (!isset($blockID)) {
$where .= " AND blockUser.blockID = user.userID";}

versus

LEFT JOIN blockUser
ON user.userID = blockUser.blockID


Anyway, if you really intended those LEFT JOINs to *BE* left joins, then you need to completely reorganize that query.

Instead of having

...
LEFT JOIN appearance
ON user.userID = appearance.userID
...
WHERE ...
AND appearance.height >= $heightMin
...
(just to pick *one* example), you need to have

...
LEFT JOIN appearance
ON user.userID = appearance.userID
AND appearance.height >= $heightMin
...

so that the condition becomes part of the ON.

I see what your saying and that works for everything but my blockUser table.

I modified my code and am trying to use NOT IN. I can get it to work in a simple query but not my larger one, can anyone help me out with that??

Works fine here
$result = mysql_query("
SELECT user.userID
FROM user
WHERE userID NOT IN (SELECT blockUser.blockID FROM blockUser WHERE blockUser.blockUserID = '$clientID')")


But not here

$result = mysql_query("SELECT user.userID, user.gender, user.genderPref, user.city, user.state, photos.photo_1,
CURDATE(), (YEAR(CURDATE())-YEAR(birth_date)) - (RIGHT(CURDATE(),5)<RIGHT(birth_date,5)) AS age
FROM user

LEFT JOIN photos
ON user.userID = photos.userID

LEFT JOIN about_me
ON user.userID = about_me.userID
AND about_me.smoking = $smoke

LEFT JOIN bkgd
ON user.userID = bkgd.userID
AND bkgd.relg = $religion

LEFT JOIN appearance
ON user.userID = appearance.userID
AND appearance.height >= $heightMin
AND appearance.height <= $heightMax
AND appearance.eye_color = $eyeColor

WHERE zip IN (". $zips_in_range .") AND user.bd_year <= $year1 AND user.bd_year >= $year2 AND user.gender = $genderPref AND user.genderPref = $gender AND user.exp_date >= CURDATE() AND user.userID NOT IN (SELECT blockUser.blockID FROM blockUser WHERE blockUser.blockUserID = '$clientID')

LIMIT $offset, $rowsperpage")or die(mysql_error());