[SOLVED] MYSQL UNION

[dennismonsewicz]

Ok....

 

I have written a PHP search App and I want it to search between like 8 or so tables in one Database.

 

Here is my union mysql code:

 

$query = "(select * from arcamax where promo_code like \"%$trimmed%\" or start_end_date like \"%$trimmed%\" or mailedlist like \"%$trimmed%\" or description like \"%$trimmed%\" or orders like \"%$trimmed%\" or totalrevenue like \"%$trimmed%\") union (select * from drudge where promo_code like \"%$trimmed%\" or start_end_date like \"%$trimmed%\" or mailedlist like \"%$trimmed%\" or description like \"%$trimmed%\" or orders like \"%$trimmed%\" or totalrevenue like \"%$trimmed%\") union (select * from google where promo_code like \"%$trimmed%\" or start_end_date like \"%$trimmed%\" or mailedlist like \"%$trimmed%\" or description like \"%$trimmed%\" or orders like \"%$trimmed%\" or totalrevenue like \"%$trimmed%\") union (select * from human_events where promo_code like \"%$trimmed%\" or start_end_date like \"%$trimmed%\" or mailedlist like \"%$trimmed%\" or description like \"%$trimmed%\" or orders like \"%$trimmed%\" or totalrevenue like \"%$trimmed%\") union (select * from newsmax where promo_code like \"%$trimmed%\" or start_end_date like \"%$trimmed%\" or mailedlist like \"%$trimmed%\" or description like \"%$trimmed%\" or orders like \"%$trimmed%\" or totalrevenue like \"%$trimmed%\") union (select * from street where promo_code like \"%$trimmed%\" or start_end_date like \"%$trimmed%\" or mailedlist like \"%$trimmed%\" or description like \"%$trimmed%\" or orders like \"%$trimmed%\" or totalrevenue like \"%$trimmed%\") union (select * from townhall where promo_code like \"%$trimmed%\" or start_end_date like \"%$trimmed%\" or mailedlist like \"%$trimmed%\" or description like \"%$trimmed%\" or orders like \"%$trimmed%\" or totalrevenue like \"%$trimmed%\") union (select * from weatherbug where promo_code like \"%$trimmed%\" or start_end_date like \"%$trimmed%\" or mailedlist like \"%$trimmed%\" or description like \"%$trimmed%\" or orders like \"%$trimmed%\" or totalrevenue like \"%$trimmed%\") union (select * from worldnet where promo_code like \"%$trimmed%\" or start_end_date like \"%$trimmed%\" or mailedlist like \"%$trimmed%\" or description like \"%$trimmed%\" or orders like \"%$trimmed%\" or totalrevenue like \"%$trimmed%\") order by mailedlist";

 

Now if I run a search with the above code it does not work...but if I limit the number of tables to two it works fine. Any suggestions?

[Barand]

The "return" key is at the right of the main section of your keyboard.

[dennismonsewicz]

here ya go (with the added return key):

 

$query = "(select * from arcamax where promo_code like \"%$trimmed%\" or start_end_date like \"%$trimmed%\" or mailedlist like \"%$trimmed%\" or description like \"%$trimmed%\" or orders like \"%$trimmed%\" or totalrevenue like \"%$trimmed%\") union 
(select * from drudge where promo_code like \"%$trimmed%\" or start_end_date like \"%$trimmed%\" or mailedlist like \"%$trimmed%\" or description like \"%$trimmed%\" or orders like \"%$trimmed%\" or totalrevenue like \"%$trimmed%\") union 
(select * from google where promo_code like \"%$trimmed%\" or start_end_date like \"%$trimmed%\" or mailedlist like \"%$trimmed%\" or description like \"%$trimmed%\" or orders like \"%$trimmed%\" or totalrevenue like \"%$trimmed%\") union 
(select * from human_events where promo_code like \"%$trimmed%\" or start_end_date like \"%$trimmed%\" or mailedlist like \"%$trimmed%\" or description like \"%$trimmed%\" or orders like \"%$trimmed%\" or totalrevenue like \"%$trimmed%\") union 
(select * from newsmax where promo_code like \"%$trimmed%\" or start_end_date like \"%$trimmed%\" or mailedlist like \"%$trimmed%\" or description like \"%$trimmed%\" or orders like \"%$trimmed%\" or totalrevenue like \"%$trimmed%\") union 
(select * from street where promo_code like \"%$trimmed%\" or start_end_date like \"%$trimmed%\" or mailedlist like \"%$trimmed%\" or description like \"%$trimmed%\" or orders like \"%$trimmed%\" or totalrevenue like \"%$trimmed%\") union 
(select * from townhall where promo_code like \"%$trimmed%\" or start_end_date like \"%$trimmed%\" or mailedlist like \"%$trimmed%\" or description like \"%$trimmed%\" or orders like \"%$trimmed%\" or totalrevenue like \"%$trimmed%\") union (
select * from weatherbug where promo_code like \"%$trimmed%\" or start_end_date like \"%$trimmed%\" or mailedlist like \"%$trimmed%\" or description like \"%$trimmed%\" or orders like \"%$trimmed%\" or totalrevenue like \"%$trimmed%\") union 
(select * from worldnet where promo_code like \"%$trimmed%\" or start_end_date like \"%$trimmed%\" or mailedlist like \"%$trimmed%\" or description like \"%$trimmed%\" or orders like \"%$trimmed%\" or totalrevenue like \"%$trimmed%\") order by mailedlist";

[Barand]

Do all those tables (arcamax, drudge,google etc) have exactly the same structure?

[dennismonsewicz]

well all of them do except one...

[dennismonsewicz]

the one that does not is the google one. I took it out and it works fine. How would I be able to search this DB table as well while still searching through all of the other DB tables?

[Barand]

You will have to dump the '*' and specify the columns selected so identically structured rows are returned from each union

[dennismonsewicz]

Problem Solved. Thanks