www.hkvforums.com

My orginal SQL on the server was this

SELECT
b.event_id AS a,
b.user_ID AS a,
b.date AS a,
b.Time AS a
FROM quadtest AS a
JOIN quadtest AS b
WHERE a.date = b.date

AND time_to_sec(a.Time) - time_to_sec(b.Time) BETWEEN -30 AND 30");

I then as a trial just did this in Table Specific\Common\Recordset_selecting

function Recordset_Selecting(&$filter) {
ew_AddFilter($filter, "quadtest.date = '2017/01/01'");
}

and it worked fine so im adding in the right place and the syntax is correct

Next I tried this
function Recordset_Selecting(&$filter) {
ew_AddFilter($filter, "SELECT
b.event_id AS a,
b.user_ID AS a,
b.date AS a,
b.Time AS a
FROM quadtest AS a
JOIN quadtest AS b
WHERE a.date = b.date

AND time_to_sec(a.Time) - time_to_sec(b.Time) BETWEEN -30 AND 30");}

Failure - I assume its a syntaxt issue

My eventual aim is for the user to be able to add a value via the search page for the 30 in the between statement, but though moving in stages would be sensible ?

Regards
John B

Your code is wrong. You cannot include SELECT statement in ew_AddFilter() function. You just only need to include in the WHERE clause into the function.

Ok i see that but as I need to do a join on the table in order for it work how do I do that ?

John B

Then you should create a Database View that joins those tables, afterwards use that Database View as the object in your PHPMaker project.

You may simply treat the related Fields in that Database View to define your own custom filter in "Recordset_Selecting" server event.

Hmm

So I created a view on the server called QT with this code

select a.event_id AS event_id,a.user_ID AS user_ID,a.date AS date,a.Time AS Time from (quadrantids2017 a join quadrantids2017 b)

and added to Table Specific\Common\Recordset_selecting

function Recordset_Selecting(&$filter) {
ew_AddFilter($filter, "where ((a.date = b.date) and ((time_to_sec(a.Time) - time_to_sec(b.Time)) between -(30) and 30))");
}

when I run I get this error

(mysqlt): SELECT * FROM qt WHERE where ((a.date = b.date) and ((time_to_sec(a.Time) - time_to_sec(b.Time)) between -(30) and 30)) LIMIT 0, 20
Error (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 'where ((a.date = b.date) and ((time_to_sec(a.Time) - time_to_sec(b' at line 1

so its not join the server sql to the recordset sql ?

Regards
John B

Just remove "where" word in your "Recordset_Selecting" server event. Please always read "Server Events and Client Scripts" topic from PHPMaker Help menu for more info and example.