Doubt to build MySQL query

Question

Doubt to build MySQL query

Navigation

#1 by (0 votes)

0

On my system I have the following tables: tb_partida(id, data_hora, flag_ativo, tb_cotacao_id, tb_campeonato_id) , tb_campeonato(id, nome_camp, tb_pais_id) e **tb_pais**(id, nome_pais, tb_continente_id) .

Well, I need a select popular with the country and championship of matches and value being id of the championship onde tb_partida.flag_ativo = 1 . So far I have the following:

Function:

function seleciona_campeonatos_ativos()
{
    $link = conectar();

    $query = "SELECT tb_campeonato.id as id_campeonato, 
      tb_campeonato.nome_camp as nome_campeonato, 
      tb_pais.nome_pais as nome_pais
      FROM tb_campeonato, tb_pais
      WHERE tb_pais.id = tb_campeonato.tb_pais_id";

    $result = mysqli_query($link, $query) or die(mysqli_error($link));

    while ($registro = mysqli_fetch_assoc($result))
    {
        echo "<option value='".$registro['id_campeonato']."'>".$registro['nome_pais'].'» '.$registro['nome_campeonato']."</option>";
    }
}

Select:

<label for="sel1">Filtrar por campeonato:</label>
<br>
<div class="form-group">
    <select style="width: 300px;" class="form-control" id="sel1">
        <?php
            seleciona_campeonatos_ativos();
        ?>
    </select>
</div>

How do I display only the championships for which there is an active match?

php mysql

asked by anonymous 01.08.2016 / 20:40

1 answer

Custom Taxonomy in Wordpress Microsoft Speech Plataform in Portuguese

score 0 · Accepted Answer

As the dichrist commented, add the condition WHERE to the flag_ativo column:

SELECT 
    c.id AS id_campeonato, 
    c.nome_camp AS nome_campeonato, 
    p.nome_pais AS nome_pais
FROM 
    tb_campeonato c
LEFT JOIN  
    tb_pais p ON p.id = c.tb_pais_id
LEFT JOIN  
    tb_partida pt ON pt.tb_campeonato_id = c.id
WHERE 
    pt.flag_ativo = 1

See sqlfiddle