MySQL

Comma after each record, except after the last one

Display a comma after each value except 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
<?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

Comma after each value, except the last one Display records Display records in a Bootstrap Table Display records in columns Display records with Bootstrap pagination Display records with CSS styles Escape special characters in a string Highlight the search term in results Replace unicode characters by utf-8 Search autocomplete with Typeahead Search records by an option Select rows for the current day and month Short commands