How to select in MYSQL, bringing together results

Asked

Viewed 38 times

0

I need to make a query that returns the amount similar to the below:

TIPO 1
unidade 1
unidade 2
unidade n

TIPO 2 
unidade 1
unidade 2
unidade n

The tables are like this:

tabela_tipo
id|tipo
1 |TIPO 1
2 |TIPO 2

tabela_unidades
id|idtipo|unidade
1 |  1   |unidade 1
2 |  1   |unidade 2
3 |  2   |unidade 1
4 |  2   |unidade 2

I’m using PHP and mysql, the way I know, is to make a foreach within the result, thus making several queries, but I believe that there must be some way to not need to make several queries, below as it is currently:

$result = $db->query( "SELECT * FROM tabela_tipos" );
$tipos = $result->fetchAll( PDO::FETCH_ASSOC );
foreach($tipos as $t){
    echo $t['tipo'].'<br/>';;
    $result = $db->query( "SELECT * FROM tabela_unidades WHERE idtipo = {$t['id']}" );
    $unidades = $result->fetchAll( PDO::FETCH_ASSOC );
    foreach($unidades as $u){
        echo $u['unidade'].'<br/>';
    }
}
php mysql select foreach

  • Make an INNER JOIN between table_type and table_units with table_type.id = table_units.idtype and sort the result by table_type.id and table_units.id which will result in a table with all the desired data.. How much view you do in PHP with proper break control.

  • but this way, it will bring a list with everything, right? how do I separate into blocks by type?

  • As I said earlier: "How much display form you do in PHP with proper break control."

1 answer

0

sort of like this

$result = $db->query( "SELECT * FROM tabela_tipos INNER JOIN tabela_unidades ON tabela_tipo.id = tabelas_unidades.idtipo" );
$tipos = $result->fetchAll( PDO::FETCH_ASSOC );

with the array $guys you can do foreach, but basically you will have to do as you have done, but with a single call to the database (what if it is a system with MANY requests will be great for your server)

Browser other questions tagged php mysql select foreach

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