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!
解决方案
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.