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

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

wmarranewmarrane Posts: 2Questions: 1Answers: 0

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?

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 61,972Questions: 1Answers: 10,160 Site admin
    edited March 2022

    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

  • rf1234rf1234 Posts: 2,809Questions: 85Answers: 406
    edited March 2022

    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

  • rf1234rf1234 Posts: 2,809Questions: 85Answers: 406

    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

  • wmarranewmarrane Posts: 2Questions: 1Answers: 0

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

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

  • colincolin Posts: 15,177Questions: 1Answers: 2,590
    Answer ✓

    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

Sign In or Register to comment.