Search two Mysql tables and select rows with a common element

Asked

Viewed 864 times

0

What is the way to use a foreach for this situation ?

Would be selecting an element from tabela dois - event - and for each event, pick up from tabela um data that also have in its structure the same event.

By the way, when asking the question I saw in the related on INNER JOIN, that would be one way ?

inserir a descrição da imagem aqui

Update - Now it worked! :)

First was some details regarding the HTML table.

Was using <thread> but it is <thead> and the <th> of the title outside a <tr> does not work, I started to use <h>.

Now the changes that really matter to logic.

I changed the structure of the fetch() loop to fetchAll(). The difference is that before I was creating a simple array and I wasn’t going through all the elements when filling the tables. With Fetchall I passed all the information from the Tickets table in one variable - Just like in the @Paulo example.

And the condition If now it works well, taking the events with the same name and making the loop for each one.

MySQL

CREATE DATABASE Exercicio;

USE Exercicio;

CREATE TABLE IF NOT EXISTS eventos (
ID  SMALLINT  AUTO_INCREMENT PRIMARY KEY,
evento VARCHAR(50) NOT NULL

);

INSERT INTO eventos (evento) VALUES ('um');
INSERT INTO eventos (evento) VALUES ('dois');
INSERT INTO eventos (evento) VALUES ('tres');

CREATE TABLE IF NOT EXISTS ingressos (
ID  SMALLINT  AUTO_INCREMENT PRIMARY KEY,
nome VARCHAR(50) NOT NULL,
mesa VARCHAR(50) NOT NULL,
evento VARCHAR(50) NOT NULL
);

INSERT INTO ingressos (nome,mesa,evento) VALUES ('fulano', '30', 'um');
INSERT INTO ingressos (nome,mesa,evento) VALUES ('fulana', '35', 'um');
INSERT INTO ingressos (nome,mesa,evento) VALUES ('ciclano', '10', 'dois');
INSERT INTO ingressos (nome,mesa,evento) VALUES ('ciclana', '31',  'dois');
INSERT INTO ingressos (nome,mesa,evento) VALUES ('beltrano', '60', 'tres');
INSERT INTO ingressos (nome,mesa,evento) VALUES ('beltrana', '35', 'tres');

HTML and PHP

<?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, all going your way!

inserir a descrição da imagem aqui

php mysql

3 answers

1

There are numerous ways to use the Foreach repainting loop, as a basis in your question I have elaborated a simple example to show use of Join and Foreach.

Database construction (Note that I used the data provided by you):

inserir a descrição da imagem aqui

Code:

CREATE TABLE Eventos(
    ID INT AUTO_INCREMENT PRIMARY KEY,
    Evento NVARCHAR(100) NOT NULL,
    DataEvento DATETIME,
    Valor decimal
);


CREATE TABLE Controle(
    ID INT AUTO_INCREMENT PRIMARY KEY,
    Mesa INT NOT NULL,
    Cliente NVARCHAR(100),
    EventoID INT,

    CONSTRAINT FK_EventoDaMesa
    FOREIGN KEY (EventoID)
    REFERENCES Eventos.ID
);


INSERT INTO Eventos(Evento, DataEvento, Valor) VALUES('Evento A', '2018-02-23', 20.00);
INSERT INTO Eventos(Evento, DataEvento, Valor) VALUES('Evento B', '2018-02-21', 15.00);

INSERT INTO Controle(Mesa, Cliente, EventoID) VALUES(25, 'Cliente X', 1);
INSERT INTO Controle(Mesa, Cliente, EventoID) VALUES(40, 'Cliente X', 2);
INSERT INTO Controle(Mesa, Cliente, EventoID) VALUES(34, 'Cliente X', 1);
INSERT INTO Controle(Mesa, Cliente, EventoID) VALUES(55, 'Cliente X', 2);

SELECT C.Mesa, C.Cliente, E.Evento, E.DataEvento, E.Valor FROM Controle AS C
INNER JOIN Eventos AS E
ON C.ID = E.ID;

Use of foreach in html + php (highlighting there are several ways):

inserir a descrição da imagem aqui

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 didn’t understand why display a column with event name if the table header already tells you which event it is):

NEW IMAGE WITH CORRECTION inserir a descrição da imagem aqui

I am crawling in PHP and since I am not a "throw in the towel", I did a search and searching the PHP documentation, I found a class called RecursiveIteratorIterator... by digging deeper I found an example where this class was used to increment rows of a table with the return of the array generated by the property Fetch. Soon I saw the possibilities. PHP Documentation - Class Recursiveiteratoriterator

I hope to have helped to find your solution, because I am super happy with the discovery of this class.

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

  • you commented on the other uses of foreach, would it by any chance be as if I wanted to group mysql lines according to the common event ? I repeated his example and the foreach ends up generating a table for each row found, always having Event A or Event B repeating for example, but the data are organized correctly, the only thing is that they are not grouped in the same table. Returning to your example, would continue printing in Event A the rest of your INSERT, in the case of event A would have tbm the table 34 client for example. Thank you very much!

  • I added a way I tried to do, it’s not like yours, the structure of Mysql is different, then take a look if you can.

  • @Yemoja I have seen yes, already put as requested, it is a matter of logic only!

  • @Yemoja Ready each table with their respective data.

  • Thanks again @Paulo, but I still need to review some things... it’s still not what I imagined, I must be wanting to work wrong with the database. The idea was to take this data from the ticket table and organize it on a page and separate it into their respective events. I put the Event column in both to make this the common element. However, the events are logged dynamically, this is the need of foreach - I think - pro php see in Mysql how many html tables will have to create.

  • Good morning @Yemoja - I found a way and I think now you can solve your problem!

  • Let’s go continue this discussion in chat.

Show 2 more comments

0

I didn’t 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 don’t quite understand your question, but look if it satisfies you.

  • take a look at the other answers, maybe seeing them makes it clearer what I asked. But in short it would generate a table for each event and search in mysql the data for each event. The point is that has 2 tables to work.

  • for every new event you add to the main table you would want a new table only for that event? That’s it?

  • And then when choosing such an event he would bring the data that is on the table of the event, right?

  • Regarding the creation of tables HTML is exactly that, generate one for each event registered in the table MySQL ( Table 2 ). And after that, the other table MySQL ( Table 1 ) will store data of people, telling which table and which event they chose, and with this data being printed in their respective table HTML.

  • I just posted more information, including the code.

0

Do with INNER JOIN would be the clearest and perhaps performative way to solve this problem, the query would be more or less that:

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

To do with foreach would be 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>
            ";
        }
    }
}

  • is right there, I commented in another answer another "problem" that is the repetition of HTML tables, and it was for each event to have its table and its data.

Browser other questions tagged php mysql

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