General use of UNION with condition:
SELECT campo1, campo2 FROM tabela1 WHERE ...
UNION
SELECT campo1, campo2 FROM tabela2 WHERE ...
UNION
SELECT campo1, campo2 FROM tabela3 WHERE ...
UNION
SELECT campo1, campo2 FROM tabela4 WHERE ...
Or:
SELECT * FROM (
SELECT campo1, campo2 FROM tabela1
UNION
SELECT campo1, campo2 FROM tabela2
UNION
SELECT campo1, campo2 FROM tabela3
UNION
SELECT campo1, campo2 FROM tabela4
) todos
WHERE condicao
Applying to your case and differentiating the tables:
Usually in SQL you do not have to return only fields, you can use expressions involving more than one field, or simply not use fields in any of the columns. And here's what we're going to do:
SELECT 'web_tv' AS tabela, campo1, campo2
^^^^^^^^^^^^^^^^^^
This select will always return a field named tabela with content webtv , to know where the information came from, thus:
SELECT 'web_tv' AS tabela, campo1, campo2 FROM web_tv WHERE ...condicoes...
UNION
SELECT 'agenda' AS tabela, campo1, campo2 FROM agenda WHERE ...condicoes...
UNION
SELECT 'blog' AS tabela, campo1, campo2 FROM blog WHERE ...condicoes...
UNION
SELECT 'osorio' AS tabela, campo1, campo2 FROM osorio WHERE ...condicoes...
I recommend using only the fields that are in fact required for the search, instead of * , whenever possible. In some situations * is more practical, but generally specifying the columns is almost always more secure.
If you really want to use * , you can do this:
SELECT 'web_tv' AS tabela, * FROM web_tv WHERE ...condicoes...
UNION
SELECT 'agenda' AS tabela, * FROM agenda WHERE ...condicoes...
...
But it will only give you a headache when you restructure any of them, plus it will probably be returning data you will not use.
The important thing is to understand that we have created a literal column with the names of the original table so you know which result came from where.
If you want to simplify, you can use a numeric value:
SELECT 1 AS tabela, * FROM web_tv WHERE ...condicoes...
Tip of simplification
This here:
WHERE titulo LIKE '%".$busca."%' OR texto LIKE '%".$busca."%'
It can be simplified this way:
WHERE concat_ws( ' ', titulo, texto ) LIKE '%".$busca."%'
In the case of two fields does not change much, but if you have more fields, can make your life easier.
Alternative syntax
First of all, it's important to say that when you use this way, UNION will probably generate a temporary result set and then apply WHERE , which is not very performative . But, as in your case it is a question of LIKE with % on both sides, it ends up giving it anyway, because in any case the records will be read one by one (in all cases).
SELECT * FROM (
SELECT 'web_tv' AS tabela, campo1, campo2 FROM web_tv
UNION
SELECT 'agenda' AS tabela, campo1, campo2 FROM agenda
UNION
SELECT 'blog' AS tabela, campo1, campo2 FROM blog
UNION
SELECT 'osorio' AS tabela, campo1, campo2 FROM osorio
) todos
WHERE todos.campo1 LIKE '%".$busca."%' OR todos.campo2 ....
ORDER BY todos.campo1
