Recordset_Selecting Server Event

This public forum is for user-to-user discussions of PHPMaker. Note that this is not support forum.
Post Reply
johnberman
User
Posts: 210

Recordset_Selecting Server Event

Post by johnberman »

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


mobhar
User
Posts: 11660

Post by mobhar »

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.


johnberman
User
Posts: 210

Post by johnberman »

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


mobhar
User
Posts: 11660

Post by mobhar »

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.


johnberman
User
Posts: 210

Post by johnberman »

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


mobhar
User
Posts: 11660

Post by mobhar »

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.


Post Reply