Search in multiple tables

Question

Search in multiple tables

Navigation

#1 by (2 votes)

2

Hello, I'm trying to search in 3 different tables, see my code:

$buscando = $pdo->query("SELECT * FROM filmes WHERE nome LIKE '%$busca%' AND status='ativo' ORDER BY nome ASC");

The tables would be: animes, films and series. All have the same columns, that is, it would be WHERE nome LIKE '%$busca%' AND status='ativo' for all.

I tried to perform using this select: SELECT * FROM animes, filmes, series , but did not return anything. Could you help me?

Structures of my tables:

animes :

filmes:

series:

Ifyouprefertheimageshosted:

animes: prntscr.com/ga9nw9 || movies: prnt.sc/ga9o5w || series: prnt.sc/ga9occ

php pdo query busca

asked by anonymous 19.08.2017 / 00:25

1 answer

Error trying to migrate bd from Laravel to PostgreSQL Validating input type dates with jquery

score 2 · Accepted Answer

Hello,

In this case you can use the UNION clause, which together with two or more results in one, follows an example:

SELECT
    *
FROM
    (
        SELECT
            'animes' AS tabela,
            id,
            nome,
            url,
            imagem,
            status,
            null AS destaque
        FROM
            animes
        UNION ALL
        SELECT
            'filmes' AS tabela,
            id,
            nome,
            url,
            imagem,
            status,
            destaque
        FROM
            filmes
        UNION ALL
        SELECT
            'series' AS tabela,
            id,
            nome,
            url,
            imagem,
            status,
            null AS destaque
        FROM
            series
    ) AS t
WHERE
    t.nome LIKE '%a%'
    AND t.status='ativo'
ORDER BY
    t.nome;

In this example, it includes a 'column' indicating what table the record is coming from (first column). I include all columns that are common among the 3 tables and include the destaque column that exists in the filmes table and does not exist in the other one to exemplify this behavior which, in this example, left as the default value in the other tables as null .

I created this example also in this fiddle: link

Remembering that UNION requires that all selects must have exactly the same amount of columns with the same types

.

To read more about union you can consult the official MySQL documentation: link