[SOLVED] Database join Element | Data - where clause + Solution

[marcq]

Hi,

In one of my forms, I've got a date field element (book_bookingdate) and a database join drop-down element.

The table "fab_boarding_location" table contains also a field called "displaydate", which enable me to display in an enrollment form the content of the drop-down to certain date only (the current date).

In this new form, I would like to display the content of the drop-down not according the current date, but according the booking date, which is contained in one of my form date field element called "book_bookingdate" :

I'm unable to build the Data - where clause of the databasejoin element.

Table of the databasejoin drop-down element is : "fab_boarding_location"
Field of this table which contains the date is : "displaydate"

Table of the form is : "fab_booking"
Field of this table which contains the date to compare with is : "book_bookingdate"

Data - where clause tested without success :

WHERE DATE({thistable}.displaydate)
IN (
SELECT DATE({thistable}.displaydate)
FROM fab_boarding_location, fab_booking
WHERE DATE({thistable}.displaydate) = DATE(fab_booking.book_bookingdate));

WHERE DATE({thistable}.displaydate)
IN (
SELECT DATE(fab_boarding_location.displaydate)
FROM fab_booking, fab_boarding_location
WHERE DATE(fab_boarding_location.displaydate) = DATE(fab_booking.book_bookingdate));

I read already this thread and tried to code something similar :
http://fabrikar.com/forums/index.ph...where-clause-to-limit-dropdown-options.15491/
http://fabrikar.com/forums/index.php?wiki/database-join-element/

What is wrong ? Thank you in advance for your support.

Cheers,

Marc

 

[coullet]

Not sure what you want to do but, i think you need to use placeholder:

WHERE DATE({thistable}.displaydate)
IN (
SELECT DATE(fab_boarding_location.displaydate)
FROM fab_booking
WHERE DATE(fab_boarding_location.displaydate) = DATE('{fab_booking___book_bookingdate}'));

(check the name of your book_bookingdate element)

 

[marcq]

Not sure what you want to do but, i think you need to use placeholder:

WHERE DATE({thistable}.displaydate)
IN (
SELECT DATE(fab_boarding_location.displaydate)
FROM fab_booking
WHERE DATE(fab_boarding_location.displaydate) = DATE('{fab_booking___book_bookingdate}'));

(check the name of your book_bookingdate element)

Thank you for your feedback. Sadly it doesn't work.

The dropdown is populated with all the value I have in the fab_boarding_location table.

We should only find the values which have a 'displaydate' value which is equal to the 'fab_booking___book_bookingdate' value.

But anyway, thanks a lot for your time.

Cheers, Marc

 

[marcq]

Hi,

I tried among others this simple where clause

WHERE DATE({thistable}.displaydate) = '2016-05-30 00:00:00';

error is :

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ORDER BY text ASC' at line 4 SQL=SELECT DISTINCT(`fab_boarding_location`.`id`) AS value, `boarding_location` AS text FROM `fab_boarding_location` AS `fab_boarding_location` WHERE DATE(`fab_boarding_location`.displaydate) = '2016-05-30 00:00:00'; ORDER BY text ASC

or

WHERE {thistable}.displaydate IS NOT NULL;

which should normally work without any problem.


The following SQL query works fine in phpMyAdmin:

SELECT `displaydate` FROM `fab_boarding_location` WHERE `displaydate` IS NOT NULL;

I really need some help here. It might be trivial, but I'm unable to find a way to make it work.

Thank you in avance for your support.

Marc

By the way, I'm willing to extra pay to have this issue and the two other ones in this forum which are unsolved. Thank you for your understanding.

 

[coullet]

Don't add the ; !

 

[marcq]

Don't add the ; !

Thanks coullet for your tip and for your support. In the meantime I found the solution which is :

DATE({thistable}.displaydate)=DATE('{fab_booking___book_bookingdate}')

Simple no ? I needed hours to sort it out...