I am doing a Select and my date comparison is not working.

I am comparing the date as such user.exp_date >= curdate()

This is my query,
$where = "WHERE zip IN (". $zips_in_range .") AND user.bd_year <= $year1 AND user.bd_year >= $year2 AND genderPref = $genderPref 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
$where")or die('query 2 failed');


Works fine when I dumb it down

$result = mysql_query("SELECT * FROM user WHERE gender = 2 AND genderPref = 1 AND exp_date >= curdate()")or die(mysql_error());

while ($r=mysql_fetch_array($result)){
$userID=$r["userID"];
$exp_date=$r["exp_date"];

echo "$userID, $exp_date <br>";
}

Can someone see what I am doing wrong? I am not getting an error it is just not eliminating those with the exp_date

You'll get more help by asking this moved to the mySQL forum, as its clearly a mySQL issue. I'll take a look and play around to see if I can find the issue in the meantime. If you echo out your final query, what does it look like?

try to wrap all fields' values with single quotes, ex:

" AND appearance.height >= '$heightMin' "

and
" LEFT JOIN photos ON user.userID = 'photos.userID' "

Actually, its bad form, and can even be problematic, to quote numbers. If its a number, quote it, if its not, don't.

Here is my query

SELECT user.userID, user.gender, user.genderPref, user.city, user.state, photos.photo_1, CURDATE(), (YEAR(CURDATE())-YEAR(birth_date)) - (RIGHT(CURDATE(),5)= 1911 AND user.gender = 1 AND user.genderPref = 2 AND user.exp_date >= curdate()

Actually, its bad form, and can even be problematic, to quote numbers. If its a number, quote it, if its not, don't.

Agree with you, but I don't know the structure of his tables and ID's can contain letters. So try to see if it works or the problem is not a quotes

Here is my query

SELECT user.userID, user.gender, user.genderPref, user.city, user.state, photos.photo_1, CURDATE(), (YEAR(CURDATE())-YEAR(birth_date)) - (RIGHT(CURDATE(),5)= 1911 AND user.gender = 1 AND user.genderPref = 2 AND user.exp_date >= curdate()


Where is the WHERE clause? :)

Where is the WHERE clause? :)

That's all that shows when I use the following

echo "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
$where";


and also when I paste the $where into it

WHERE user.bd_year <= $year1 AND user.bd_year >= $year2 AND user.gender = $genderPref AND user.genderPref = $gender AND user.exp_date >= curdate()

Agree with you, but I don't know the structure of his tables and ID's can contain letters. So try to see if it works or the problem is not a quotes

The IDs can contain letters, but I have the search working. I just added the date requirement and that is the only part that is not working.

It still pulls up data without regard to the exp_date.

Got it to work!