CODEWITHSUNDEEP
phpmysql
Benn
Benn

Reputation: 5023

How to do Mysql Union with check if row exists?

OK this is bit complicated because my tables are not referenced or combined or to be matched with each other so I cant use JOIN's. I am building a search form that would simply match search keyword in multiple tables.

The issue I have is that I have to generate news item link depending from which table the result came but I cant find a hook. MySql union works perfect except that I cant check if data is from table a or table b. 

here is short version 
SELECT  i.title,i.category FROM table_a  WHERE REGEXP 'news'
UNION 
SELECT i.title,i.category FROM table_b WHERE REGEXP 'news'

now , my table A has 36 rows and table B has 34 , one more difference is that table A has row name extra_field which I wanted to check and based on that switch my link.

php switch after query could be something like 

foreach($rows as $row) :
if($row->extra_field):
$link = 'index.php?".$row->category.$row->title".html'
else:
$link = 'index.php?".$row->title".html'
endif;
endforeach;

So is there a way to check in my UNION sql if that row exists? I know how to check if row exists but how to do that in UNION?

Please help. Thank you!

Upvotes: 1

Views: 1198

Answers (1)

Fosco
Fosco

Reputation: 38526

Using your short version as an example, you could add an extra column to the query:

SELECT  i.title,i.category,'a' as source FROM table_a  WHERE REGEXP 'news'
UNION 
SELECT i.title,i.category, 'b' as source FROM table_b WHERE REGEXP 'news'

Then in your code you could check the 'source' column.

Upvotes: 3

Related Questions