MySQL

Display records in a Bootstrap table

Display MySQL data in a Bootstrap table

This example demonstrates how to display MySQL data in a Bootstrap table in a simple and structured way:

YearSong TitleArtist
1960My Heart Has a Mind of It's OwnConnie Francis
1965(I Can't Get No) SatisfactionThe Rolling Stones
1950Goodnight, IreneFrank Sinatra
1978Miss YouThe Rolling Stones
2008Violet HillColdplay
1952A Guy is a GuyDoris Day
1972Nice To Be With YouGallery
1981Bette Davis EyesKim Carnes
1993IfJanet Jackson
1960Good Timin'Jimmy Jones
1991JoyrideRoxette
2008WomanizerBritney Spears
1988Two HeartsPhil Collins
1964JavaAl Hirt
1972Hurting Each OtherThe Carpenters
1968Magic BusThe Who
1957Great Balls of FireJerry Lee Lewis
1993All That She WantsAce of Base
1984Last ChristmasWham!
1986Everybody Have Fun TonightWang Chung
2008I am YoursJason Mraz
1997Where Have All the Cowboys Gone?Paula Cole
1950Mona LisaNat King Cole
1951Mockin' Bird HillLes Paul & Mary Ford
1977Hot LineSylvers
1983Vamos a la playaRigheira
1952The Glow-WormThe Mills Brothers
1963One Fine DayThe Chiffons
1969It's Your ThingThe Isley Brothers
1991ChangeLisa Stansfield
1993Just Kickin' ItXscape
2006JumpMadonna
19819 to 5Dolly Parton
1989CherishMadonna
19831999Prince
1992Ain't No DoubtJimmy Nail
1987Living in a BoxLiving In A Box
1998TornNatalie Imbruglia
1925If You Knew Susie (Like I Know Susie)Eddie Cantor
1972Without YouHarry Nilsson
1972Guitar ManBread
1951Cold, Cold HeartTony Bennett
1943Paper DollThe Mills Brothers
1995Gangsta's ParadiseCoolio
2008Miss IndependentNe-Yo
1954Stranger in ParadiseTony Martin
1984The ReflexDuran Duran
1987Livin' On a PrayerBon Jovi
2008TattooJordin Sparks
2013RoarKaty Perry
1976Disco LadyJohnnie Taylor
1980Working My Way Back to YouThe Detroit Spinners
1929Am I Blue?Ethel Waters
1982MickeyToni Basil
1937They Can't Take That Away From MeFred Astaire
1996Hey LoverLL Cool J
1997I Want You BackN Sync
1990Do You Remember?Phil Collins
1995Colors of the WindVanessa Williams
1974The Way We WereBarbra Streisand
Link to Bootstrap CSS and JS Files
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/css/bootstrap.min.css" rel="stylesheet">
<link href="https://cdn.datatables.net/1.12.1/css/jquery.dataTables.min.css" rel="stylesheet" type="text/css">
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/js/bootstrap.min.js"></script>
<script src="https://cdn.datatables.net/1.12.1/js/jquery.dataTables.min.js"></script>
PHP
<?php
  $conn = new mysqli ('server', 'user', 'password', 'database');
  mysqli_set_charset ($conn, 'utf8');

if ($conn -> connect_error) {
  die ('Connection failed: ' . $conn->connect_error);
  } 

$sql = 'SELECT * FROM top_5000_songs ORDER BY RAND() LIMIT 0, 60';
  $result = $conn -> query($sql);

echo '<table id="table-demo" class="table table-striped">';
  echo '<thead>';
  echo '<tr>';
  echo '<th>Year</th>';
  echo '<th>Song Title</th>';
  echo '<th>Artist</th>';
  echo '</tr>';
  echo '</thead>';
  echo '<tbody>'; 
  
  while ($row = $result -> fetch_assoc()) { 
  
  echo '<tr>';
  echo '<td>' . $row['year'] . '</td>';
  echo '<td>' . $row['song_title'] . '</td>';
  echo '<td>' . $row['artist'] . '</td>';
  echo '</tr>';
  }
  
  echo '</tbody>';
  echo '</table>';
  $conn -> close();
  ?>

Responsive Design (Optional)

If you want the table to be responsive, meaning it will adapt to smaller screens, wrap the <table> inside a .table-responsive class:

HTML
<div class="table-responsive">
  <table class="table table-striped"> 

    <!-- Table content goes here -->

  </table>
</div>
Table Custom Settings with jQuery
<script>
$(document).ready(function() {
  $("#table-demo").DataTable({
  paging: true,
  pageLength: 4,
  ordering: true,
  info: false,
  lengthChange: false,
  language: {
    search: "_INPUT_",
    searchPlaceholder: "Search"
  },
search: {
addClass: 'form-control input-lg col-xs-12',
  },
  });
});
</script>