Collect all rows in one year without repeating year

user_11689028 0
Viewed 69

I would like to have this output:

2018
event1
event2

2019
event3
event4

The output of my code:

2018
event1
2018
event1
2019
event3
2019
event4

How could I achieve this without function GROUP_CONCAT() and repeating year?

My code:

while ($myrow = mysqli_fetch_array($result)) {
    $var = $myrow['event_date'];
    echo" 
    " . date("Y", strtotime($var)) . "
    " . date("d.m.Y", strtotime($var)) ." 
    "?> 
    <a class="link" href="article.php?event_id=<?php echo

    $myrow['event_id']; ?>"><code><?php echo $myrow['article_title']; ?></a> 
    <a class="link" href="speaker.php?speaker_id=<?php echo
    $myrow['speaker_id']; ?>"><?php
    echo" 
    ".$myrow['speaker_degree']." 
    ".$myrow['speaker_name']." 
    ".$myrow['speaker_surname']." 
    ".$myrow['speaker_patronymie']." 
    </a></b></p> 
    ";
}

My query:

SELECT * 
FROM event_t  
JOIN event_speaker_article_t ON event_t.event_id=event_speaker_article_t.event_id 
JOIN article_t ON event_speaker_article_t.article_id=article_t.article_id 
JOIN speaker_t ON event_speaker_article_t.speaker_id=speaker_t.speaker_id 
ORDER by event_date
mysqli mysql php
1 Answers

You can try putting it to an array:

<?php
    // Your query
    $sel = "SELECT * 
    FROM event_t  
    JOIN event_speaker_article_t ON event_t.event_id=event_speaker_article_t.event_id 
    JOIN article_t ON event_speaker_article_t.article_id=article_t.article_id 
    JOIN speaker_t ON event_speaker_article_t.speaker_id=speaker_t.speaker_id 
    ORDER by event_date";

    $result = mysqli_query($con,$sel)

    $arrData = array();
    $Count = 0;
    $OldYear = "";

    while($row = mysqli_fetch_array($result))
    {
        $FullDate = $myrow['event_date']; 
        $Year = date("Y", strtotime($FullDate));
     
     if($OldYear == "" || $OldYear != $Year)
     {
      $OldYear = $Year;
      $Count = 0;
     }
     
     $arrData[$Year][$Count]['event_id'] = $row['event_id'];
     $arrData[$Year][$Count]['article_title'] = $row['article_title'];
     $arrData[$Year][$Count]['speaker_id'] = $row['speaker_id'];
     $arrData[$Year][$Count]['speaker_degree'] = $row['speaker_degree'];
     $arrData[$Year][$Count]['speaker_name'] = $row['speaker_name'];
     $arrData[$Year][$Count]['speaker_surname'] = $row['speaker_surname'];
     $arrData[$Year][$Count]['speaker_patronymie'] = $row['speaker_patronymie'];
     
     $Count++;
    }
?>

HTML Part

 <?php
    foreach($arrData as $Year => $year)
    {
    ?>
     <h4> <?php echo $Year . "<br />"; ?> </h4>

     <?php
     for($event = 0; $event < $arrData[$Year].length; $event++)
     {
     ?>
      <a class="link" href="article.php?event_id=<?php echo $arrData[$Year][$event]['event_id']; ?>"><?php echo $arrData[$Year][$event]['article_title']; ?></a> 
      <a class="link" href="speaker.php?speaker_id=<?php echo $arrData[$Year][$event]['speaker_id']; ?>"><?php echo $arrData[$Year][$event]['speaker_degree' ] ."  ". $arrData[$Year][$event]['speaker_name'] ."  ". $arrData[$Year][$event]['speaker_surname'] ."  ". $arrData[$Year][$event]['speaker_patronymie']; ?></a>
     <?php
     }
    }
    ?>

user_87233350
Related