Showing multi row in one row

I am developing a page where I have to show all the jobs of an employee with a work number and the total of each type of hours in each work

I can display it, but, my problem is

when the employer works on the same job twice it displays the same job twice too

I have this:

JOB      | H1     | H2      | H3      | H4      | H5      | H6

O-2019-1 |   2.00 |    2.00 |    0.00 |    0.00 |    0.00 |    1.50        
O-2019-1 |   1.50 |    0.50 |    0.00 |    0.00 |    1.50 |    0.00

I want this:

JOB      | H1    | H2       | H3      | H4      | H5      | H6

O-2019-1 |  3.50 |     2.50 |    0.00 |    0.00 |    1.50 |    1.50

PHP CODE:

<?php 
    $stmt = $link -> prepare("SELECT * FROM rostosativos_funcionarios WHERE contribuinte = ?;");
    $stmt -> bind_param('s',
            $_GET['id']);
    $stmt -> execute();
    $resultado = $stmt -> get_result();
    $r = $resultado -> fetch_assoc();
    $id_funcionario = $r['id_empregado'];
    $stmt -> close();


    $sql = $link -> prepare("SELECT * FROM rostosativos_trabalhos WHERE id_funcionario = ?;");
    $sql -> bind_param('s',
            $id_funcionario);
    $sql -> execute();
    $resultado = $sql -> get_result();
    $sql -> close();

?>

HTML CODE:

<?php while($r = $resultado -> fetch_assoc()){ ?>
<section class="panel">
    <div class="panel-body">
        <div class="form-group">
            <div class="col-sm-8">
                <div class="row">
                    <label class="col-sm-2 control-label">Obra: </label>
                    <div class="col-sm-2">
                        <input type="text" value="<?php echo $r['id_obra']; ?>" class="form-control" disabled>
                    </div>
                </div>
            </div>
        </div>
        <div class="form-group">
            <div class="col-sm-8">
                <div class="row">
                    <label class="col-sm-2 control-label">Hora 1: </label>
                    <div class="col-sm-2">
                        <input type="text" value="<?php echo $r['hora_a']; ?>h" class="form-control" disabled>
                    </div>

                    <label class="col-sm-2 control-label">Hora 2: </label>
                    <div class="col-sm-2">
                        <input type="text" value="<?php echo $r['hora_b']; ?>h" class="form-control" disabled>
                    </div>

                    <label class="col-sm-2 control-label">Hora 3: </label>
                    <div class="col-sm-2">
                        <input type="text" value="<?php echo $r['hora_c']; ?>h" class="form-control" disabled>
                    </div>
                </div>
            </div>
        </div>
    </div>
</section>

Answer

You want to use group by for the first column and sum group function on the hours

Select job, sum(h1) h1, sum(h2) h2, ....
From ....
Where ....
Group by job

Advertisement

Answer