Find two MySQL tables and select rows with one element in common

0

How can I use a foreach for this situation?

I would be selecting an element of the tabela dois - event - and for each event, get the tabela um data that also have the same event in its structure.

By the way, when asking the related vi question about INNER JOIN , would that be a path?

  

Update-Nowitworks!:)

FirstwassomedetailsabouttheHTMLtable.

Iwasusing<thread>butit<thead>andthe<th>ofthetitleoutofa<tr>doesnotwork,Istartedusing<h>.

Nowthechangesthatreallymattertologic.

Ichangedthefetch()loopstructuretofetchAll().ThedifferenceisthatbeforeIwascreatingasimplearrayandIwasnotgoingthroughalltheelementsinthetimetofillthetables.WithFetchAllIpassedallinformationfromtheEntriestabletoavariable-justlikeinthe@Pauloexample.

AndtheIfconditionnowworksfine,takingthesamenamedeventsandloopingforeach.

MySQL

CREATEDATABASEExercicio;USEExercicio;CREATETABLEIFNOTEXISTSeventos(IDSMALLINTAUTO_INCREMENTPRIMARYKEY,eventoVARCHAR(50)NOTNULL);INSERTINTOeventos(evento)VALUES('um');INSERTINTOeventos(evento)VALUES('dois');INSERTINTOeventos(evento)VALUES('tres');CREATETABLEIFNOTEXISTSingressos(IDSMALLINTAUTO_INCREMENTPRIMARYKEY,nomeVARCHAR(50)NOTNULL,mesaVARCHAR(50)NOTNULL,eventoVARCHAR(50)NOTNULL);INSERTINTOingressos(nome,mesa,evento)VALUES('fulano','30','um');INSERTINTOingressos(nome,mesa,evento)VALUES('fulana','35','um');INSERTINTOingressos(nome,mesa,evento)VALUES('ciclano','10','dois');INSERTINTOingressos(nome,mesa,evento)VALUES('ciclana','31','dois');INSERTINTOingressos(nome,mesa,evento)VALUES('beltrano','60','tres');INSERTINTOingressos(nome,mesa,evento)VALUES('beltrana','35','tres');

HTMLandPHP

<?php

include 'conexao.php';

$ingressos = $con->prepare("SELECT ingressos.nome, ingressos.mesa, ingressos.evento, eventos.evento FROM ingressos INNER JOIN eventos ON ingressos.evento = eventos.evento");
$ingressos ->execute();
$ingressos->setFetchMode(PDO::FETCH_ASSOC);
$dados = $ingressos->fetchAll();


$eventos = $con->prepare("SELECT evento FROM eventos");
$eventos ->execute();

?>


<!doctype html>

<html>
	<body>
        
                <?php 

    if($ingressos){

        
            foreach($eventos as $evt){

echo " <div class='box[]'>
                       
       <table border='1px'>
         
         <thead>
                
                <h3> ".$evt['evento']."   </h3>
                
               <tr>
                         <th>mesa</th>
                         <th>nome</th>
               </tr> 
   
         </thead> ";

foreach($dados as $ingr){
    
    if($ingr['evento'] == $evt['evento']){
         
    echo " <tbody> ";
    echo " <tr>   ";                    
      
        
      echo "<td>" .$ingr['mesa'].   "</td>";
      echo "<td>" .$ingr['nome'].   "</td>";
      
      
     
  }}}}
        
    echo " </tr>     ";                             
    echo " </tbody>  ";    
        
    echo " </table> ";                     
    echo "</div>";

?>



	</body>
</html>

I've been running in the database adding events in the Events table and more tickets in the Tickets table and ready, everything going your way!

    
asked by anonymous 15.02.2018 / 02:28

3 answers

1

There are lots of ways to use the ForEach loop, as a basis for your question, I've put together a simple example to show use of Join and Foreach.

Construction of the bank (Note that I used the data provided by you):

Code:

CREATETABLEEventos(IDINTAUTO_INCREMENTPRIMARYKEY,EventoNVARCHAR(100)NOTNULL,DataEventoDATETIME,Valordecimal);CREATETABLEControle(IDINTAUTO_INCREMENTPRIMARYKEY,MesaINTNOTNULL,ClienteNVARCHAR(100),EventoIDINT,CONSTRAINTFK_EventoDaMesaFOREIGNKEY(EventoID)REFERENCESEventos.ID);INSERTINTOEventos(Evento,DataEvento,Valor)VALUES('EventoA','2018-02-23',20.00);INSERTINTOEventos(Evento,DataEvento,Valor)VALUES('EventoB','2018-02-21',15.00);INSERTINTOControle(Mesa,Cliente,EventoID)VALUES(25,'ClienteX',1);INSERTINTOControle(Mesa,Cliente,EventoID)VALUES(40,'ClienteX',2);INSERTINTOControle(Mesa,Cliente,EventoID)VALUES(34,'ClienteX',1);INSERTINTOControle(Mesa,Cliente,EventoID)VALUES(55,'ClienteX',2);SELECTC.Mesa,C.Cliente,E.Evento,E.DataEvento,E.ValorFROMControleASCINNERJOINEventosASEONC.ID=E.ID;

Usingforeachinhtml+php(thereareseveralwaystodoit):

Code:

foreach($pdo->query($query)as$linha){echo("<table>
                <tr><th>$linha[2]</th></tr> 
                <tr><td>Mesa: $linha[0]</td></tr>
                <tr><td>Cliente: $linha[1]</td></tr>
                <tr><td>Data: $linha[3]</td></tr>
                <tr><td>Valor: $linha[4]</td></tr>
             </table");         
    }

As requested a table for each event (I just did not understand why to display a column with event name if the table header already informs which event it is):

NEW IMAGE WITH CORRECTION

I'mcrawlinginPHPandasI'mnot"throwing in the towel", I did a search and browsing the PHP documentation, I found a class called RecursiveIteratorIterator ... searching deeper I found an example where this class was used for increment rows of a table with the return of the array generated by the Fetch property. I soon saw the possibilities. PHP Documentation - RecursiveIteratorIterator Class

I hope I have helped you find your solution, as I am very happy with the discovery of this class.

How can you see very simple and functional. I did not bother creating a connection class because no connection was requested in the question.

    
15.02.2018 / 03:53
0

I did not quite understand your question, but in php I would do so

<?php
$mysqli = new mysqli("host", "user", "password", "db");

if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}

$evento = $_POST['evento'];

if ($sth = $mysqli->query("SELECT * FROM tabelas_um WHERE evento = '$evento'")){
while ($row = $sth->fetch_assoc()) 
{
    //Aqui tu faz oq quer
     //Ex: imprimir os dados
       echo "Mesa: " . $row['mesa'] . " Nome: " . $row['nome'];

}
}else{
     echo "Nada encontrado!";
}

As I said, I did not quite understand your question, but see if it satisfies you.

    
15.02.2018 / 02:40
0

Doing with INNER JOIN would be the clearest and perhaps performative way to solve this problem, query would be something like this:

SELECT b.* FROM 'tabela_dois' a
INNER JOIN 'tabela_um' b ON a.'evento' = b.'evento';

To do with foreach would look something like this:

foreach ($tabelaDois as $linhaTabelaDois){
    foreach ($tabelaUM as $linhaTabelaUM){
        if ($linhaTabelaDois["evento"] == $linhaTabelaUM["evento"]){
            echo "
                <tr>
                    <td>".$linhaTabelaUM['mesa']."</td>
                    <td>".$linhaTabelaUM['nome']."</td>
                <tr>
            ";
        }
    }
}
    
15.02.2018 / 02:41