0

I am trying to group my data with country and year together and display it in a table but it is currently only grouped by year and not country. As below -

USA 2020 | Reise | Land | Reisedatnum | Dauer | Reise | | ------- | ------ | ------------- | ------- | ------- | |12324 | Ak | 23-10-22 | AF |intensive|

USA 2020 |Reise |Land |Reisedatnum |Dauer |Reise| |-|-|-|-|-| |12323 |AG |3-10-22 |AF| intensive|

But it should be shown in one table only as Country and year are the same, currently is showing in a different table. I also grouped by keyregion

In database

keyregion = countryname
Year =  $count=$row->jahr;

MySQL Query

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

        <caption class="date"><b><?php   echo $country;?>&nbsp;<?php echo $year; ?> </b></caption>
        <thead>
        <tr>
            <th scope="col" class="reisenr">Reise Nr.</th>
            <th scope="col" class="land">Land</th>
            <th scope="col" class="datum">Reisedatum</th>
            <th scope="col" class="dauer">Dauer</th>
            <th scope="col" class="reise">Reise</th>
            <th scope="col" class="preis">Reisepreis</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 $zeile->keycountry .'<a href="http://'. $zeile->Shortlink .'">'.'<br>' .  $zeile->Shortlink . '</a>'?></td>
            <td class="commdate"><?php echo $anfang ?> &ndash; <?php echo $ende ?></td>
            <td><?php echo $zeile->tage+1 ?>&nbsp;T</td>
            <td><?php echo $zeile->tourname ?></td>
            <td><?php echo ($zeile->price) ? $zeile->price."&nbsp;Euro" : "-" ?> </td>

        </tr>
        <?
    }


    // Datenbankverbindung
    $connection = @mysqli_connect("localhost","username","password","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 = "Afrika";
        $zeig = 1;
        if ($keysuffix == "")
            $where = "WHERE keyregion LIKE \"".$keywords."\" AND zeig='$zeig'" ;
        else
            $where = "WHERE ( keyregion LIKE \"".$keywords."\" OR keycountry LIKE \"".$keywords.$keysuffix."\" )";

        $abfrage =  "SELECT reisenr,beginn,ende,airline,status,price,keywords,keyregion,Shortlink,keycountry,tourname, YEAR(beginn) AS jahr, MONTH(beginn) AS month, 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, keyregion";

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

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

        while($row = mysqli_fetch_object($ergebnis))
        {
            if ($row->month != $p_month)
            {
                $p_month=$row->month;
                $datee= htmlentities($row->beginn,ENT_COMPAT,'UTF-8',0);
                $count=$row->jahr;
                $country= $row->keyregion;
                $bigin =htmlentities($row->beginn,ENT_COMPAT,'UTF-8',0);

                if ($opentab)
                    closetable();
                starttable($count,$country);
                $opentab = true;
            }

            ausgabe_einfach($row);
        }
        if ($opentab)
            closetable();
    }
    else
    { ?>
        Daten können nicht geladen werden.
        <?
    } 
earl3s
  • 2,393
  • 1
  • 23
  • 24
  • Can you include your table data in actual _tabular_ format rather than free-flow text? – Tim Biegeleisen May 07 '21 at 06:46
  • @TimBiegeleisen I have updated my Code. –  May 07 '21 at 06:46
  • Let's try again: see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query – Strawberry May 07 '21 at 06:59
  • 3
    **Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) – Dharman May 07 '21 at 08:53

0 Answers0