0

I've been trying to code to create the following tables shown in the figure. I need to generate tables for each month but the thing is for 1 month this code generates 3 tables. I only need 1 table for each month. Please if anyone can help me with the code that would be a greate help.

Thanks in advance!

function JobRaised(){
    include '../dbc.php';
    echo "<br><br>";
    echo "<div class='row'><div class='col-sm-6'><h3>Based on Origin</h3>";

    $queryt = "SELECT DISTINCT month, Category, Parcels, LoadParcels FROM mDraftProfileLM WHERE month > 202001 ORDER BY month DESC";
    $stmt = sqlsrv_query($conn, $queryt);
    if ($stmt === false) {
        die(print_r(sqlsrv_errors(), true));
    }

    while ($row1 = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
        $month = $row1['month'];
        $category = $row1['Category'];
        $forecast = $row1['Parcels'];
        $actual = $row1['LoadParcels'];
        $percentage = $row1['Percentage'];

        // Start a new table
        echo "<table class='table table-bordered'>
            <thead>
                <tr class='info'>
                    <th style='background-color: light blue; color: black;' colspan='5'>" . $month . "</th>
                </tr>
                <tr>
                    <th></th>
                    <th>A</th>
                    <th>B</th>
                    <th>C</th>
                    <th>Total</th>
                </tr>
            </thead>
            <tbody>";

        // Add the row for forecast
        echo "<tr>
            <td>Forecast</td>
            <td>" . $forecast . "</td>
            <td>" . $forecast . "</td>
            <td>" . $forecast . "</td>
            <td>" . $forecast . "</td>
        </tr>";

        // Add the row for actual
        echo "<tr>
            <td>Actual</td>
            <td>" . $actual . "</td>
            <td>" . $actual . "</td>
            <td>" . $actual . "</td>
            <td>" . $actual . "</td>
        </tr>";

        // Add the row for percentage
        echo "<tr>
            <td>%</td>
            <td>" . $percentage . "</td>
            <td>" . $percentage . "</td>
            <td>" . $percentage . "</td>
            <td>" . $percentage . "</td>
        </tr>";

        // Close the table
        echo "</tbody></table><br>";
    }

    echo "</div><div class='col-sm-6'></div></div>";
}

enter image description here

Barmar
  • 741,623
  • 53
  • 500
  • 612
Wathsala Heenkende
  • 15
  • 5
  • 1
    Presumably you're ending up with multiple tables for each month because you have more than 1 row, per month, being returned from your query. If, indeed, you only want a single row per month then you will probably need to aggregate monthly values in your query. (ie. SUM() the Category, Parcels and LoadParcels values, grouping by the month – Craig Jun 28 '23 at 23:33
  • 1
    Your query returns a row for each different value of *all* the columns you're selecting, not a row for each month. You should start a new table only when the month changes, not for every row. – Barmar Jun 28 '23 at 23:35
  • @Barmar Thanks so much. Would it be possible to give me the updated code as I'm bit struggle to do it my-self, please? – Wathsala Heenkende Jun 28 '23 at 23:57
  • We're not a code-writing service. I gave you code that shows the general pattern, you should try adapting that to your specific needs. If you can't get it working, post what you tried and we'll help you fix it. – Barmar Jun 28 '23 at 23:59

0 Answers0