MySQL

Comma after each record, except after the last one

To display a comma after each value except the last one in MySQL, you can use one of the following two approaches:

Approach 1: Using GROUP_CONCAT() with a custom separator (e.g., a semicolon followed by a space)

<?php
$conn = new mysqli ("server", "user", "password", "database");
mysqli_set_charset ($conn, "utf8");
if ($conn -> connect_error) {
die ("Error: " . $conn->connect_error);
}
$sql = "SELECT SUBSTRING_INDEX(GROUP_CONCAT(song SEPARATOR '; '), '; ', 10) as song_title FROM top_songs_eu";
$result = $conn -> query($sql);
if ($result -> num_rows > 0) {
while ($row = $result -> fetch_assoc()) {
echo "" . $row["song_title"] . "";
}
} 
else {
echo "No records found";
}
$conn -> close();
?>

Sample Output:

My Heart Will Go On; I Will Always Love You; Candle in the Wind; Believe; A Whiter Shade of Pale; Stayin' Alive; Bette Davis Eyes; My Sweet Lord; Baby One More Time; Wannabe

Approach 2: Counting the total number of rows in the result set

In this approach, you first count the total number of rows in the result set. Then, you select the rows as usual, but for the last row, you omit the comma that would typically separate the values.

<?php
  $conn = new mysqli ("server", "user", "password", "database");
  mysqli_set_charset ($conn, "utf8");
  if ($conn -> connect_error) {
  die ("Error: " . $conn->connect_error);
  }
  
  $sql = "SELECT song FROM top_songs_eu LIMIT 0, 10";
  $result = $conn -> query($sql);
  if ($result -> num_rows > 0) {

$count_results = mysqli_num_rows($result); 
  $count_song_titles = 0; 
  
  while ($row = $result -> fetch_assoc()) {

if (++$count_song_titles == $count_results) {
  echo "" . $row["song"] . "";  // last row
  } else {
  echo "" . $row["song"] . ",  ";
  } 
  
  }
  } 
  else {
  echo "No records found";
  }
  $conn -> close();
?>

Sample Output:

My Heart Will Go On, I Will Always Love You, Candle in the Wind, Believe, A Whiter Shade of Pale, Stayin' Alive, Bette Davis Eyes, My Sweet Lord, Baby One More Time, Wannabe