Skip to main content ↑ ↓
Topic: How can I add php command to custom view Query as parameter? (Read 5066 times) previous topic - next topic

How can I add php command to custom view Query as parameter?

Hi,

I created a Custom View in my application (SELECT ... WHERE week_attr=?). I'd like to use the current week as query parameter, with date("W") command. If I use a number in query, the query running is perfect, but I'd like use dynamical parameter. How can I do this?

Regards,
Laci

Re: How can I add php command to custom view Query as parameter?

Reply #1 –
@machobymb‍ where are you adding the query and the PHP code? I mean which part of the phprad configuration are you adding it?

Re: How can I add php command to custom view Query as parameter?

Reply #2 –
When I create a Custom View.

Re: How can I add php command to custom view Query as parameter?

Reply #3 –
@machobymb‍ when you create a custom view from where? There are multiple ways of creating a custom view, please elaborate more to get the right answer to your question.

Re: How can I add php command to custom view Query as parameter?

Reply #4 –
"Custom Views" => "Create Custom View"
If I wrote numbers in the query, that are question marks, then query running correct in phpmyadmin and in PHPRad. I send image in attachment.

I modified the query, that isn't subquery, I create a query with LEFT OUTER JOIN. In phpmyadmin the query running is perfect, but problem is in the new query too.
 

Re: How can I add php command to custom view Query as parameter?

Reply #5 –
@machobymb‍ in place of the first question mark(?) use this DATE_FORMAT(CURRENT_DATE(),'%Y') and in place of the second question mark(?) use this DATE_FORMAT(CURRENT_DATE(),'%V').
DATE_FORMAT(CURRENT_DATE(),'%Y') is for the current year and DATE_FORMAT(CURRENT_DATE(),'%V') is for the current week number in MySQL.

Note: for some reason I don't know, the binding param seems not to be working for that configuration.

Re: How can I add php command to custom view Query as parameter?

Reply #6 –
Thx Willvin, but this only work in PHPMyAdmin. With QueryBuilder and generated application not working. Here is the SQL Query:

SELECT tbl1.hname, tbl2.kname FROM tbl2 JOIN tbl1 ON tbl2.attr = tbl1.id WHERE tbl2.id NOT IN (SELECT tbl3.attr FROM tbl3 WHERE (tbl3.year=?) AND (tbl3.week=?)) ORDER BY tbl1.hname, tbl2.kname

Re: How can I add php command to custom view Query as parameter?

Reply #7 –
@machobymb‍ it means maybe you are doing something wrong. if it works in PhpMyAdmin, it should work on Phprad generated app.

your query should look like this when you add what I said you should add.👇

Code: [Select]
SELECT tbl1.hname, tbl2.kname FROM tbl2 JOIN tbl1 ON tbl2.attr = tbl1.id WHERE tbl2.id 
NOT IN (SELECT tbl3.attr FROM tbl3 WHERE (tbl3.year = DATE_FORMAT(CURRENT_DATE(),'%Y')) 
AND (tbl3.week = DATE_FORMAT(CURRENT_DATE(),'%V'))) ORDER BY tbl1.hname, tbl2.kname

Give me an example value of what is stored in tbl3.week and tbl3.year in your database.

Re: How can I add php command to custom view Query as parameter?

Reply #8 –
I store statistics to be submitted weekly, with the year (eg 2019) in one field and the current week (eg 44) in one field.

Re: How can I add php command to custom view Query as parameter?

Reply #9 –

Quote from: willvin –
@machobymb‍ it means maybe you are doing something wrong. if it works in PhpMyAdmin, it should work on Phprad generated app.

your query should look like this when you add what I said you should add.👇

Code: [Select]
SELECT tbl1.hname, tbl2.kname FROM tbl2 JOIN tbl1 ON tbl2.attr = tbl1.id WHERE tbl2.id 
NOT IN (SELECT tbl3.attr FROM tbl3 WHERE (tbl3.year = DATE_FORMAT(CURRENT_DATE(),'%Y')) 
AND (tbl3.week = DATE_FORMAT(CURRENT_DATE(),'%V'))) ORDER BY tbl1.hname, tbl2.kname

Give me an example value of what is stored in tbl3.week and tbl3.year in your database.

 
@machobymb‍ the above code should work.

Re: How can I add php command to custom view Query as parameter?

Reply #10 –
For me the result is "500 error" page.

Re: How can I add php command to custom view Query as parameter?

Reply #12 –
If I run it under PHPMyAdmin it gives me the result I need.

Re: How can I add php command to custom view Query as parameter?

Reply #14 –
Can I send a private message to You?