2. PHPRunner General questions
  3. UNION statement issue
This topic is locked

UNION statement issue

 		7/29/2008 7:54:31 PM
PHPRunner General questions
S
 shaoulee author

I have a few tables with similar columns that I want to combine into a single view. For the sake of simplicity, let's say I have the following 2 tables:
Table: Fruit_purchases

Columns: Fruit_name, Fruit_color, Fruit_weight, Fruit_price, Fruit_purchase_date
Table: Vegetable_purchases

Columns: Vegetable_name, Vegetable_type, Vegetable_price, Vegetable_purchase_date
Say I want to have one list that shows all purchases from both fruits and vegetables, with date and purchase date. So I create a new "Custom View" (let's call it "All_Purchases") and place the following code on the "Edit SQL Query":
SELECT Fruit_name AS Name, Fruit_price AS Price, Fruit_purchase_date AS Date FROM Fruit_purchases, Vegetable_purchases

UNION SELECT Vegetable_name, Vegetable_price, Vegetable_purchase_date FROM Fruit_purchases, Vegetable_purchases;
(PHPRunner gives an error if I don't specify all tables in every union statement). The code as it is works great in the preview (shows 3 columns, with all records combined).
However, when I build the project, PHPRunner comments out my custom query and places a simple "SELECT" on the original table that the "Custom View" was based on. Then I have to manually go every time to "All_Purchases_variables.php" and put the code again.
Why is PHPRunner automatically commenting out my custom code?
J
 		Jane 7/30/2008

Hi,
PHPRunner do not support UNION in the SQL query on the Edit SQL query tab.

As workaround create view in the database directly and then use this view in the PHPRunner.

Here is a sample:

create view `all_purchases` as

SELECT Fruit_name AS Name, Fruit_price AS Price, Fruit_purchase_date AS Date FROM Fruit_purchases, Vegetable_purchases

UNION SELECT Vegetable_name, Vegetable_price, Vegetable_purchase_date FROM Fruit_purchases, Vegetable_purchases;
S
 		steveh 7/30/2008

Another option which has worked (and may be useful if you're querying customer databasses and they don't want you ctreating views) is to turn it into a subselect:-
select * from (select a as Name from table1 union select b from table2) z
O
 		ossi69 3/30/2009

Hi,

PHPRunner do not support UNION in the SQL query on the Edit SQL query tab.

As workaround create view in the database directly and then use this view in the PHPRunner.

Here is a sample:


So where do you "create view" ? Somewhere in phprunner?

And when you say "use this in the phprunner" where are you referring to?

Thx
Sergey Kornilov admin 3/30/2009

No, you need to create this view in MySQL (need MySQL 5.0 or higher for this purpose).
Once this view is created in MySQL you will be able to pick it from the list of available views on Datasource tables screen.
O
 		ossi69 3/30/2009

No, you need to create this view in MySQL (need MySQL 5.0 or higher for this purpose).

Once this view is created in MySQL you will be able to pick it from the list of available views on Datasource tables screen.


Holy Smokes <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=39625&image=1&table=forumreplies' class='bbc_emoticon' alt=':rolleyes:' />

This thing is cool.
I was looking for "view" folder, bit it is actually its own table... did not even know about this.
Very impressed.
Thanks