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))
Kyle (Krileon) Community Builder Team Member Before posting on forums:
Read FAQ thoroughly
+
Read our Documentation
+
Search the forums CB links:
Documentation
-
Localization
-
CB Quickstart
-
CB Paid Subscriptions
-
Add-Ons
-
Forge
-- If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
-- If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please
send me a private message
with your thread and will reply when possible!
-- Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
-- My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.
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!
krileon
