Consult in two tables simultaneously with two different parameters

Question

Hello, good night!

I’m creating a schedule of events by time, in different locations. So I need that, even before scheduling the event, show which venues are available, after the available venues, I need to show the available schedules.

I have tried to search here and in several other places, but I have not found a solution that meets.

I have 5 tables with 28 identical times, but with different locations.

Basically, using Ajax, I can find the location and times available, but how can I find them for different days?

Date Statement
The returnSalas(); only returns which rooms have registered in the BD

<div class="form-group col-md-6">
   <label for="data">Data:</label>
   <input type="date" name="data" id="data" onchange="returnSalas();" placeholder="Data" class="form-control">
</div>

Return of the rooms in the comic

<div class="form-group col-md-6">
    <label for="sala_de_aula">Sala:</label>
    <select name ="sala_de_aula" id="sala_de_aula" class="form-control" onchange="returnHorariosDisp();">
        <?php
           if (!isset($_GET['DataSelecionada'])) {
        ?>
             <option disabled selected>Informe a data Primeiro</option>
        <?php
              }
        ?>
     </select>
</div>

The role of Ajax is to return the rooms in the above section
returnHorariosDisp(); = Search the available schedules of this room and return in the next section:

<div class="form-group col-md-6">
   <label for="horario_entrada">Horário de Entrada</label>
   <select name="horario_entrada" id="horario_entrada" class="form-control">
      <?php
         if (!isset($_GET['SalaSelecionada'])) {
      ?>
          <option disabled selected>Selecione a sala de aula</option>
      <?php
            }
      ?>
    </select>
</div>

The database has the following tables:

• Sala1
• Sala2
• Sala3
• Sala4
• Sala5

Each table has columns:

• id (int/a_i)
• input (float) 8 to 21.5
• output (float) 8.5 to 22
• status (int) 0 = free | 1 = busy
• display_input (varchar) 08:00 until 21:30
• display_output (varchar) 08:30 until 22:00

All is well, the records are recorded in another table with the name _agenda_aulas_, the composition of the table is like this:

• id (int/a_i)
• matricula_student (int null)
• student name (varchar)
• date (varchar)
• prof (varchar)
• input (varchar)
• exit (varchar)
• materia (varchar)
• qtd_time (float)
• value (float)
• payment (varchar)

Programming is working, both to store the values in the table scheduling, how much to update the table according to the selected room.

Excerpt that makes the programming:

    //Pré Cadastro do aluno
    $QueryConsultarAlunos = "SELECT * FROM alunos WHERE matricula_aluno = '$matricula'";
    $ExeQrConsultarAlunos = mysql_query($QueryConsultarAlunos);
    if (!$ExeQrConsultarAlunos) {
        echo $QrPreCadastrarAluno = "INSERT INTO alunos (matricula_aluno,nome_aluno) VALUES ('$matricula','$nomeAluno')";
        $cadastrarAluno = mysql_query($QrPreCadastrarAluno, $db);
        echo '<br>';
    }

    echo $QrUpdateHorarioSalas = "UPDATE $salaDeAula SET status = 1 WHERE entrada = $horarioEntrada";
    mysql_query($QrUpdateHorarioSalas);
    echo "<br>";

    echo $QrCadastrar = "INSERT INTO agenda_aulas (matricula_aluno,nome_aluno,data,sala,prof,entrada,saida,materia,qtd_hora,valor,pagamento) VALUES ('$matricula','$nomeAluno','$dataAula', '$salaDeAula', '$professor', '$horarioEntrada', '$horarioSaida','$materiaAula','$tempoDeAula','$valorDaAula','$pagamentoAula')";
    $cadastrar = mysql_query($QrCadastrar, $db);
?>

However, as I said at the beginning, I need this same information from each room, but with the filter per day.

I thought about creating new tables declaring as name the date:

<?php
$tabelaDataAgendamento = 'ag_' . date('d_m_Y', strtotime($dataAula));
$resultAgData = mysql_query("SHOW TABLES LIKE '$tabelaDataAgendamento'");
$tabelaExists = mysql_num_rows($resultAgData) > 0;

if ($tabelaExists) {
    echo $QueryInserirTabelaData = "INSERT INTO '$tabelaDataAgendamento'(matricula_aluno,nome_aluno,sala,prof,entrada,saida,materia,qtd_hora,valor) VALUES('$matricula','$nomeAluno','$salaDeAula','$professor','$horarioEntrada','$horarioSaida','$materiaAula','$tempoDeAula','$valorDaAula')";
    $inserirTabelaData = mysql_query($QueryInserirTabelaData);
    echo "<br>";
    if ($inserirTabelaData):echo "Inserido!";
    else: echo "Não Inserido! Erro: " . mysql_error();
    endif;
    echo "<br>";
} else {
    echo $QueryCriarTabelaData = "CREATE TABLE `$tabelaDataAgendamento`(id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, matricula_aluno int, nome_aluno varchar(255),sala varchar(255),prof varchar(255),entrada float,saida float,materia varchar(255),qtd_hora float, valor float)ENGINE=INNODB;";
    mysql_query($QueryCriarTabelaData);
    echo "<br>";
}

Only the problem is, when it comes time to check the records before entering a new record, I don’t know how to perform the filter.

I’ve been racking my brain to solve this for almost a week, someone can help me solve this problem?

Thanks in advance for the help!

Answer 1

Hello let me get this straight, you need to filter the 5 rooms to have the same information with different parameters in the room search ?

Well if that’s what I believe a Union all of the rooms would solve Ex: select id,data from tab1 Where id=1 Union select id,data from tab2 Where data <> 0;