Creating an interactive archives section for your website can enhance user experience by organizing content and making it easily accessible. In this guide, we'll walk through the steps to implement an archives section with expand/collapse functionality using PHP, MySQL, and Bootstrap.

Step 1: Set Up Your Database and Connection

First, ensure you have a connection to your MySQL database. This example assumes you have a table named posts with fields id, published, and created_at.

<?php
global $conn;
// Your database connection code here
?>

Step 2: Create the Basic HTML Structure

Start with the HTML structure for the archives section. We'll use Bootstrap's accordion component for the collapsible elements.

<h2 class="section-title"><i class="fas fa-file-archive"></i> Archives
    <button id="toggle-expand-collapse-accordion" class="expand-all">Expand All ?</button>
</h2>
<div class="accordion accordion-flush" id="accordionFlushExample">
    <!-- PHP code will dynamically generate accordion items here -->
</div>

Step 3: Fetch Data and Generate Accordion Items

Use PHP to fetch data from your database and generate the accordion items for each year. We'll count the number of published posts for each year and group them by month.

<?php
$currentYear = date('Y');
$startYear = 2016; // Adjust the start year as needed

for ($year = $currentYear + 1; $year >= $startYear; $year--) {
    $sql = "SELECT COUNT(id) AS 'total' FROM posts WHERE published = 1 AND DATE_FORMAT(created_at, '%Y') = $year";
    $result = mysqli_query($conn, $sql);
    $count = mysqli_fetch_assoc($result);
    ?>
    <div class="accordion-item">
        <div class="accordion-header" id="flush-heading<?php echo $year; ?>">
            <button class="accordion-button collapsed" type="button" data-bs-toggle="collapse" data-bs-target="#flush-collapse<?php echo $year; ?>" aria-expanded="false" aria-controls="flush-collapse<?php echo $year; ?>">
                <i class="fas fa-calendar-alt"></i>  Year <?php echo $year; ?> <span class="text"> (<?php echo $count['total']; ?>)</span>
            </button>
        </div>
        <div id="flush-collapse<?php echo $year; ?>" class="accordion-collapse collapse" aria-labelledby="flush-heading<?php echo $year; ?>" data-bs-parent="#accordionFlushExample">
            <div class="accordion-body">
                <?php
                $query = "SELECT DATE_FORMAT(created_at, '%M %Y') AS 'archive', DATE_FORMAT(created_at, '%m') AS 'm', DATE_FORMAT(created_at, '%Y') AS 'y', COUNT(id) AS 'total' 
                          FROM posts WHERE published = 1 AND DATE_FORMAT(created_at, '%Y') = $year 
                          GROUP BY DATE_FORMAT(created_at, '%Y%M') ORDER BY created_at DESC";
                $archives = mysqli_query($conn, $query);
                if ($archives->num_rows > 0) {
                    while ($row = mysqli_fetch_assoc($archives)) {
                        ?>
                        <div class="month">
                            <a data='m=<?php echo $row['m']; ?>&y=<?php echo $row['y']; ?>' href='<?php echo BASE_URL; ?>archivesview.php?m=<?php echo $row['m']; ?>&y=<?php echo $row['y']; ?>' class='list-group-item'>
                                <?php echo $row['archive']; ?> <span class='text'>(<?php echo $row['total']; ?>)</span>
                            </a>
                        </div>
                        <?php
                    }
                } else {
                    echo "No archives data found yet in $year.";
                }
                ?>
            </div>
        </div>
    </div>
    <?php
}
?>

Step 4: Add Expand/Collapse Functionality with JavaScript

To allow users to expand or collapse all accordion items at once, add the following JavaScript code:

document.getElementById('toggle-expand-collapse-accordion').addEventListener('click', function() {
    const allExpanded = Array.from(document.querySelectorAll('.accordion-collapse')).every(item => item.classList.contains('show'));
    const action = allExpanded ? 'collapse' : 'expand';
    
    document.querySelectorAll('.accordion-collapse').forEach(item => {
      const bsCollapse = new bootstrap.Collapse(item, {
        toggle: false
      });
      if (action === 'expand') {
        bsCollapse.show();
      } else {
        bsCollapse.hide();
      }
    });
    
    this.textContent = action === 'expand' ? 'Collapse All ?' : 'Expand All ?';
  });

  function checkAllCollapsibles() {
    const allExpanded = Array.from(document.querySelectorAll('.accordion-collapse')).every(item => item.classList.contains('show'));
    document.getElementById('toggle-expand-collapse-accordion').textContent = allExpanded ? 'Collapse All ?' : 'Expand All ?';
  }

  document.querySelectorAll('.accordion-collapse').forEach(item => {
    item.addEventListener('shown.bs.collapse', checkAllCollapsibles);
    item.addEventListener('hidden.bs.collapse', checkAllCollapsibles);
  });

  checkAllCollapsibles();

Conclusion

By following these steps, you can create an interactive archives section for your website, allowing users to easily navigate through your posts by year and month. The expand/collapse functionality enhances user experience by providing a clean and organized view of your content.