MySQL PHP Dynamic table

Question

I have a MySQL Data table and I want to get out the datas sum by month and sum by wood type. I get the total sum but I dont get for wood types.

Hear is my MySQL table:

furesz:

wood	cubic	date	machine
A-001	0,25	2022-02-01 18:13:12	mebor1
A-003	0,35	2022-02-01 18:13:12	mebor1

My code:

        <div class="col-sm">
            <table class="table">
                <thead>
                    <tr>
                    <th scope="col">Hónap</th>
                    <th scope="col">Nyár</th>
                    <th scope="col">Tölgy</th>
                    <th scope="col">VTölgy</th>
                    <th scope="col">Cser</th>
                    <th scope="col">SUM</th>
                    </tr>
                </thead>
                <tbody>
                <?php
                    $sql_list = 
                    "SELECT MONTH(date) AS month,   
                    COUNT(DISTINCT DATE(date)) AS work_days, 
                    SUM(IF(cubic='A-001',1,0)) sum_nyar,
                    SUM(IF(cubic='A-003',1,0)) sum_tölgy,
                    SUM(IF(cubic='A-004',1,0)) sum_vtölgy,
                    SUM(IF(cubic='A-018',1,0)) AS sum_cser,
                    SUM(cubic) AS sum_full
                    FROM furesz 
                    WHERE machine='mebor2'
                    GROUP BY month";
                    $result_list = mysqli_query($conn, $sql_list);
                    while($row = mysqli_fetch_assoc($result_list)) {  
                        ?>
                    <tr>
                    <th scope="row"><?=$row['month']?></th>
                    <td><?=number_format ($row['sum_nyar'],1)?>&nbsp;m3</td>
                    <td><?=number_format ($row['sum_tölgy'],1)?>&nbsp;m3</td>
                    <td><?=number_format ($row['sum_vtölgy'],1)?>&nbsp;m3</td>
                    <td><?=number_format ($row['sum_cser'],1)?>&nbsp;m3</td>
                    <th><?=number_format ($row['sum_full'],1)?>&nbsp;m3</th>
                    <?php
                }
                ?>       
                </tbody>
            </table>
        </div>

This is the output I get:

month	nyár	tölgy	VTölgy	Cser	SUM
2	0.0 m3	0.0 m3	0.0 m3	0.0 m3	0.6 m3

This is what I'am looking for:

Month	Nyár	Tölgy	VTölgy	Cser	SUM
2	0.25 m3	0.35 m3	0.0 m3	0.0 m3	0.6 m3

My code Do you need in SQL code? if so then remove PHP code part. And explain how the output is obtained from shown source data. PS. The relation between source wood value and according name ('A-001' => nyar) should be stored in dictionary table, not hardcoded. — Akina
– Akina, Commented Feb 2, 2022 at 9:46
You are testing for mebor2 in the query but your table only contains mebor1 , or your sample data is not representative and does not feed to your desired result — P.Salmon
– P.Salmon, Commented Feb 2, 2022 at 9:52

Niek · Accepted Answer · 2022-02-02 09:53:39Z

1

The following code would work. You mixed up the wood and cubic column. So you want to sum the cubic if wood equals a string.

SELECT 
    MONTH(date) AS month,
    COUNT(DISTINCT DATE(date)) AS work_days,
    SUM(IF(wood='A-001',cubic,0)) sum_nyar,
    SUM(IF(wood='A-003',cubic,0)) sum_tölgy,
    SUM(IF(wood='A-004',cubic,0)) sum_vtölgy,
    SUM(IF(wood='A-018',cubic,0)) AS sum_cser,
    SUM(cubic) AS sum_full
  FROM furesz 
  WHERE machine='mebor2'
  GROUP BY month

answered Feb 2, 2022 at 9:53

Niek

265 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL PHP Dynamic table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related