Search in different tables knowing what the result came from

Asked

Viewed 442 times

3

I’m trying to find a term in 4 different tables in Mysql, using like, but I need to rescue the id of each and identify from which table came to make the listing and direct with link.

This is a search system where the results should have a link to the corresponding page with the id of each result.

I tried to use 4 selects and union, but I was unsuccessful.

Last attempt made:

("SELECT * FROM web_tv WHERE (titulo LIKE '%".$busca."%' OR (texto '%".$busca."%')) UNION SELECT * FROM agenda WHERE (titulo LIKE '%".$busca."%' OR (texto '%".$busca."%')) UNION SELECT * FROM blog WHERE (titulo LIKE '%".$busca."%' OR (texto '%".$busca."%')) UNION SELECT * FROM osorio WHERE (titulo LIKE '%".$busca."%' OR (texto '%".$busca."%'")
php mysql

  • put your sql to try to help you

  • I updated the question by adding the query code.

  • 1

    To use the union, the number of columns and their names have to be exactly equal, which if it is the case, it would not make sense to have the records in different tables, you have already tried to make the 4 separate queries bringing everything in array and joining afterwards?

  • They do not have equal tables. The only thing that repeats are ID, TITLE and TEXT. The rest is different in each one. I’ll try to join the results, but I thought there was a way to run the query directly in mysql.

  • 1

    @Kennyrafael and Vitor, the names do not need to be equal at all, only the amount and types of the ones that are effectively used in select. You can mix names, but to use outside of UNION you will need to use positional syntax (numbers indicating the column instead of the names)

  • 1

    In other words, SELECT nome, cpf FROM pessoa UNION SELECT razao_social, cnpj FROM empresa works if nome and razao_social are of the same type, and cpf and cnpj also..

  • Thank you @Bacco

  • Thank you @Bacco and Kenny Rafael. I swapped * for the fields I really needed (id, title and text) and fixed the OR that wasn’t working either. Almost all problems have been solved, but I still can’t figure out which table each row is brought... Anyway, thanks.

Show 3 more comments

1 answer

5


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 tables:

Usually in SQL you do not need to return only fields, you can use expressions involving more than one field, or simply use fields in any of the columns. And that’s what we’re gonna do here:

SELECT 'web_tv' AS tabela, campo1, campo2 
       ^^^^^^^^^^^^^^^^^^

This select will always return a name field tabela with the content webtv, to find out where the information came from:

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 actually needed for the search, in place of the *, whenever possible. In some situations * is more practical, but usually specifying the columns is almost always safer.

If you really want to use the *, 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 you’re probably returning data that you won’t use.

The important thing is to understand that we 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...


Simplification tip

This here:

WHERE titulo LIKE '%".$busca."%' OR texto LIKE '%".$busca."%'

It can be simplified in this way:

WHERE concat_ws( ' ', titulo, texto ) LIKE '%".$busca."%'

In the case of two fields it doesn’t change much, but if you have more fields, you can make your life easier.


Alternative syntax

First of all, important to say that when you use this way, probably the UNION will generate a result set temporary and apply the WHERE then what is not very performatic. But as in your case it comes to LIKE with % of both sides, ends up giving in the same, because anyway will be read the records 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

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.