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
1971Smiling Faces SometimesThe Undisputed Truth
1997Stand By MeOasis
1997Where's the LoveHanson
1974I Honestly Love YouOlivia Newton-John
2009Party In The U.s.a.Miley Cyrus
1967Pleasant Valley SundayThe Monkees
2004What U Waitin' 4Gwen Stefani
2009Russian RouletteRihanna
1974Hooked On a FeelingBlue Swede
2000Everything You WantVertical Horizon
1988Love Changes EverythingClimie Fisher
1965Il SilenzioNini Rosso
1999Back At OneBrian McKnight
1983Wrapped Around Your FingerThe Police
1942Deep in the Heart of TexasAlveno Rey
2004EverythingAlanis Morissette
1977I'm in YouPeter Frampton
1962Good Luck CharmElvis Presley
1997Can't Nobody Hold Me DownP Diddy & Mase
2003Stand UpLudacris & Shaunna
1995Candy RainSoul For Real
2005Rich GirlGwen Stefani
1983JeopardyGreg Kihn Band
1920Crazy BluesMamie Smith
2013Blurred LinesRobin Thicke, T.I. & Pharrell Williams
2010DynamiteTaio Cruz
1984Original SinINXS
2013Can't Hold UsMacklemore & Ryan Lewis
2000Could I Have This Kiss ForeverWhitney Houston & Enrique Iglesias
2013StayRihanna & Mikky Ekko
1982The Look of LoveABC
1969And When I DieBlood Sweat & Tears
2007Kiss KissChris Brown & T-Pain
2007ValerieMark Ronson & Amy Winehouse
1989Eternal FlameThe Bangles
2005Don't LieThe Black Eyed Peas
1965The Last TimeThe Rolling Stones
1962I Can't Stop Loving YouRay Charles
1991Shiny Happy PeopleREM
1967(The Lights Went Out In) MassachusettsBee Gees
1972Long Haired Lover From LiverpoolLittle Jimmy Osmond
1999Man! I Feel Like a Woman!Shania Twain
1949Cruising Down the RiverRuss Morgan
1984Missing YouJohn Waite
1963I Will Follow HimLittle Peggy March
1974The EntertainerMarvin Hamlisch
1981Our Lips Are SealedThe Go Gos
1994Anytime You Need a FriendMariah Carey
1967White RabbitJefferson Airplane
2004I Believe in YouKylie Minogue
1980Stomp!Brothers Johnson
19972 Become 1Spice Girls
1964Oh, Pretty WomanRoy Orbison
1970Black Magic WomanSantana
2004GoodiesCiara & Petey Pablo
2002DreamerOzzy Osbourne
1987Wishing WellTerence Trent D'Arby
1976(Shake, Shake, Shake) Shake Your BootyKC & The Sunshine Band
1969Love Me TonightTom Jones
1956I Almost Lost My MindPat Boone
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>