MySQL

Display records in columns

Demonstration

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

What is Love?
Haddaway (1993)

Funkytown
Lipps Inc (1980)

All That She Wants
Ace of Base (1993)

Sadeness
Enigma (1991)

The Final Countdown
Europe (1986)

Moonlight Shadow
Mike Oldfield (1983)

Lambada
Kaoma (1989)

Macarena
Los Del Rio (1996)

Barbie Girl
Aqua (1997)

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.