PHP: Line break is not working in data table using MYSQL?

Question

Hi I have data in my MYSQL which I am trying to sort by Months and Year together and display it in new HTML table every time sort by Year and months.

trying to do like this -

January 2021
Reise Nr.   Reisedatum  Dauer   Flug    Reis
2122330 Di, 1. jan – Do, 24. jan 2022   23 T    AF  Mali Intensiv

February 2021
Reise Nr.   Reisedatum  Dauer   Flug    Reis
2122330 Di, 1. Feb – Do, 24. Feb 2022   23 T    AF  Mali Intensiv

(Months wise data)

But HTML table is sorted by year only not months. see below the output.

But I am trying to make it like this January 2021 data is different table and OKT = October 2021 data in new table like below image

Here is my PHP CODE -

    <?php
 
    $tabelle="Termine_afrika";

    $keysuffix="";

    $status = array('freie&nbsp;Plätze','ausgebucht','nicht mehr buchbar','Restplätze','auf Anfrage','entfällt');

    function starttable($year,$month)
    {
        ?>
    <table class="termine" >

        <caption><?php echo date("F",strtotime($month)); ?> <?php echo $year ?></caption>
        <thead>
        <tr>
            <th scope="col" class="reisenr">Reise Nr.</th>
            <th scope="col" class="datum">Reisedatum</th>
            <th scope="col" class="dauer">Dauer</th>
            <th scope="col" class="flug">Flug</th>
            <th scope="col" class="reise">Reise</th>
            <th scope="col" class="preis">Reisepreis</th>
            <th scope="col" class="status">Status</th>
        </tr>
        </thead>

        <tbody>
        <?
    }

    function closetable()
    {
        echo "
        </tbody>

    </table>
";
    }

    function ausgabe_einfach($zeile)
    {
        global $status;
        $anfang = htmlentities($zeile->beginn_f,ENT_COMPAT,'UTF-8',0);
        $ende   = htmlentities($zeile->ende_f  ,ENT_COMPAT,'UTF-8',0);
        $commen = ($zeile->comment_de) ? "<div class=\"comment\">". nl2br(htmlentities($zeile->comment_de,ENT_COMPAT,'UTF-8',0)) ."</div>" : "";
        ?>
        <tr>
            <td><?php echo $zeile->reisenr ?></td>
            <td><?php echo $anfang ?> &ndash; <?php echo $ende . $commen ?></td>
            <td><?php echo $zeile->tage ?>&nbsp;T</td>
            <td><?php echo $zeile->airline ?></td>
            <td><?php echo $zeile->tourname ?></td>
            <td><?php echo ($zeile->price) ? $zeile->price."&nbsp;Euro" : "-" ?> </td>
            <td><?php echo $status[$zeile->status] ?></td>
        </tr>
        <?
    }

 
    $connection = @mysqli_connect("localhost","user","pass","DB");
    mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
    if ($connection)
    {
        mysqli_set_charset($connection, "UTF8");
        mysqli_query($connection, "SET lc_time_names = 'de_DE'");

        $keywords = "mali";
        $zeig = 1;
        if ($keysuffix == "")
            $where = "WHERE keycountry LIKE \"".$keywords."\" AND zeig='$zeig'" ;
        else
            $where = "WHERE ( keycountry LIKE \"".$keywords."\" OR keycountry LIKE \"".$keywords.$keysuffix."\" )";

        $abfrage = "SELECT reisenr,beginn,ende,airline,status,price,keywords,keycountry,tourname, YEAR(beginn) AS jahr, DATE_FORMAT(beginn,\"%a, %e.&nbsp;%b\") AS beginn_f,DATE_FORMAT(ende,\"%a, %e.&nbsp;%b %Y\") AS ende_f,comment_de, DATEDIFF(ende,beginn) as tage FROM $tabelle $where ORDER BY jahr, beginn";


        $ergebnis = mysqli_query($connection, $abfrage);

        $opentab = false;        // Tabelle offen
        $count=0;                // Aktueller Jahresdurchlauf

        while($row = mysqli_fetch_object($ergebnis))
        {
            if (intval($row->jahr) > $count)
            {
                $count=$row->jahr;
                $bigin = $row->beginn;
                if ($opentab)
                    closetable();
                starttable($count,$bigin);
                $opentab = true;
            }

            ausgabe_einfach($row);
        }

        if ($opentab)
            closetable();
    }
    else
    { ?>
        Daten können nicht geladen werden.
        <?
    }

?>
 

Answer 1

do comparison between months not years and get month from DB also break line and add new line only when month change

Update query

$abfrage = "SELECT reisenr,beginn,ende,airline,status,price,keywords,keycountry,tourname, YEAR(beginn) AS jahr, MONTH(beginn) AS month, DATE_FORMAT(beginn,\"%a, %e. %b\") AS beginn_f,DATE_FORMAT(ende,\"%a, %e. %b %Y\") AS ende_f,comment_de, DATEDIFF(ende,beginn) as tage FROM $tabelle $where ORDER BY jahr, MONTH(beginn)";

update code

$p_month=0;                
// Aktueller Jahresdurchlauf

        while($row = mysqli_fetch_object($ergebnis))
        {
            if ($row->month != $p_month)
            {
                $p_month=$row->month;
                $count=$row->jahr;
                $bigin = $row->beginn;
                if ($opentab)
                    closetable();
                starttable($count,$bigin);
                $opentab = true;
            }

            ausgabe_einfach($row);
        }