2. PHPRunner General questions
  3. [SOLVED]  Custom view
This topic is locked
[SOLVED]

 Custom view

 		6/17/2020 1:17:44 AM
PHPRunner General questions
rdf author

Hi PHPR community,
Is it ok to use SQL variables on creating SQL on the Query page? It is a Custom View named: formdetail_subcat

SELECT s1.id

FROM subcategory AS s1

WHERE (s1.id NOT IN(SELECT formdetail.subcategory_id FROM formdetail WHERE formdetail.formhf_id=':formhf_id' AND formdetail.category_id=':category_id'))

AND (s1.category_id = ':category_id')

UNION

SELECT s2.id

FROM subcategory AS s2

WHERE s2.id = ':subcategory_id'


NOTE: Forums suggest to use views to use UNION since it is not compatible with PHPR.
The return is just single column with IDs. Example:



3

17

73

26


And, then, I used it on the WHERE of the Lookup Wizard:



WHERE subcategory.id IN (SELECT * FROM formdetail_subcat)


But, it gives the following error:



Technical information

Error type 256

Error description Table 'wtmc.formdetail_subcat' doesn't exist

URL localhost/formdetail_edit.php?

Error file ...\output\connections\Connection.php

Error line 611

SQL query SELECT `id`, `name`, `category_id` FROM `subcategory` WHERE ( ( subcategory.id IN (SELECT * FROM formdetail_subcat) ) and ( (category_id=3) ) )
rdf author 6/17/2020

Hi PHPR community,
I still want to know why the Custom View created does not exist when application is run.
I did create a custom view from within MySQL. PHPR can see it. But the Custom View I created from within PHPR are not showing in MySQL. Thus probably making PHPR pop an error message: "Table ... not existing".
For now, I did a little twist on the SQL query without the use of UNION or even a Custom View.



subcategory.id NOT IN(SELECT formdetail.subcategory_id FROM formdetail

WHERE formdetail.formhf_id=':formhf_id'

AND formdetail.category_id=':category_id'

AND formdetail.subcategory_id NOT IN(':subcategory_id'))
Sergey Kornilov admin 6/17/2020

You are confusing database views and custom views. Custom Views are PHPRunner thing, just merely a named SQL Query.
If you need to work with something like UNION you need to create a view in the database itself.

https://dev.mysql.com/doc/refman/8.0/en/create-view.html
rdf author 6/17/2020



You are confusing database views and custom views. Custom Views are PHPRunner thing, just merely a named SQL Query.
If you need to work with something like UNION you need to create a view in the database itself.

https://dev.mysql.com/doc/refman/8.0/en/create-view.html


Hi Sergey,
Thank you for your reply. But how come the Custom View "does not exist" when I ran my application?
I will figure out how to pass SQL variables from the SQL statement to the MySQL database view.
You think I will require procedure for this?
You have examples somewhere on PHPR website, forums or blog so I can see how its use?
Sergey Kornilov admin 6/18/2020

First create the view in MySQL without using any variables. Then add this view to the project and use those variables in WHERE clause.
But formdetail_subcat needs to be a real view in MySQL and not a custom view.
rdf author 6/18/2020



First create the view in MySQL without using any variables. Then add this view to the project and use those variables in WHERE clause.
But formdetail_subcat needs to be a real view in MySQL and not a custom view.


Hi Sergey,
Noted and thank you for the instruction. I will give them a try.