[SOLVED] List Filter Problem

This could be my syntax, but I've looked at it over and over and don't see anything wrong with it. And counted parenthesis over and over too. But I can't get it to filter this particular user list using the advanced filter. I'm using this query...

cb_usertype = 'Looking for Ride' AND ((registerDate >= (NOW() - 5) AND cb_departuredate = '') OR (cb_departuredate >= NOW() OR (cb_departuredate <= NOW() AND cb_todate >= NOW())))

cb_departuredate, is of field type 'Dates' and registerDate is of field type 'Datetime' (can't change that type). Is one of these maybe not in the same format as NOW() ?

Here's what I'm looking for in human terms, by the way, in case it helps...

IF user is looking for ride AND (user registered less than 5 days ago AND user entered nothing for a first departure date) OR (user entered a first day of departure of today or later OR (user entered a first day of departure of earlier than today AND user entered a last day of departure of today or later))

Try >= (CURDATE() - INTERVAL 5 DAY) or NOW instead of CURDATE

Couple ways you can do it. Inline or through functions. Personally I prefer functions since it's cleaner to read. The below should work fine.

Please see the below MYSQL documentation for further date usage.

dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html

Thanks, Kyle. I'll read the date documentation, but on first run I'm getting an error from the 'functions' method...

1064 - 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 '' at line 8 SQL=SELECT COUNT( DISTINCT u.id ) FROM jos_users u JOIN jos_user_usergroup_map g ON g.`user_id` = u.`id` JOIN jos_comprofiler ue ON ue.`id` = u.`id` WHERE u.block = 0 AND ue.approved = 1 AND ue.confirmed = 1 AND ue.banned = 0 AND g.group_id IN (2) AND ( ue.`cb_usertype` = 'Looking for Ride' AND ( ( ( u.`registerDate` >= DATE_SUB( NOW(), INTERVAL 5 DAY ) ) AND ( ( ue.`cb_departuredate` = '' ) OR ( ue.`cb_departuredate` IS NULL ) ) ) OR ( ( ue.`cb_departuredate` >= NOW() ) OR ( ( ue.`cb_departuredate` <= NOW() ) AND ( ue.`cb_todate` >= NOW() ) ) )

I tried INTERVAL both ways you suggested also, Heyai, but still getting the same result. I don't think I mentioned the result I was getting from my method... basically, it acts like there's no filter (except the first condition is working: usertype is 'looking for ride').

Like I said, I'll read through the SQL date documentation via the link you gave me. If it still doesn't work and I don't find an answer here, I guess I have a few other options like finding a Joomla plugin to unpublish users after 5 days of inactivity (not the perfect solution). Or maybe adding an extra 'unpublish after' field to the profile.

Okay... I found one issue. It was with NOW() since it is not only date, but time also. Switching to CURDATE() fixed that issue (thanks, Heyai).

The other problem I have is that they're not showing up if they don't fill out a date, but have registered within 5 days of now. I looked all over the database and can't find the field values (it's not in jos_comprofiler_field_values, by the way) of the date. In my SQL statement I used:

cb_departuredate = ''

But I wanted to see what the database was holding if they didn't enter anything. Is it nothing like my statement or is it '0000-00-00 00:00:00' as I see the last visit 'datetime' value in the DB? YES! That's it. All is working now. '0000-00-00' Seems to work for an empty datetime also. Happy now... Thanks again you guys!

One other thing I found. In order for the date (-5) to work, I had to use 'INTERVAL 5 DAY'. Thanks for that also, Heyai!