MySQL

Display records in columns

Demonstration

Here's an example of how to display MySQL 9 random database records in 3 Bootstrap columns.

I Will Always Love You
Whitney Houston (1992)

Believe
Cher (1998)

Barbie Girl
Aqua (1997)

Do You Really Want to Hurt Me
Culture Club (1982)

My Heart Will Go On
Celine Dion (1998)

Cotton Eye Joe
Rednex (1994)

In the Summertime
Mungo Jerry (1970)

Born to Be Alive
Patrick Hernandez (1979)

Sadeness
Enigma (1991)

PHP
<?php
  // Database connection
  $conn = new mysqli ("server", "user", "password", "database");
  mysqli_set_charset ($conn, "utf8");

if ($conn->connect_error) {
  die("Error: " . $conn->connect_error);
  }

// Fetch records
  $sql = "SELECT * FROM top_songs_eu ORDER BY song ASC LIMIT 9";
  $result = $conn->query($sql);

// Store records in an array
  $songs = [];
  if ($result->num_rows > 0) {
  while ($row = $result->fetch_assoc()) {
  $songs[] = "<div class='song-item'><strong>{$row['song']}</strong><br>{$row['artist']} ({$row['year']})</div>";
  }
  } else {
  echo "<p>No records found</p>";
  }

// Split into 3 columns
  $songs_list = array_chunk($songs, ceil(count($songs) / 3));

// Display in Bootstrap grid
  foreach ($songs_list as $column) {
  echo '<div class="col-md-4">';
  foreach ($column as $song) {
  echo "<div>$song</div>";
  }
  echo '</div>';
  }

$conn->close();
  ?>

Explanation of the Code

This PHP script connects to a MySQL database, fetches the top 9 songs from the top_songs_eu table, and displays them in three Bootstrap columns.

Database Connection
<?php
  // Database connection
  $conn = new mysqli ("server", "user", "password", "database");
  mysqli_set_charset ($conn, "utf8");

if ($conn->connect_error) {
  die("Error: " . $conn->connect_error);
  }

What the code above ↑ does:

  • Establishes a MySQL database connection using new mysqli().
  • Sets the character encoding to UTF-8 to support special characters.
  • Checks for connection errors and stops execution (die()) if an error occurs.
Fetching Records from the Database
$sql = "SELECT * FROM top_songs_eu ORDER BY song ASC LIMIT 9";
  $result = $conn->query($sql);

What the code above ↑ does:

  • Selects all columns (*) from the top_songs_eu table.
  • Orders results alphabetically by the song column (ORDER BY song ASC).
  • Limits the results to 9 records (LIMIT 9).
  • Runs the query using $conn->query($sql) and stores the result in $result.
Storing Records in an Array
$songs = [];
if ($result->num_rows > 0) {
  while ($row = $result->fetch_assoc()) {
  $songs[] = "<div class='song-item'><strong>{$row['song']}</strong><br>{$row['artist']} ({$row['year']})</div>";
  }
  } else {
  echo "<p>No records found</p>";
}

What the code above ↑ does:

  • Checks if records exist ($result->num_rows > 0).
  • Loops through the results using while ($row = $result->fetch_assoc()).
  • Formats each record into an HTML <div> and stores it in the $songs array.
  • If no records are found, it displays "No records found".
Splitting Songs into 3 Columns
$songs_list = array_chunk($songs, ceil(count($songs) / 3));

What the code above ↑ does:

  • Divides $songs into 3 groups using array_chunk().
  • Uses ceil(count($songs) / 3) to evenly distribute songs across three columns.
Displaying Songs in Bootstrap Columns
foreach ($songs_list as $column) {
  echo '<div class="col-md-4">';
  foreach ($column as $song) {
  echo "<div>$song</div>";
  }
  echo '</div>';
  }

What the code above ↑ does:

  • Loops through each column in $songs_list.
  • Wraps each column in <div class="col-md-4">, creating three equal columns.
  • Loops through each song inside the column and displays it.