Pull results from two Mysql tables with PHP class

Question

I created a class for dynamic generation of tables with results coming from Mysql. The syntax I use after instantiating the class is as follows:

$table->topo('Menu:','menu','menu');
$table->Corpo('menu_sub','ID,Nome,Menu,Icones','id,nome,id_menu,icone','','','','id_menu,icone','menu','id','nome,nome');

The last fields refer to a query in a second Mysql table

The name field works perfectly however as you can observe the field marked in red this pulling the other fields of the main table, I solved this by modifying this part of the code

if ($v == $z) {
    echo 'Acho ->'.$z;
}else{
    echo 'p';   //MUDEI ESTA PARTE AQUI DO CODIGO
}

}

Well now that comes my biggest problem I want to consult on more than one table.

I figured I’d settle by separating the fields by (Tipo: id_menu,icone) what happens I show in the following image

$table->topo('Menu:','menu','menu');
$table->Corpo('menu_sub','ID,Nome,Menu,Icones','id,nome,id_menu,icone','','','','id_menu,icone','menuicones','id,icone','menu,icone');

Summarizing with only a single field I can pull the results of the second table but if I try to pull the result of more than one table I have the problem shown in the image above, I have duplicated result.

Follows the class responsible for creating the dynamic table:

PS. This class is without the SQL responsible for select second table

<?php
class Tabela{
    /*
     * TOPO BARRA DE NAVEGAÇÃO DA TABELA MASTER
     */
    function topo($nome,$modulo,$arquivo){
        echo '
        <div style="padding:0 10px;"><div  style="border-bottom: solid 3px #297ACC">
        <table width="100%" border="0" cellspacing="0" cellpadding="0">
            <tr>
                <td width="92%"><strong>'.$nome.'</strong></td>
                <td width="8%" align="right"><ul class="nav  span7" style="margin-bottom:0px;">
                        <li class="pull-right"><a href="?pg=modulos/'.$modulo.'/list_'.$arquivo.'">Listar</a></li>
                        <li class="pull-right"><a href="?pg=modulos/'.$modulo.'/add_'.$arquivo.'">Cadastrar</a></li>
                    </ul></td>
            </tr>
            </table>
        </div>
        <br />
            ';
    }
    /*
     * GERADOR AUTOMATICO DE TABELA
     * ESTA FUNÇÃO IRA GERA UMA SQL ONDE OS DADOS VIRÃO DINAMICAMENTE
     */
    function Corpo($db,$th,$campos,$where,$modulos,$files,$_campo,$_tb,$_where,$_valor){
        $pag = "$_GET[pag]";
        if($pag >=  '1'){
            $pag = $pag;
        }else{
            $pag = '1';
        }
        $maximo = '10';
        $inicio = ($pag * $maximo) - $maximo;
        $sql = mysql_query("SELECT * FROM $db $where LIMIT $inicio, $maximo");
        /*
         * AQUI FICA O CABEÇALHO DA TABELA
         */
        echo
        '<table border="1" width="100%" >
            <tr>
            ';
        $v = explode(',',$th);;
        foreach($v as $x){
            echo '<th>'.ucfirst($x).'</th>';   
        }
        /*
         * AQUI VAI O LOOP DA TABELA
         */
        echo '
                <th colspan="2">Ações</th>
            </tr>
            <tr>';
        while($dados = mysql_fetch_array($sql)){   
            foreach(explode(',',$campos) as $v) {   
                $x = $dados[$v];
                $d = explode(',', $_campo);
                echo '<td>';
            foreach ($d as $z) {
                    if ($v == $z) {
                        echo 'Acho ->'.$z;
                        }else{
                        }
                    }
                echo 'p';
                echo '</td>';
                }

            echo '
                <td width="1"><a href="?pg=modulos/'.$modulos.'/edit_'.$files.'&id='.$dados[id].'" class="fa fa-edit"></a></td>
                <td width="1"><a href="?pg=modulos/'.$modulos.'/dell_'.$files.'&id='.$dados[id].'" class="fa fa-close" style="color:#FF0000;"></a> </td>
            </tr>';
        }
        echo '
        </table>';

        /*
         * PAGINAÇÃO COM NUMERAÇÃO
         */
        echo '<ul class="pagination">';
        $sql_res = mysql_query("SELECT * FROM $db $where");
        $total = mysql_num_rows($sql_res);
        $paginas = ceil($total/$maximo);
        $links = '5';
        echo "<li><a href=\"?pg=modulos/$modulos/list_$files&pag=1\">«</a></li>";
        for ($i = $pag-$links; $i <= $pag-1; $i++){
            if($i >= 0){
                echo "<li><a href=\"?pg=modulos/$modulos/list_$files&pag=$i\">$i</a></li>";
            }
        }

        echo '<li class="disabled"><a href="#">'.$pag.'</a></li>';
        for($i = $pag +1; $i <= $pag+$links; $i++){
            if($i > $paginas){
            }  else {
                echo "<li><a href=\"?pg=modulos/$modulos/list_$files&pag=$i\">$i</a></li>";
            }
        }

        echo "<li><a href=\"?pg=modulos/$modulos/list_$files&pag=$paginas\">»</a></li>";    
    }
}

Here I leave the link to the project I put in GIT.

Answer 1

Let’s focus on your main code, which generates the table loop.

Tag error

Right at the beginning, you print a <tr>. But it’s before the while, then the tag will be missing for the next lines.

That:

echo '
        <th colspan="2">Ações</th>
    </tr>
    <tr>';
while($dados = mysql_fetch_array($sql)){   
    ...

Should be:

//finaliza o cabeçalho
echo '
        <th colspan="2">Ações</th>
    </tr>'; 
while($dados = mysql_fetch_array($sql)){   
    //define o início de cada linha
    echo '<tr>'; 
    ....

Recovering Fields from Multiple Tables

Other than that, there should be no distinction for you to retrieve ("pull") data from different tables. When you select fields in a command SELECT, the returned record does not differentiate by table.

What I mean is that it doesn’t matter which table the field belongs to, as long as it’s in the SELECT.

See the following examples:

select tab1.campo1, tab1.campo2, tab1.campo3 from tab1, tab 2 where ...

select tab1.campo1, tab1.campo2, tab1.campo3 from tab1 where ...

select tab1.campo1, tab1.campo2, tab3.campo3 from tab1, tab2, tab3 where ...

Regardless of what the query looks like, the returned array will be exactly the same, containing the keys campo1, campo2 and campo3.

Therefore, to print all values in the table, simply print the value of $x, where you have already recovered the values.

Finally, the loop would be just like this:

while($dados = mysql_fetch_array($sql)){   
    echo '<tr>';
    foreach(explode(',',$campos) as $v) {   
        $x = $dados[$v];
        echo '<td>'.$x.'</td>';
    }
    echo '
        <td width="1"><a href="?pg=modulos/'.$modulos.'/edit_'.$files.'&id='.$dados[id].'" class="fa fa-edit"></a></td>
        <td width="1"><a href="?pg=modulos/'.$modulos.'/dell_'.$files.'&id='.$dados[id].'" class="fa fa-close" style="color:#FF0000;"></a> </td>
    </tr>';
}

If you want to do some differentiated treatment for the case of the field being another table, then you can use the vector $d and check of $v is contained in it.