1. Forums
  3. Support and Presales
  5. Developer Members Support
  7. [SOLVED] Query date 1 >= date 2 doesn't work

[SOLVED] Query date 1 >= date 2 doesn't work

4 years 9 months ago #313139 by ericmuc
Replied by ericmuc on topic Query date 1 >= date 2 doesn't work

ericmuc wrote:

ericmuc wrote: Thanks, I tried further an this works: SELECT id FROM XXXX_comprofiler WHERE cb_tauschdatum_ab < "[value]"


Unfortunately is doesn't work right:

SELECT id FROM XXXX_comprofiler WHERE cb_tauschdatum_ab < "[value]"
SELECT id FROM acmp6_comprofiler WHERE cb_tauschdatum_ab < UNIX_TIMESTAMP ("[value]")
SELECT id FROM XXXX_comprofiler WHERE UNIX_TIMESTAMP (cb_tauschdatum_ab) < UNIX_TIMESTAMP ("[value]")

All this queries have following results:
from the date in the data bank shown as "2019-07-01" only the year is taking for the validation, so all inputs like 2019-08-01 or 2019-06-01 are wrong, but 2020-xx-xx is right.

What now? That is a basic function and I woud like to use further like this in my installation.


??? Now it works with: "SELECT id FROM XXXX_comprofiler WHERE UNIX_TIMESTAMP (cb_tauschdatum_ab) < UNIX_TIMESTAMP ("[value]")"
gabou.eu

Please Log in to join the conversation.

4 years 9 months ago #313140 by ericmuc
Replied by ericmuc on topic Query date 1 >= date 2 doesn't work

krileon wrote: Yes, the validation should work fine when using inline editing provided by CB Core Fields Ajax.


unfortunately not on my site, do you have any suggestions, to get it work?
gabou.eu

Please Log in to join the conversation.

4 years 9 months ago #313147 by krileon
Replied by krileon on topic Query date 1 >= date 2 doesn't work
Ok, was able to confirm there's an issue with the error handling. Was able to find, fix, and improve the validation code for ajax editing. Appears to be working fine now. Please be sure to update CB Core Fields Ajax to latest build now available. The validation rewrite in CB Query Field came after CB Core Fields Ajax release so it wasn't accounting for that usage; shouldn't have any such issues in the future either due to improved validation code.

forge.joomlapolis.com/issues/7618

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.

Please Log in to join the conversation.

4 years 9 months ago - 4 years 9 months ago #313225 by ericmuc
Replied by ericmuc on topic Query date 1 >= date 2 doesn't work

ericmuc wrote:

ericmuc wrote:

ericmuc wrote: Thanks, I tried further an this works: SELECT id FROM XXXX_comprofiler WHERE cb_tauschdatum_ab < "[value]"


Unfortunately is doesn't work right:

SELECT id FROM XXXX_comprofiler WHERE cb_tauschdatum_ab < "[value]"
SELECT id FROM acmp6_comprofiler WHERE cb_tauschdatum_ab < UNIX_TIMESTAMP ("[value]")
SELECT id FROM XXXX_comprofiler WHERE UNIX_TIMESTAMP (cb_tauschdatum_ab) < UNIX_TIMESTAMP ("[value]")

All this queries have following results:
from the date in the data bank shown as "2019-07-01" only the year is taking for the validation, so all inputs like 2019-08-01 or 2019-06-01 are wrong, but 2020-xx-xx is right.

What now? That is a basic function and I woud like to use further like this in my installation.


??? Now it works with: "SELECT id FROM XXXX_comprofiler WHERE UNIX_TIMESTAMP (cb_tauschdatum_ab) < UNIX_TIMESTAMP ("[value]")"


I have to write again hereto: it still doesn't work. I changed nothing, but really it was working on the time of the last post and now not. I tried really a lot, but without success.

At the moment am using this query:
SELECT id FROM XXXX_comprofiler WHERE UNIX_TIMESTAMP (cb_tauschdatum_ab) < UNIX_TIMESTAMP ("[value]")
and my start date is 2019-10-02 and the first date what gives no error and can be choosen is 2019-02-22. I have no idea what happens here. In the mysql is the field as a date field.

So I am searching for a solution for this.

Or can I use another validation query for date 2 > date 1. I shouldn't be the first user with this, so why it is so problematic? What I have set wrong?
gabou.eu

Please Log in to join the conversation.

4 years 9 months ago #313227 by krileon
Replied by krileon on topic Query date 1 >= date 2 doesn't work
Doing a query validation from Date 2 against Date 1 is not going to work how you're wanting it to. The validation has no idea you changed the value of Date 1 until after you save your profile. So you'll likely only ever see it work as expected after Date 1 has been saved. You also should not need to convert Date 1 (cb_tauschdatum_ab) assuming it's already a Date or Datetime fieldtype. As long as [value] is also a Date or Datetime field it shouldn't need to be converted either. So the below should work fine.

SELECT `id` FROM `#__comprofiler` WHERE `cb_tauschdatum_ab` < '[value]'

Note you currently are not filtering this down to the current user either so I'm not sure if you're intending on it doing that or not, but the below would fix that.

SELECT `id` FROM `#__comprofiler` WHERE `user_id` = '[user_id]' AND `cb_tauschdatum_ab` < '[value]'

Additionally you can probably just use Code validation using CB Code Field and do this with PHP as I don't see any specific logic that would require SQL in your usage. Example as follows with PHP.

return ( strtotime( '[cb_tauschdatum_ab]' ) < strtotime( '[value]' ) );

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.

Please Log in to join the conversation.

4 years 9 months ago #313229 by ericmuc
Replied by ericmuc on topic Query date 1 >= date 2 doesn't work

krileon wrote: Doing a query validation from Date 2 against Date 1 is not going to work how you're wanting it to. The validation has no idea you changed the value of Date 1 until after you save your profile. So you'll likely only ever see it work as expected after Date 1 has been saved. You also should not need to convert Date 1 (cb_tauschdatum_ab) assuming it's already a Date or Datetime fieldtype. As long as [value] is also a Date or Datetime field it shouldn't need to be converted either. So the below should work fine.

SELECT `id` FROM `#__comprofiler` WHERE `cb_tauschdatum_ab` < '[value]'

Note you currently are not filtering this down to the current user either so I'm not sure if you're intending on it doing that or not, but the below would fix that.

SELECT `id` FROM `#__comprofiler` WHERE `user_id` = '[user_id]' AND `cb_tauschdatum_ab` < '[value]'

Additionally you can probably just use Code validation using CB Code Field and do this with PHP as I don't see any specific logic that would require SQL in your usage. Example as follows with PHP.

return ( strtotime( '[cb_tauschdatum_ab]' ) < strtotime( '[value]' ) );


Thanks a lot! Yeah, oh, I missed the user, your are right, sorry for that stupid error.

Now
SELECT `id` FROM `#__comprofiler` WHERE `user_id` = '[user_id]' AND `cb_tauschdatum_ab` < '[value]'
is working!
gabou.eu
The following user(s) said Thank You: nant, krileon

Please Log in to join the conversation.

Moderators: beat, nant, krileon
  1. Forums
  3. Support and Presales
  5. Developer Members Support
  7. [SOLVED] Query date 1 >= date 2 doesn't work
Time to create page: 0.221 seconds

Facebook Twitter LinkedIn

    You are here:  
  1. Home
  2. Forums
  3. Support and Presales
  4. Developer Members Support
  5. [SOLVED] Query date 1 >= date 2 doesn't work

Search

Login / Logout

User Menu