How to group items by alphabet into two columns without code repetition

Question

In my songbook php project, I'm trying to categorized and display all the songs from mysql database like this in my Homepage.

A                         M
All the way               North start
                          No, please

B                         N                     
Bought to you
Bell Bell

C
Cinderalla

..until L               ..until Z

So each side will display 13 alphabets.

I was able to display it like this but I'm repeating myself 26 time for all the alphabet so I'm just wondering if anybody can give me suggestion on away to not repeat myself.

//Currently I copied this 26 time and replaced 'A%' to different letter and also $songTitle variable to different variable. 

<?php
  require_once 'config.php';
  //$sql = 'SELECT id, title FROM song ORDER BY title ASC;';
  $sql = "SELECT id, title, chord FROM song Where title like 'A%'";
  $stmt = $conn->prepare($sql);
  $stmt->execute(['title' => $title]);
  
  // fetch all rows
  $songTitle = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>

Currently I show only one group in each side but in my project I have 13 on each side for other alphabets.

<div class="column">
      <div class="table-wrapper">
            <table class="fl-table">
                  <tr>
                  <th style="color:black; width:80%">A</th>
                    <th style=" color:white;"></th>
                  </tr>
                  <?php // display the song title and chord  
                    foreach ($songTitleA as $song) {
                    echo "<tr>";
                    echo "<td><a style='margin-left:10px' href='details.php?id={$song['id']}'>{$song['title']} <br> </a></td>";
                    echo "<td><a style='margin-left:10px'>{$song['chord']}   <br> </a></td>";
                    } ?>
                </tr>
              </table>
        </div>
      </div>
      <div class="column">
      <div class="table-wrapper">
            <table class="fl-table">
                  <tr>
                  <th style="color:black; width:80%">M</th>
                    <th style=" color:white;"></th>
                  </tr>
                  <?php // display the song title and chord  
                    foreach ($songTitleM as $song) {
                    echo "<tr>";
                    echo "<td><a style='margin-left:10px' href='details.php?id={$song['id']}'>{$song['title']} <br> </a></td>";
                    echo "<td><a style='margin-left:10px'>{$song['chord']}   <br> </a></td>";
                    } ?>
                </tr>
              </table>
        </div>
      </div>
    </div>

Answer 1

OK, so here goes the full works. Written to be educational. First, let's get your sample data. You would get this by selecting all the songs in your the database, ordered by title ASC (as in the query you've commented out):

$sql = 'SELECT id, title, chords FROM song ORDER BY title ASC;';
$stmt = $conn->prepare($sql);
$stmt->execute();
$songs = $stmt->fetchAll(PDO::FETCH_ASSOC);

I'm using a top-50 all time list for sample data since I don't have your database handy:

$songs = [
    ['title' => 'A Change Is Gonna Come'],
    ['title' => 'A Day In The Life'],
    ['title' => 'Be My Baby'],
    ['title' => 'Best Of My Love'],
    ['title' => 'Billie Jean'],
    ['title' => 'Bohemian Rhapsody'],
    ['title' => 'Born To Run'],
    ['title' => 'Bridge Over Troubled Water'],
    ['title' => 'Creep'],
    ['title' => 'Dancing In The Street'],
    ['title' => 'Dancing Queen'],
    ['title' => 'Every Breath You Take'],
    ['title' => 'Family Affair'],
    ['title' => 'Gimme Shelter'],
    ['title' => 'God Only Knows'],
    ['title' => 'God Save The Queen'],
    ['title' => 'Good Vibrations'],
    ['title' => 'Hallelujah'],
    ['title' => 'Heartbreak Hotel'],
    ['title' => 'Hey Jude'],
    ['title' => 'Hotel California'],
    ['title' => 'I Cant Get No Satisfaction'],
    ['title' => 'I Will Always Love You'],
    ['title' => 'Imagine'],
    ['title' => 'Jonny B Good'],
    ['title' => 'Life On Mars?'],
    ['title' => 'Like A Rolling Stone'],
    ['title' => 'Live Forever'],
    ['title' => 'London Calling'],
    ['title' => 'My Generation'],
    ['title' => 'No Woman No Cry'],
    ['title' => 'One'],
    ['title' => 'Over The Rainbow'],
    ['title' => 'Papas Got A Brand New Bag'],
    ['title' => 'Purple Haze'],
    ['title' => 'Respect'],
    ['title' => 'River Deep Mountain High'],
    ['title' => 'Smells Like Teen Spirit'],
    ['title' => 'Stairway To Heaven'],
    ['title' => 'Stand By Me'],
    ['title' => 'Sultans Of Swing'],
    ['title' => 'Sweet Child O Mine'],
    ['title' => 'The Twist'],
    ['title' => 'Waterloo Sunset'],
    ['title' => 'Whatd I Say'],
    ['title' => 'Whats Goin On'],
    ['title' => 'When Doves Cry'],
    ['title' => 'Yesterday'],
    ['title' => 'Your Song'],
    ['title' => 'Youve Lost That Lovin Feeling'],
];

Then, we need to get them organized by initial letter.

// Group the songs by initial letter:

$grouped = [];

foreach($songs as $song) {

    // Get the first letter
    $initial = $song['title'][0];

    // Add to the grouping array:
    $grouped[$initial][] = $song;
}

Now we have a full list of songs in $grouped['A'], $grouped['B'] ..., obviously excluding any letters that don't have a matching song. Let's divide them into two separate arrays for your columns (with equal number of index letters, or +1 in the first):

// Divide into two sections (as number of index letters / 2):

list($left, $right) = array_chunk($grouped, ceil(count($grouped) / 2), true);

You could also split them into equal length by number of songs, however that'd be a bit more complicated. We don't want to repeat anything, so we need a function to output a column:

// Function to output column:

function print_songs(array $songs): string {
    
    $html = '';
    foreach($songs as $letter => $songsByLetter) {
        $html .= "\n" . $letter . "\n";
        foreach($songsByLetter as $song) {
            $html .= $song['title'] . "\n";
        }
    }
    return $html;
}

First we got the index letter, then looped through the titles. Now let's output our song index:

// Here's how we call the function:

$left_column = print_songs($left);
$right_column = print_songs($right);

// Then we output the ready song columns (add HTML as necessary):

echo <<<SONGS
--------
{$left_column}
--------
{$right_column}
--------
SONGS;

And we get a tidy list of songs in two columns:

--------

A
A Change Is Gonna Come
A Day In The Life

B
Be My Baby
Best Of My Love
Billie Jean
Bohemian Rhapsody
Born To Run
Bridge Over Troubled Water

C
Creep

D
Dancing In The Street
Dancing Queen
...

As noted, this produces plain text output only -- to keep focus on the code itself. Add in your HTML as necessary. Demo: https://3v4l.org/iSPCJ