2. PHPRunner General questions
  3. SESSIONS in SQL QUERY
This topic is locked

SESSIONS in SQL QUERY

 		8/14/2017 2:32:50 PM
PHPRunner General questions
S
 stiven author

Hello Everyone!
Has anyone been able to put a Session variable into the SQL Query? I need some dates into the sql query to be dynamic but I've had little success accomplishing this in PHPRunner. Here is the query.



SELECT

timecard.tm_id,

timecard.case_no,

timecard.tm_date,

CONCAT(last_name,' ',first_name) AS client_name,

SUM(tm_hours) AS hours_completed,

(SELECT max_hours FROM pos WHERE pos.case_no = timecard.case_no AND '2017-08-01' <= end_date AND '2017-08-31' >= start_date) AS max_hours,// this date have to be dynamic. '".$_SESSION["StartDate"]."' this won't work

((SELECT max_hours FROM pos WHERE pos.case_no = timecard.case_no AND '2017-08-01'<= end_date AND '2017-08-31' >= start_date) - SUM(tm_hours)) AS missing_hours

FROM `timecard`

INNER JOIN consumers ON timecard.case_no = consumers.case_no

GROUP BY timecard.case_no

ORDER BY client_name ASC
Sergey Kornilov admin 8/15/2017

Yes, this can be done:

https://xlinesoft.com/phprunner/docs/modify_sql_query_on_the_fly.htm
S
 		stiven author 8/15/2017

Thanks for the reply,

That works if the dynamic data is in the WHERE clause. However, I need it to show in the column. I also tried the $query->addField() but with no success.
I tried this as a test

$query->addField("1", "new_column"); but nothing shows on the table...

I also tried $query = new SQLQuery("dynamic query here"); but also no success
I could accomplish this by just adding the column manually into the query and get the results in ViewAs Customview but then I won't be able to sort or search in that column.



Yes, this can be done:

https://xlinesoft.com/phprunner/docs/modify_sql_query_on_the_fly.htm
Sergey Kornilov admin 8/15/2017

I see what you saying. If you need to display a field on the list page it needs to be in the original SQL Query. You can add a simple calculated field to SQL Query and then instead of addField() use replaceField() with the similar idea.