PHP Mysql: Comparing Data Between Two Tables

Asked

Viewed 1,924 times

1

I have the following tables draw and games in a particular Mysql database.
In the draw table, are stored the data of the contest number, drawn 1st decade, 2nd, 3rd, 4th and 5th decade.
draw table
+------------------------------------------------+
| competition | so1 | so2 | so3 | so4 | so5 |
+------------------------------------------------+
|. . 3916 ... | 13 ..| 26 .| 44 .| 64 ..| 68 ..|
+------------------------------------------------+

On the table games, is stored the cards played, containing the dice as number of the contest of the draw, 1st to 5th scored. each game contains an identified called id_games which is the P.K table.

table games
+--------------------------------------------------+-----+
| contest | id_matches | jo1 | Jo2 | Jo3 | jo4 | jo5 |
+--------------------------------------------------+-----+
|. . 3916 ... | . 01 . . . | 13 | 28 . | 44 | 68 | 69 .|
+--------------------------------------------------+-----+
|. . 3916 ... | . 02 . . . | 13 | 28 . | 44 | 68 | 69 .|
+--------------------------------------------------+-----+
|. . 3917 ... | . 03 . . . | 13 | 28 . | 44 | 68 | 69 .|
+--------------------------------------------------+-----+

<?php

// Variável recebida referente a consulta
$consulta = $_POST["consulta"];
// variável que conecta ao banco 
$pesquisa = $conn->prepare
    ("SELECT * FROM SORTEIO INNER JOIN JOGOS USING(CONCURSO)");  
$pesquisa->execute();  
$resultado = $pesquisa->fetchAll(PDO::FETCH_ASSOC);

// laço para buscar coluna id_jogos, o valor de cada cartela.
foreach($resultado as $r ){
if ($r['concurso'] == $consulta){
// aqui dentro desse laço quero retornar as dezenas acertadas de cada jogo. exemplo, ao consultar o concurso 3916, encontro três jogos na tabela jogos. são as id_jogos 01 e 02. Na id_jogos 01 há três resultados que se repetem, isso quer dizer que acertei três dezenas. exibo os valores acertados, e passo para o id_jogos 02 realizar outra consulta e exibir os valores acertados. e assim sucessivamente, independente da quantidade de jogos tiver.
}
}	

?>

php mysql
  • 2

    What’s the matter?

  • Try to express yourself better, it’s not clear the problem.

  • When explaining the columns of the table, put specific names of the columns for us to put in the answer, so it is very vague. Also paste a piece of code to make it clearer.

  • Noble friends @Marcoauréliodeleu sought to improve editing the post for a better understanding.

  • 1

    Opa @Edsonhoraciojunior, edited for better understanding.

  • @Pedropaparology OK Pedro, now you have explained the context, but it remains to explain what the problem is/what you want.

  • @Edsonhoraciojunior , worth is almost this, I want to return from each game the dozens individually hit. Ex: The scores hit from the game (id_games 01), there being another game in the same contest also display it separately. let’s say that in a given contest consulted, have 10 games, I want for each game related in the consultation, return me the tens hit.

  • @Pedropaparology the code I posted already does this, just apply your visualization logic now: display in table or Divs, whether it will be all in a row or separate into rows, etc, but the logic is already correct.

Show 3 more comments

1 answer

1


From what I understand is to display the dozens of hits of all games in a given contest, if that is the case you can filter the contest in SELECT

$sql  = "SELECT * FROM SORTEIO INNER JOIN JOGOS USING(CONCURSO)";
$sql .= "WHERE concurso = {$consulta}";
$conn->prepare($sql);

Below is your code but now comparing and displaying the correct dozens

<?php
// Variável recebida referente a consulta
$consulta = $_POST["consulta"];

$sql        = "SELECT * FROM SORTEIO INNER JOIN JOGOS USING(CONCURSO)";
// ou $sql  = "SELECT * FROM SORTEIO INNER JOIN JOGOS USING(CONCURSO)";
//    $sql .= "WHERE concurso = {$consulta}";

// variável que conecta ao banco 
$pesquisa  = $conn->prepare($sql);  
$pesquisa->execute();  
$resultado = $pesquisa->fetchAll(PDO::FETCH_ASSOC);

// laço para buscar coluna id_jogos, o valor de cada cartela.
foreach ($resultado as $r) {

    // este if pode ser removido caso esteja filtrando o concurso no SELECT
    if ($r['concurso'] == $consulta) {            
        $sorteios = array($r['so1'], $r['so2'], $r['so3'], $r['so4'], $r['so5']);
        $jogos    = array($r['jo1'], $r['jo2'], $r['jo3'], $r['jo4'], $r['jo5']);

        // Loop nos jogos
        foreach ($jogos as $jogo) {

            // se jogo foi sorteado, então exibe
            if (in_array($jogo, $sorteios)) {
                echo $jogo;
            }
        }
    }
} ?>

Browser other questions tagged php mysql

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