Child row with another table mysql - relationship with id master table

wmarrane

Hello,
This is my first development in PHP and I need help to develop a datatable that I need to connect with a another table. Below the main page with the main and child table:

Main Table Projetos:

Child Table prj_tasks

    <?php
    $pag = 'projetos';
    @session_start();
    require_once("../conexao.php");
    require_once('verificar-permissao.php');

    if (!isset($_SESSION['usuario_usuario'])) {

        header("Location:../index.php");
        exit();
    }
    ?>

    <a href="index.php?pagina=<?php echo $pag ?>&funcao=novo" type="button" class="btn btn-secondary mt-2">Novo projeto</a>
    <?php
    $query = $pdo->query("SELECT * FROM projetos ORDER BY status asc");
    $res = $query->fetchAll(PDO::FETCH_ASSOC);
    $total_reg = @count($res);
    if ($total_reg > 0) {

    ?>
    <div class="mt-4" style="margin-right:25px">
        <small>
            <table id="example" class="table table-striped my-2" style="width:100%">
                <thead>
                <tr>
                    <th></th>
                    <th>PPM</th>
                    <th>Nome</th>
                    <th>Status</th>
                    <th>Previsto</th>
                    <th>Realizado</th>
                    <th>Ações</th>
                </tr>
                </thead>
                <tbody>

                <?php
                for ($i = 0; $i < $total_reg; $i++) {
                foreach ($res[$i] as $key => $value) {
                }
                ?>

                <tr>
                    <td></td>
                    <td><?php echo $res[$i]['ppm'] ?></td>
                    <td><?php echo $res[$i]['nome'] ?></td>
                    <td><?php echo $res[$i]['Status'] ?></td>
                    <td>Previsto###</td>
                    <td>Realizado</td>
                    <td>
                        <a href="index.php?pagina=<?php echo $pag ?>&funcao=editar&id=<?php echo $res[$i]['id'] ?>" title="Editar registro">
                            <i class="bi bi-pencil-square text-primary" style="font-size: 1.3rem;"></i></a>

                        <a href="index.php?pagina=<?php echo $pag ?>&funcao=deletar&id=<?php echo $res[$i]['id'] ?>" title="Excluir registro">
                            <i class="bi bi-trash text-danger mx-2" style="font-size: 1.3rem;"></i></a>
                    </td>
                    <?php } ?>
                </tbody>
            </table>
        </small>
        <?php
        } else {
            echo "Não existe dados para serem exibidos !!";
        }
        ?>
    </div>

Below de JSON with datas of prj_task.

<?php
require_once("../../conexao.php");
@session_start();


$query_con_past = $pdo->prepare("select * from prj_task order by id_projeto, prioridade asc");
$query_con_past->execute();
$res_con_past = $query_con_past->fetchAll(PDO::FETCH_ASSOC);

$total_reg = count($res_con_past);

for ($i = 0; $i < $total_reg; $i++) {
    foreach ($res_con_past[$i] as $key => $value) {
    }
    $data[] =array( $res_con_past[$i]['id'],
        $res_con_past[$i]['descricao'],
        $res_con_past[$i]['percentual'],
        $res_con_past[$i]['data_inicio'],
        $res_con_past[$i]['data_fim'],
        $res_con_past[$i]['prioridade'],
        $res_con_past[$i]['id_projeto']);
}

echo json_encode($data);

?>

I tried to use tis example, but a I can't

https://datatables.net/examples/api/row_details.html

Now, I need show de child (prj_tasks table) below each projeto. How Can I do?

allan

I tried to use tis example, but a I can't

Are you getting an error or something else? That is certainly the example to start with. Then create a <table> in the child row and initialise it as a DataTable.

Allan

rf1234

That confused me, too. The child rows of the example don't have much in common with the parent - child relationship (1: N) in a relational data model.

I see you have projects and those projects have tasks. Strictly hierarchical. A task can only belong to one project and it cannot exist without a project. No link table required.

Take a look at this please: (Parent: sites, child: users)
https://datatables.net/blog/2016-03-25#DataTables-Javascript
https://editor.datatables.net/examples/advanced/parentChild

You can also do parent / child editing in a child row:
https://datatables.net/blog/2019-01-11

rf1234

Now, I need show de child (prj_tasks table) below each projeto. How Can I do?

I think this link really has all you need. If you're not using Editor just skip that part.
https://datatables.net/blog/2019-01-11

wmarrane

Allan,
Do you have some example? Do you have step-by-step?

I read the article, but I didn't understand.

colin

Taking your first example, you would need to make another server-side call using ajax in that format() function to get the data for that child table, then as Allan said, create that table with the returned data.

Colin