Comparison of tables

1

I am creating a system of bolão, where the administrator registers the games and the scoreboard, and the user has the option to kick a scoreboard, if this score is the same as the one registered by the administrator he informs that the user has hit, otherwise missed I'll put the code in which the administrator registers the game, the user of the bet and also the image of the database. In this case the primary key is código da partida

Table tb_jogos where the administrator registers the items:

CREATE TABLE tb_jogos (
  casa varchar(20) NOT NULL,
  placarcasa int(1) NOT NULL,
  fora varchar(20) NOT NULL,
  placarfora int(1) NOT NULL,
  codigo int(5) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  PRIMARY KEY (codigo);

Admin code to register game:

<?php
session_start();
if(!empty($_SESSION['email'])){

}else{
    echo"<script language='javascript' type='text/javascript'>alert('Aréa Restrita.');
                window.location.href='/bolao/index.php';</script>";
}
$nome=$_SESSION['nome'];

$placarc = substr(mt_rand (0,5),0,1); //gera números de para time casa 0 a 5
$placarf = substr(mt_rand (0,5),0,1); //gera números de para time fora 0 a 5


$btnCadastrar = filter_input(INPUT_POST, 'btnCadastrar', FILTER_SANITIZE_STRING);
if($btnCadastrar){
    include_once ("../conn/conexao.php");
    $dados = filter_input_array(INPUT_POST, FILTER_DEFAULT);

    $jogos = "INSERT INTO tb_jogos(casa, placarcasa, fora, placarfora, codigo)VALUES(
        '".$dados['casa']."',
        '".$dados['placarcasa']."',
        '".$dados['fora']."',
        '".$dados['placarfora']."',
        '".$dados['codigo']."'
        )";
        $mensagem="<script>alert('Codigo de partida ja cadastrado.');
                window.location='partidas.php';</script>";
    $resultado_jogos = mysqli_query($conexao, $jogos) or die ($mensagem);
    if($resultado_jogos):
        echo "<script>
                alert('Jogo cadastrado com sucesso.');
                window.location='index.php';
            </script>"; 
    else:
        echo "<script>
                alert('Ocorreu um erro ao cadastrar o jogo.');
                window.location='index.php';
            </script>";
    endif;
}

?>

<!DOCTYPE html>
<html lang="pt-br">
<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link rel='stylesheet' href='//maxcdn.bootstrapcdn.com/bootstrap/3.3.4/css/bootstrap.min.css'/>
    <link href="https://fonts.googleapis.com/css?family=Open+Sans" rel="stylesheet">

    <title>Lance Web</title>
    <!-- Bootstrap -->
    <link href="../bootstrap/css/bootstrap.min.css" rel="stylesheet">
    <!-- HTML5 shim e Respond.js para suporte no IE8 de elementos HTML5 e media queries -->
    <!-- ALERTA: Respond.js nÃŖo funciona se vocÃĒ visualizar uma pÃĄgina file:// -->
    <!--[if lt IE 9]>
      <script src="https://oss.maxcdn.com/html5shiv/3.7.3/html5shiv.min.js"></script><scriptsrc="https://oss.maxcdn.com/respond/1.4.2/respond.min.js"></script>
    <![endif]-->   

    <!-- Aqui começa o script para gerar um placar para o time casa -->
    <script>
        function myFunction(){
    document.getElementById("placar-c").value = <?php echo $placarc; ?>;
    }
    </script>

    <!-- Aqui começa o script para gerar um placar para o time fora -->
    <script>
        function myFunction2(){
    document.getElementById("placar-f").value = <?php echo $placarf; ?>;
    }
    </script>
</head>
<body>
<!--------------------------------------------------------- nav e o menu ------------------------------------------------------------------->   
<nav class="navbar navbar-inverse">
  <div class="container-fluid">

    <!-- Aqui e como ira aparece em um telefone -->
    <div class="navbar-header">
      <button type="button" class="navbar-toggle collapsed" data-toggle="collapse" data-target="#bs-example-navbar-collapse-1" aria-expanded="false">
        <!-- Aqui no span, sÃŖo os 3 pontos ao abrir em um telefone -->
        <span class="icon-bar"></span>
        <span class="icon-bar"></span>
        <span class="icon-bar"></span>
      </button>
        <a class="navbar-brand" href="index.php">Voltar</a>
    </div>

    <!-- Aqui se edita a parte do saldo -->
    <div class="collapse navbar-collapse" id="bs-example-navbar-collapse-1">
        <ul class="nav navbar-nav">
            <li class=""><a href="partidas.php">Cadastrar Partidas<span class="sr-only">(current)</span></a></li>
            <li class=""><a href="editarpartidas.php">Editar Partidas<span class="sr-only">(current)</span></a></li>
        </ul>

            <!-- Aqui se edita a parte do sair -->  
             <ul class="nav navbar-nav navbar-right">
                 <li><a href="sair.php"><i class="glyphicon glyphicon-off"></i></a></li>
             </ul>

    </div><!-- fim da div collapse, ela faz com que abra um menu ao aumentar o site -->
  </div><!-- /.container-fluid -->
</nav>

<!------------------------------------------------- Aqui começa o formulario de cadastrar os placares ------------------------------------->    
    <form class="form-group" action="" method="post">
        <div class="container" align="center" >
            <div class="col-md-4 col-md-offset-4"><br>
                <label id="font">Time Casa</label>
                    <select class="form-control" name="casa" required>
                         <option selected></option>
                         <option value="Atletico-go">Atlético-GO</option>
                         <option value="Atletico-mg">Atlético-MG</option>
                         <option value="Atletico-pr">Atlético-PR</option>
                         <option value="Avai">Avaí</option>
                         <option value="Bahia">Bahia</option>
                         <option value="Botafogo">Botafogo</option>
                         <option value="Corinthians">Corinthians</option>
                         <option value="Coritiba">Coritiba</option>
                         <option value="Cruzeiro">Cruzeiro</option>
                         <option value="Chapecoense">Chapecoense</option>
                    </select><br>

                <label id="font">Placar time Casa</label>   
                    <div class="row">
                        <div class="col-md-3 col-md-offset-3 col-xs-3">
                            <input id="placar-c" class="form-control" type="text" name="placarcasa" maxlength="1" value="" style="text-align: center;" required /></div>
                        <div class="col-xs-4">
                            <input class="btn btn-success submit-botao" type="submit" value="Gerar" onclick="myFunction()">
                        </div>
                    </div><hr><br><!-- Fim div row placar casa-->

                <label id="font">Time Fora</label>
                    <select class="form-control" name="fora" required>
                        <option selected></option>
                        <option value="Flamengo">Flamengo</option>
                        <option value="Fluminense">Fluminense</option>
                        <option value="Gremio">Grêmio</option>
                        <option value="Palmeiras">Palmeiras</option>
                        <option value="Ponte preta">Ponte Preta</option>
                        <option value="Santos">Santos</option>
                        <option value="Sao paulo">São Paulo</option>
                        <option value="Sport">Sport</option>
                        <option value="Vasco">Vasco</option>
                        <option value="Vitoria">Vitória</option>
                </select><br>

                <label id="font">Placar time fora</label>   
                    <div class="row">
                        <div class="col-md-3 col-md-offset-3 col-xs-3">
                            <input id="placar-f" class="form-control" type="text" name="placarfora" maxlength="1" value="" style="text-align: center;" required /></div>
                        <div class="col-xs-4">
                            <input class="btn btn-success submit-botao" type="submit" value="Gerar" onclick="myFunction2()">
                        </div>
                    </div><hr><!-- Fim div row placar fora-->

                    <label id="font">Codigo da partida</label>  
                    <div class="row">
                        <div class="col-md-4 col-md-offset-4 col-xs-3">
                            <input class="form-control" type="text" name="codigo" maxlength="5" style="text-align: center;" required></div>
                    </div><hr><br><!-- Fim div row placar casa-->

                    <div class="col-md-4 col-md-offset-1"> 
                        <a href="index.php"><input class="btn btn-danger btn-lg" type="button" value="Cancelar"></a>
                    </div>

                    <div class="col-md-5 col-md-offset-1">
                        <input class="btn btn-info btn-lg submit-botao" type="submit" value="Cadastrar" name="btnCadastrar">
                    </div>

            </div><!-- Fim div mover-->

        </div><!-- Fim div container -->

    </form><!-- Fim formulario-->

    </div><!-- Fim Div row-->

    </div><!-- Fim Div container-->

<script src='http://code.jquery.com/jquery-2.1.3.min.js'></script>
<script src='//maxcdn.bootstrapcdn.com/bootstrap/3.3.4/js/bootstrap.min.js'></script>

</body>
</html>

Table tb_aposta where the user places the bets:

CREATE TABLE tb_aposta (
  apostacasa int(1) NOT NULL,
  apostafora int(1) NOT NULL,
  valor int(10) NOT NULL,
  data varchar(15) NOT NULL,
  usuario varchar(40) NOT NULL,
  codigopartida int(5) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  ADD PRIMARY KEY (codigopartida);

User code where he places the bet:

<?php
session_start();
include_once("../conn/conexao.php");
if(!empty($_SESSION['email'])){

}else{
    echo"<script language='javascript' type='text/javascript'>alert('Aréa Restrita.');
                window.location.href='/bolao/index.php';</script>";
}
$nome=$_SESSION['nome'];
$email=$_SESSION['email'];
$saldo="SELECT * FROM tb_usuario WHERE email= '$email'";
$exe= mysqli_query($conexao, $saldo);
$linha = mysqli_fetch_array($exe);

$btnApostar = filter_input(INPUT_POST, 'btnApostar', FILTER_SANITIZE_STRING);
if($btnApostar){
    include_once ("../conn/conexao.php");
    $dados = filter_input_array(INPUT_POST, FILTER_DEFAULT);

    $aposta = "INSERT INTO tb_aposta(apostacasa, apostafora, valor, data, usuario, codigopartida)VALUES(
        '".$dados['apostacasa']."',
        '".$dados['apostafora']."',
        '".$dados['valor']."',
        '".$dados['data']."',
        '".$_SESSION['nome']."',
        '".$dados['codigopartida']."'
        )";
    $r_aposta = mysqli_query($conexao, $aposta) or die (mysqli_error($conexao));
    if($r_aposta):
        echo "<script>
                alert('Apostado com sucesso.');
                window.location='index.php';
            </script>"; 
    else:
        echo "<script>
                alert('Ocorreu um erro ao apostar no jogo.');
                window.location='index.php';
            </script>";
    endif;
}
?>

<!DOCTYPE html>
<html lang="pt-br">
<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link rel='stylesheet' href='//maxcdn.bootstrapcdn.com/bootstrap/3.3.4/css/bootstrap.min.css'/>
    <link href="https://fonts.googleapis.com/css?family=Open+Sans" rel="stylesheet">
    <!-- As 3 meta tags acima *devem* vir em primeiro lugar dentro do 'head'; qualquer outro conteúdo deve vir *após* essas tags -->
    <title>Lance Web</title>
    <!-- Bootstrap -->
    <link href="../bootstrap/css/bootstrap.min.css" rel="stylesheet">
    <!-- HTML5 shim e Respond.js para suporte no IE8 de elementos HTML5 e media queries -->
    <!-- ALERTA: Respond.js não funciona se você visualizar uma página file:// -->
    <!--[if lt IE 9]>
      <script src="https://oss.maxcdn.com/html5shiv/3.7.3/html5shiv.min.js"></script><scriptsrc="https://oss.maxcdn.com/respond/1.4.2/respond.min.js"></script>
    <![endif]-->
</head>
<body>
<!-- nav e o menu -->   
<nav class="navbar navbar-inverse">
  <div class="container-fluid">

    <!-- Aqui e como ira aparece em um telefone -->
    <div class="navbar-header">
      <button type="button" class="navbar-toggle collapsed" data-toggle="collapse" data-target="#bs-example-navbar-collapse-1" aria-expanded="false">
        <!-- Aqui no span, são os 3 pontos ao abrir em um telefone -->
        <span class="icon-bar"></span>
        <span class="icon-bar"></span>
        <span class="icon-bar"></span>
      </button>
        <a class="navbar-brand" href="index.php">Voltar</a>
    </div>

    <!-- Aqui se edita a parte do saldo -->
    <div class="collapse navbar-collapse" id="bs-example-navbar-collapse-1">
        <ul class="nav navbar-nav">
            <li class=""><a>Saldo&nbsp; R$<?php echo $linha['saldo']; ?><span class="sr-only">(current)</span></a></li>
        </ul>
        <ul class="nav navbar-nav">
            <li class=""><a href="apostar.php">Apostar<span class="sr-only">(current)</span></a></li>
        </ul>
        <ul class="nav navbar-nav">
            <li class=""><a href="">Histórico<span class="sr-only">(current)</span></a></li>
        </ul>
        <ul class="nav navbar-nav">
            <li class=""><a href="ranking.php">Ranking<span class="sr-only">(current)</span></a></li>
        </ul>
        <ul class="nav navbar-nav">
            <li class=""><a href="loja/loja.php">Loja<span class="sr-only">(current)</span></a></li>
        </ul>
            <!-- Aqui se edita a parte do sair -->  
             <ul class="nav navbar-nav navbar-right">
                 <li><a href="sair.php"><i class="glyphicon glyphicon-off"></i></a></li>
             </ul>

    </div><!-- fim da div collapse, ela faz com que abra um menu ao aumentar o site -->
  </div><!-- /.container-fluid -->
</nav>

<!------------------------------------------------------------------------------------------------------------------------>

        <?php
                include("../conn/conexao.php");
                //ORDER BY serve para organizar os dados de acordo com o que voce quiser
                $buscar="SELECT * FROM tb_jogos ORDER BY codigo DESC";
                $exe= mysqli_query($conexao, $buscar) or die ("OCORREU UM ERRO AO MOSTRAR OS DADOS");
                //começo da tabela
                echo "<br><br><div class='container'>
                        <table class='table table-inverse'>
                          <thead>
                            <tr bgcolor='#222222' align='center'>
                              <th><font color='white'>Codigo Partida</font></th>
                              <th><font color='white'>Time Casa</font></th>
                              <th><font color='white'>Placar</font></th>
                              <th><font color='white'>Time Fora</font></th>
                              <th><font color='white'>Placar</font></th>
                              <th><font color='white'>Lance</font></th>
                              <th><font color='white'></font></th>
                            </tr>
                         </thead>
                      </div>";


                while($linha = mysqli_fetch_array($exe)){
                    echo "<form class='form-group' action='' method='post'>
                          <tbody>
                            <tr bgcolor='#222222'>
                              <td><font color='white'><input type='int' class='form-control' name='codigopartida' maxlength='1' value=".$linha['codigo']." style='text-align: center;' readonly='readonly'></font></td>
                              <td><font color='white'>".$linha['casa']."</font></td>
                              <td><input type='int' class='form-control' name='apostacasa' maxlength='1' value='' style='text-align: center;'></td>
                              <td><font color='white'>".$linha['fora']."</font></td>
                              <td><input type='int' class='form-control' name='apostafora' maxlength='1' value='' style='text-align: center;'></td>
                              <td><input type='int' class='form-control' name='valor' maxlength='5' value='' style='text-align: center;'></td>
                              <td><input class='btn btn-success submit-botao' type='submit' value='Apostar' name='btnApostar'></td>
                            </tr>
                          </tbody>
                          </form>";

                }
        ?>


<script src='http://code.jquery.com/jquery-2.1.3.min.js'></script>
<script src='//maxcdn.bootstrapcdn.com/bootstrap/3.3.4/js/bootstrap.min.js'></script>

</body>
</html>
    
asked by anonymous 06.11.2017 / 12:24

1 answer

1

The first thing you need to keep in mind is the connection (reference) between the two tables to know which bet refers to which match, then we will have:

Table tb_jogos where the administrator registers the items:

    CREATE TABLE IF NOT EXISTS 'tb_jogos' (
  'id_jogo' int(11) NOT NULL,
  'casa' varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  'placarcasa' int(1) NOT NULL,
  'fora' varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  'placarfora' int(1) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Table tb_aposta where the user places the bets:

   CREATE TABLE IF NOT EXISTS 'tb_aposta' (
  'id_aposta' int(11) NOT NULL,
  'id_jogo' int(5) NOT NULL,
  'apostacasa' int(1) NOT NULL,
  'apostafora' int(1) NOT NULL,
  'valor' float NOT NULL,
  'data' date NOT NULL,
  'usuario' varchar(40) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Note that in the second table a game id field was added to find out which game the bet was placed on.

I thought there were lots of codes to post here, so I went up to a repository in Github .

  

Repository

     

link

    
06.11.2017 / 17:54