[SOLVED] 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]")"

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?

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

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: 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?

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.


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.


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.

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.

Code:

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.

Code:

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.

Code:

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 is working!