Search in multiple tables

Question

Hi, I’m trying to search 3 different tables, check my code:

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

The tables would be: anime, movies and series. All have the same columns, ie 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. It could help me?

Structures of my tables:

animes:

filmes:

series:

If you prefer the images hosted:

animus: prntscr.com/ga9nw9 || films: prnt.sc/ga9o5w || series: prnt.sc/ga9occ

Answer 1

Hello,

In this case you can use the clause UNION, that together 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 an extra 'column' indicating which table the record is coming from (first column). Includes all columns that are common among the 3 tables and includes the column destaque that exists in the table filmes and does not exist in the other to exemplify this behavior that, in this example, I left as default value in the other tables as null.

I created this example also in this fiddle: https://www.db-fiddle.com/f/jefqxbEkSJxArr4xQus6B2/0

Recalling that the UNION demands 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: https://dev.mysql.com/doc/refman/5.7/en/union.html